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.