Planning Analytics with Watson

Expand all | Collapse all

TM1ELLIST ignores changes in underlying Datasource

  • 1.  TM1ELLIST ignores changes in underlying Datasource

    Posted Mon June 14, 2021 08:48 AM
    Edited by Constantin Budin Mon June 14, 2021 08:59 AM
    Hello everyone,

    we are using TM1ELLIST in our excel Reports. Unfortunately the do not update if the underlying data changes:

    We are using the following MDX (The last or condition sets the 0 to 1 if a toggle is set. This then ignores all other condition thereby returning all dimension elements).
    This MDX works perfectly fine, however if some data changes such that an element should be included in the result set, the last element is instead repeated. 
    e.g. with active filters:

    • GEN-0057
    Make some changes such that now also GEN-0058 and GEN-0059 should be returned what happens instead is:

    • GEN-0057
    • GEN-0057
    • GEN-0057

    If an element should be removed from the list, because the underlying data changes, the element is instead kept in the list, which should not be the case.

    I hope there is some way to fix this.
    {Union({[PlanningMeasure_Costcenter].[GEN-0057]},
         { 
        Filter({
                TM1DRILLDOWNMEMBER(
                    {[PlanningMeasure_Costcenter].[GEN-0057]}, ALL, Recursive
        )},
        [Costcenter].(
            [Costcenter].[2030200100 - UMLAGE BEREICHSREGIE], 
            [Planningcycle].[Prg. 03+/21], 
            [Variant].[Hauptvariante], 
            [Currency].[Local currency], 
            [Datatype].[Actual], 
            [Year].[Total Year], 
            [Month].[Total Month]
        ) <> 0
        OR
        [Costcenter].(
            [Costcenter].[2030200100 - UMLAGE BEREICHSREGIE], 
            [Planningcycle].[Prg. 03+/21], 
            [Variant].[Hauptvariante], 
            [Currency].[Local currency], 
            [Datatype].[Plan], 
            [Year].[Total Year], 
            [Month].[Total Month]
        ) <> 0
        OR
         [Costcenter].(
            [Costcenter].[2030200100 - UMLAGE BEREICHSREGIE], 
            [Planningcycle].[Current Planningcycle], 
            [Variant].[Hauptvariante], 
            [Currency].[Local currency], 
            [Datatype].[Total Datatype], 
            [Year].[2020], 
            [Month].[Total Month]
        ) <> 0
        OR
         [ReportingFullView].(
            [Costcenter_All].[2030200100 - UMLAGE BEREICHSREGIE],
            [Planningcycle].[Current Budget  2], 
            [Variant].[Hauptvariante],
            [CurrencyExchange].[EUR],
            [Datatype].[Total Datatype], 
            [Year].[2021], 
            [Month].[Total Month],
            [Country].[Total Country],
            [Company_Reporting].[Total Company],
            [MatrixOrganization].[Total MatrixOrganization],
            [isBAB].[Total (BAB & NOBAB)],
            [CM_Structure].[Total]
        ) <> 0
        OR 1 = 0
    )})}
    ​


    ------------------------------
    Constantin Budin
    ------------------------------


  • 2.  RE: TM1ELLIST ignores changes in underlying Datasource

    Posted Mon June 14, 2021 11:47 AM

    Which interface are you operating tm1ellist in?

    In Excel's case, try using alt-f9 in place of doing the other refresh options.  Let us know if the outcome is desirable.



    ------------------------------
    Ted Phillips
    ------------------------------



  • 3.  RE: TM1ELLIST ignores changes in underlying Datasource

    Posted Tue June 15, 2021 03:02 AM
    Hello Ted,

    we are using PAfE with O365.

    While ALT+ F9 works it is not a usable solution for us. We need some way to clear the cache from VBA-Code.

    We have tried the following.
    • SendKeys "%{F9}" in VBA does not work. When sending ALT+F9 this way it appears to be ignored by PAfE
    • CognosOfficeAutomationObject.ClearCache() does not work
    • CognosOfficeAutomationObject.RefreshAllData does not work
    • Reporting.DynamicReports.RebuildActiveWorkbook does not work


    ------------------------------
    Constantin Budin
    ------------------------------



  • 4.  RE: TM1ELLIST ignores changes in underlying Datasource

    Posted Tue June 15, 2021 08:38 AM
    next, try ONKEY_ALT_F9 or PurgeAF (global udf) macro depending on if you want to force calculation or not, respectively.

    ------------------------------
    Ted Phillips
    ------------------------------



  • 5.  RE: TM1ELLIST ignores changes in underlying Datasource

    Posted Tue June 15, 2021 09:24 AM
    Hello Ted,

    thank you for your swift response. PurgeAF works great for us. However, we would still prefer it if TM1ELLIST would not cache the results.



    ------------------------------
    Constantin Budin
    ------------------------------