Thank you for the detailed feedback Ted. I have lots of homework to do…
Will have to run more tests before advocating anything though.
Original Message:
Sent: Wed February 22, 2023 01:33 PM
From: Ted Phillips
Subject: TM1ELLIST ignores changes in underlying Datasource
no worries George.
it's not really that short of a topic though, unless already fairly well informed about the various classes of spreadsheet functions offered by the addin and the particulars involved with PA for Excel (see: the 'differences' article in the usage notes for Perspectives user's PA for Excel topic), the PA api offerings across time, and Excel itself.
short answer is there are certain fundamental concepts underpinning the PA for Excel addin, a brief/partial summary:
uses only the rest api, uses only mdx query paths, can not assume a high quality/close/intranet network link to the rest of PA, reporting content must be stable at-rest (which is to say that after a completion of a full calculation cycle, no content is inherently Excel-Dirty)
those concepts drive the implementation outcomes in the addin. a basic example is that Custom Reporting functions must be run asynchronously (as compared to Perspectives which ran them synchronously backwards through the formula dependency graph using coercion)
another [more relevant] outcome (due to the difference in the api stacks) is that Dynamic Reports, TM1Set, TM1ELLIST use a special kind of computation to achieve their intended effect (whether that's matching historical api results/or user expectations). These items have special lifecycle needs that are balanced for reuse given they have a fairly high floor cost. Purge is a hook into the lifecycle of the natural system that effectively forces them to be recomputed, otherwise the addin will attempt to manage their lifecycle given the quality of invalidation signals it currently receives.
'stable at rest' is really what drives the Dirty item. unlike Perspectives which essentially ran all content as continuously volatile, the addin will pursue a clean resting state for the reporting content. given this is entirely a spreadsheet specific system (and not subject to outside influence like the computational outcomes are) it's less likely for anyone to have a real need to interact with it (as the addin can & should be able to manage it fairly 'perfectly') the only scenario I could think of quickly would be some extremely exotic macro scenarios.
------------------------------
Ted Phillips
IBM
Original Message:
Sent: Wed February 22, 2023 12:55 PM
From: George Tonkin
Subject: TM1ELLIST ignores changes in underlying Datasource
Apologies for jumping in the middle here but was triggered by reading PurgeAF.
@Ted Phillips - How do we as developers apply this and the DirtyAF function in PAfE?
------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
Original Message:
Sent: Wed February 22, 2023 12:47 PM
From: Ted Phillips
Subject: TM1ELLIST ignores changes in underlying Datasource
is the invalidation occurring due to actions in the spreadsheet itself, or is it happening due to an external force? (chore, other users, etc)
if the workflow of the spreadsheet is consistently driving the invalidation itself, ex: user clicks an action button, could place a purgeaf call in the shape-action association there (onclick).
------------------------------
Ted Phillips
IBM
Original Message:
Sent: Thu February 16, 2023 01:21 PM
From: Peyton David
Subject: TM1ELLIST ignores changes in underlying Datasource
I'm having the exact same issue I believe. I am adding a leaf element to a dimension, so when I grab the all leaves subset, I was assuming it would populate (at least more than just the first time). I then tried the above filter around the MDX (displayed below), but that method unfortunately was not successful for me. Were you able to to find a more efficient solution by chance?
What I tried:
=TM1ELLIST("tm1:Employee List",,,,,FILTER("TM1FILTERBYLEVEL([Employee List].[Employee List].MEMBERS , 0)", 1>0),,)
Note: the bolded/underlined "1" was essentially a variable that was never the same, but always greater than 0.
------------------------------
Peyton David
Original Message:
Sent: Thu May 26, 2022 02:19 PM
From: Mitchell Powell
Subject: TM1ELLIST ignores changes in underlying Datasource
I'm having similar issues but with a much simpler use case. In my case, the only argument I'm using is the first one (dimension), in which case it simply returns all elements. Problem is that when an element is added to the dimension, the returned list does not reflect that.
------------------------------
Mitchell Powell
Original Message:
Sent: Fri October 08, 2021 10:43 AM
From: Constantin Budin
Subject: TM1ELLIST ignores changes in underlying Datasource
PAfE seems to cache MDX results based on the MDX String. We implemented a hacky fix by addinga Filter and a random string to the MDX in VBA and changing the random string after each refresh. Something like this:
FILTER(original_MDX, random_number > 0)
The random number we generate is always > 0. Therefore the filter does not do much except forcing PAfE to acutally execute the MDX instead of using a cached result.
------------------------------
Constantin Budin
Original Message:
Sent: Thu October 07, 2021 05:09 PM
From: Ivan Cepero
Subject: TM1ELLIST ignores changes in underlying Datasource
I am having the same issue. Can you point me to more data on using PurgeAF? I'm not finding much online so far.
------------------------------
Ivan Cepero
Original Message:
Sent: Tue June 15, 2021 09:23 AM
From: Constantin Budin
Subject: TM1ELLIST ignores changes in underlying Datasource
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
Original Message:
Sent: Tue June 15, 2021 08:38 AM
From: Ted Phillips
Subject: TM1ELLIST ignores changes in underlying Datasource
next, try ONKEY_ALT_F9 or PurgeAF (global udf) macro depending on if you want to force calculation or not, respectively.
------------------------------
Ted Phillips
Original Message:
Sent: Tue June 15, 2021 03:02 AM
From: Constantin Budin
Subject: TM1ELLIST ignores changes in underlying Datasource
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
Original Message:
Sent: Mon June 14, 2021 11:46 AM
From: Ted Phillips
Subject: TM1ELLIST ignores changes in underlying Datasource
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
Original Message:
Sent: Mon June 14, 2021 08:48 AM
From: Constantin Budin
Subject: TM1ELLIST ignores changes in underlying Datasource
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:
Make some changes such that now also GEN-0058 and GEN-0059 should be returned what happens instead is:
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
------------------------------
#PlanningAnalyticswithWatson