Hi all,
Just looking to confirm if the following situation is possible. I don't believe it is, but would like to confirm and maybe get some thoughts on what is possible.
I have a dynamic report created from a 3 dimensional cube:
Order #
Line
Measures
In the rows I have Order #, Line, and several measures and would like to filter based on a cube value.
The measure I am trying to filter on is a Flag which determines whether the transaction is active or not (see screenshot below).
In this example I'd need the 2 yellow shaded cells to be hidden on this report.
So I'd have to hide the intersection of Order# 001 and Line 2 as well as the elements within those dims.
Cell security achieves only hiding the data but the dimension elements are still displayed.
Looking forward to hearing any thoughts if there is any way this can be achieved.
Thanks in advance!
MDX filter for two dimensions
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: MDX filter for two dimensions
What client tools do you have at your disposal ? PAW/PAfE or rather Architect/Perspectives/TM1 Web ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 27
- Joined: Mon Aug 21, 2017 2:14 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: MDX filter for two dimensions
Hi Wim,
I can use either.
Right now the report is authored in Perspectives but will be converted to PAfE soon enough.
I can use either.
Right now the report is authored in Perspectives but will be converted to PAfE soon enough.
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: MDX filter for two dimensions
It’s not possible in Perspectives but should be possible in PAfE with an MDX view.
EDIT: as Andrew rightly said, it’s possible in Perspectives with some extra work done.
Thanks for adding that. With the REST API based tools it’s just easier.
EDIT: as Andrew rightly said, it’s possible in Perspectives with some extra work done.
Thanks for adding that. With the REST API based tools it’s just easier.
Last edited by Wim Gielis on Wed Feb 22, 2023 11:33 pm, edited 1 time in total.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Community Contributor
- Posts: 288
- Joined: Fri Feb 15, 2013 5:49 pm
- OLAP Product: TM1
- Version: PA 2.0.9.1
- Excel Version: 365
- Location: Minneapolis, USA
Re: MDX filter for two dimensions
If you must do this in perspectives, you could put a rule on your Value measure that forces the value to zero when the flag value is "inactive" and then set zero suppression to 1 in your TM1RptView formula. If you still want the Flag measure visible, you can populate that by referencing the cube directly in the DBRW formula instead of the TM1RptView cell to get the desired zero suppression on non zero values only.
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: MDX filter for two dimensions
And in terms of the MDX view that Wim mentioned you would want to use code like below:
Within the Filter section; you are combining the members of the 2 dimensions together into rows. And then performing a simple filter on the cube value.
So it would return everything except the "Inactive" rows.
Code: Select all
SELECT {[Measure].[Measure].MEMBERS} ON 0,
{Filter(
{[Order#].[Order#].MEMBERS}*{[Line].[Line].MEMBERS},
[Test].([Measure].[Flag])<>"Inactive")}
ON 1
FROM [CubeName]
So it would return everything except the "Inactive" rows.
Declan Rodger
- WilliamSmith
- Posts: 40
- Joined: Tue Dec 13, 2022 8:54 pm
- OLAP Product: TM1 / PA / PAx / PAW
- Version: TM1 11
- Excel Version: 365
Re: MDX filter for two dimensions
ProTip: If you model the view in Planning Analytics Workshop, you can click on the MDX button and it will auto generate the MDX string for you.