MDX filter for two dimensions

[CRP0021]

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.

Tm1_MDX.png (7.71 KiB) Viewed 1506 times

Looking forward to hearing any thoughts if there is any way this can be achieved.

Thanks in advance!

[Wim Gielis]

What client tools do you have at your disposal ? PAW/PAfE or rather Architect/Perspectives/TM1 Web ?

[CRP0021]

Hi Wim,
I can use either.
Right now the report is authored in Perspectives but will be converted to PAfE soon enough.

[Wim Gielis]

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.

[ascheevel]

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.

[declanr]

And in terms of the MDX view that Wim mentioned you would want to use code like below:

Code: Select all

SELECT {[Measure].[Measure].MEMBERS} ON 0, 
{Filter(
	{[Order#].[Order#].MEMBERS}*{[Line].[Line].MEMBERS},
	[Test].([Measure].[Flag])<>"Inactive")}
ON 1 
FROM [CubeName]

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.

[WilliamSmith]

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.