Hi Declan,
I'm having a bit of trouble getting it all working.
As a reminder, I'm trying to dynamically allow users to alter threshhold for variance between two years - e.g. 2022-2023. As Declan suggested, I created a measure which I'll use to flag for inclusion - '2022 vs 2023 var include'. That calculates as desired, reading threshold attributes in the }Clients dim. I would have thought feeders working, as I see 1's and 0's even in aggregate.
2022 vs 2023 is just a rollup with weight of 2023 = -1.
Post Adjustment amount is another consolidation in my Measures dim - a rollup of amount plus adjustments;)
But if i create a report with just the '2022 vs 2023 var include' in the column, then suppress zero's clears all rows.
Any thoughts? and Thanks!
This is my rule:
SKIPCHECK;
# run the % calculations
['2022 vs 2023','Post Adjustment Amount 2022 vs 2023 Var %',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] = DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022 vs 2023', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount') \
DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount');
# set the flag for inclusion
['2022 vs 2023 var include','2022 vs 2023',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] =
if ( abs ( DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022 vs 2023', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount' ) )
> DB('}ElementAttributes_}Clients', tm1user(), '$ Variance Threshold') &
abs ( DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022 vs 2023', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount 2022 vs 2023 Var %' ) )
> DB('}ElementAttributes_}Clients', tm1user(), '% Variance Threshold')
,1,0) ;
FEEDERS;
[{'2022', '2023'},'Post Adjustment Amount',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] => ['Post Adjustment Amount 2022 vs 2023 Var %'];
['2022 vs 2023',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'},'Post Adjustment Amount'] => ['2022 vs 2023 var include'];
[{'2022', '2023'},'Post Adjustment Amount',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] => ['2022 vs 2023 var include'];
SKIPCHECK;
# run the % calculations
['2022 vs 2023','Post Adjustment Amount 2022 vs 2023 Var %',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] = DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022 vs 2023', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount') \
DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount');
# set the flag for inclusion
['2022 vs 2023 var include','2022 vs 2023',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] =
if ( abs ( DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022 vs 2023', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount' ) )
> DB('}ElementAttributes_}Clients', tm1user(), '$ Variance Threshold') &
abs ( DB('Financial Summary', !Versions, !Charge Entities, !Source Entities, !Lines of Business, '2022 vs 2023', !Cost Elements, !AWO, !Charge Cost Centers, !Source Cost Centers, !FS Accounts, 'Post Adjustment Amount 2022 vs 2023 Var %' ) )
> DB('}ElementAttributes_}Clients', tm1user(), '% Variance Threshold')
,1,0) ;
FEEDERS;
[{'2022', '2023'},'Post Adjustment Amount',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] => ['Post Adjustment Amount 2022 vs 2023 Var %'];
['2022 vs 2023',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'},'Post Adjustment Amount'] => ['2022 vs 2023 var include'];
[{'2022', '2023'},'Post Adjustment Amount',{'Versions':'Actual','Versions':'Budget','Versions':'Budget Prelim','Versions':'Projection'}] => ['2022 vs 2023 var include'];
------------------------------
Joshua Lobel
------------------------------
Original Message:
Sent: Fri September 27, 2024 09:59 AM
From: Declan Rodger
Subject: Is it possible to filter a Dynamic or Universal PAfE report based on column values with 2 dimensions nested on rows?
Hi Josh,
I'm not sure about the universal reports as I haven't played about with them much but expect you may have a similar problem as dynamic reports where it won't work "naturally". Essentially in your MDX View you are needing to apply the Filter against the tuple (the combination of elements from 2 dimensions) - which works great with an MDX view (would work in an Exploration Report); but for Dynamic Reports they are essentially working on an old-style "native" view; where you can only apply MDX against each row dimension individually and they have no understanding of which element is referenced in the other row dimension.
There is however a fairly simple old trick to get around requirements like this:
- Create a new element in the Periods dimension that holds a 1 or a 0 dependent on whether you want to show the data (e.g. a rule could be used here that matches your filter from the MDX)
- Build your dynamic report to ONLY show that new element in the columns
- Apply zero suppression on the rows (so now you will have the relevant rows you want... but don't have the actual columns you want...YET)
- Now you just put column heading for the columns you actually wanted (e.g. 2023, 2024, variance etc.) to the right of the one that is already there
- Manually add DBRW formulas to retrieve the data you want (you only need to put these formulas in the top row - they get copied down during rebuild)
- Hide the 1/0 column the report is built on (as you don't want users to see that)
- Just make sure that the new columns you add to the report are not referenced in the TM1RptView formula as then it would start considering them during the zero suppression
And then that should get you the result you want; only downside is the "report specific" elements you then have hanging around in your model.
Thanks,
Declan
------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
Original Message:
Sent: Wed September 25, 2024 05:13 PM
From: Joshua Lobel
Subject: Is it possible to filter a Dynamic or Universal PAfE report based on column values with 2 dimensions nested on rows?
I have a pretty straightforward report I'm working on:
2023 2024 2023 vs 2024
Acct Cost Element
123 ABC 20 25 -5
124 DEF 12 15 -3
I'd like to only show rows where the absolute value of the 2023 vs 2024 variance is > 4. So only my first row would be included. My example above is a bit simplified vs code below.
This is what I put into the MDX for the TM1RPTROW definition on Cost Element (it includes all dims in the cube):
'{FILTER(
{{DRILLDOWNMEMBER({[Cost Elements].[Cost Elements].[Category Rollups]} , {[Cost Elements].[Cost Elements].[Category Rollups]} , RECURSIVE)}},
[Financial Summary].(
[Versions].[Actual],
[Charge Entities].[22]
[Source Entities].[S_ZZ],
[Lines of Business].[ZZZZZ],
[Periods].[2022 vs 2023],
[AWO].[All AWO],
[Charge Cost Centers].[ZZZ],
[Source Cost Centers].[S_ZZZ],
[FS Accounts].[Account Rollups],
[Measures_ Financial Summary].[Post Adjustment Amount]) > 4 )}
If I just have one dimension in my rows, then I can use the Dimension MDX to add a filter – that kind of works if I use current member for the other dimensions in the cube.
If I try CurrentMember on my Account dimension (which is the first dimension in the rows), then I get an error on refresh and no rows.
If I create an exploration, by MDX looks like this FOR THE COLUMNS (highlighted in red):
SELECT NON EMPTY CROSSJOIN({
[Versions].[Versions].[Actual]
},{
[Periods].[Periods].[All Time for Capital^2021],
[Periods].[Periods].[All Time for Capital^2022],
[Periods].[Periods].[Capital Budget Years^2023],
[Periods].[Periods].[2021 vs 2022],
[Periods].[Periods].[2022 vs 2023]
}) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD(NONEMPTY(FILTER(DISTINCT(DRILLDOWNMEMBER({
[FS Accounts].[FS Accounts].[Account Rollups]
} , {
[FS Accounts].[FS Accounts].[Account Rollups]
} , RECURSIVE) * DRILLDOWNMEMBER({
[Cost Elements].[Cost Elements].[Category Rollups]
} , {
[Cost Elements].[Cost Elements].[Category Rollups]
} , RECURSIVE)) ,
[Financial Summary].([Versions].[Versions].[Actual],
[Periods].[Periods].[2022 vs 2023]) > 4.0),{CROSSJOIN({
[Versions].[Versions].[Actual]
},{
[Periods].[Periods].[All Time for Capital^2021],
[Periods].[Periods].[All Time for Capital^2022],
[Periods].[Periods].[Capital Budget Years^2023],
[Periods].[Periods].[2021 vs 2022],
[Periods].[Periods].[2022 vs 2023]
})}), 100000) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
FROM [Financial Summary]
WHERE ([Charge Cost Centers].[Charge Cost Centers].[ZZZ] ,
[Source Entities].[Source Entities].[S_CR^S_ZZ] ,
[Measures_ Financial Summary].[Measures_ Financial Summary].[Post Adjustment Amount] ,
[Source Cost Centers].[Source Cost Centers].[S_ZZZ] ,
[AWO].[AWO].[Alll AWO excl Alloc^All AWO] ,
[Charge Entities].[Charge Entities].[CR^ZZ] ,
[Lines of Business].[Lines of Business].[All Lines of Business]) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY
I don't think the TM1RPTFILTER accepts freeform MDX – just top 5 etc.
If I create a dynamic report from my exploration, it omits the filter
If I create a universal report from my exploration, I get an error – IBM Framework for Microsoft Office – "Unable to refresh the Universal Report Book4_Sheet3_0.".
I'm on PaFe add in 95.1. Cloud user on the server side.
Is there a simple piece of syntax that I'm missing?
Thanks,
Josh
Josh Lobel
Financial Systems Administrator – Corporate Performance Management
Eversource Energy | 
| 247 Station Drive | Westwood, MA 02092
Joshua.Lobel@Eversource.com | Cell: 401-965-9446
This electronic message contains information from Eversource Energy or its affiliates that may be confidential, proprietary or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. Any views or opinions expressed in this message are not necessarily those of Eversource Energy or its affiliates. Any disclosure, copying or distribution of this message or the taking of any action based on its contents, other than by the intended recipient for its intended purpose, is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately and delete it from your system. Email transmission cannot be guaranteed to be error-free or secure or free from viruses, and Eversource Energy disclaims all liability for any resulting damage, errors, or omissions.