Perspektives - Active Forms - MDX

[Ganos Lal]

Hello

I want to build an active form in Perspectives with 2 nested dimensions in rows.
The idea is, if the first dimension has an C element as current member, depending on that in the second dimension should only the Total element be shown. If there's an N element in first dimension, in second dimenson all elements level 0 should be shown.

Therefore i created this formulas with mdx:
Dimension1: =TM1RPTROW($B$9;"DB:Dimension1";;;;0;{DESCENDANTS([Dimension1].[Total Dimension1]) })

Dimension2: =TM1RPTROW($B$9;"DB:Dimension2";;;;0;IIF([Dimension1].currentmember.children.count=0,{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dimension2] )}, 0)},[Dimension2].[Total Dimension2]))

But this doesn't work, it shows all items in Dimension2. Is there anything wrong in my statement or is it generally doesn't work with e.g. currentmember?
I would be happy on any advise, thanks.

Regards,
Jani

[tomok]

What exactly would be your definition of "current member" in this case? The element in the column to the left? To the right? In order for your MDX to work the query needs to have context, meaning TM1 has to know which member to "take the current member of". I'm pretty sure your idea won't work in an active form because TM1REPORTROW formulas are independent of each other, meaning the second has no knowledge of the first so it can't evaluate the CurrentMember property.

[lotsaram]

Dimension.CurrentMember (at least in the context of a TM1 view) which refers to another dimension (not the one containing the CurrentMember MDX) can be "aware" of the context of a dimension on filter but not on rows or columns. By definition the column and row tuples are variable so there is no context for current member if it is pointing to another dimension.

[declanr]

As the others have pointed out MDX alone can't do this; it would be great if the 2 tm1rptrow formulas could interact but it just doesn't work that way sadly.

To achieve the same result though you can get smart and actually have the active form reference another cube or intersection of the same cube where you populate or use a rule to populate a value in only the intersections that you want shown on the report.
You then suppress the active form so it ignores all unwanted intersections and set up a different view formula to pull back the values you do want the users to see.

You might need to think carefully about what you want in the flag cube to make sure it as efficient as possible; you may also benefit from using duplicate dimensions that have all the same elements as the actual dims but have all elements at N-Level.

Should be an idea to get you going.

[Ganos Lal]

Thank you.
I think about alternate ways.

Regards
Jani