TM1 Using MDX parameter in Active Form

[kpradeep25]

hi,

I am trying to understand a Active form which is using dynamic paremetrised MDX but not able to understand the implementaion.

In the spreadsheet ( screen print below), the formula at the top left row looks like this

=TM1RPTROW($E$8,"Planning Sample:plan_chart_of_accounts","","","AccountName",0, IF(G12="ALL", MDX_for_3_levels, IF(G12=0, MDX_for_0_level, IF(G12 = 1, MDX_for_1_level, MDX_for_2_levels))))

Then there are two excel form, one Report origin Selects the level name of Account and then 2nd form "Level" provides level number to be drilled down like this

Report name: Net Operating Income
Levels: 2.

Based on the selecting Activ forms shows all levels.



can anyone please explain what is happening here and how to find source code , this excel is "Planning sample MDX" provided in planning sample model

[lotsaram]

The MDX argument of the Tm1RptRow formula is admittedly a bit of a mess. Does this make it clearer?

Code: Select all

IF(G12="ALL",
  MDX_for_3_levels,
  IF(G12=0,
    MDX_for_0_level,
    IF(G12 = 1, 
      MDX_for_1_level, 
      MDX_for_2_levels
    )
  )
)

You could place this logic in a separate cell to keep the TM1RptRow formula nice and simple (that's what I would do ..).
The workbook contains 3 named ranges; MDX_for_1_level, MDX_for_2_levels & MDX_for_3_levels. If G12 (the levels selection) is "ALL" then the contents of MDX_for_3_levels will be grabbed, otherwise the formula checks a value of 0 or 1.

There could be formulas within the "MDX_for_level" ranges which are parsing MDX or they could be hardcoded strings.

As with all TM1 formulas TM1RptRow only cares about the string value that each argument evaluates to.

[kpradeep25]

Thanks a lot - Lotsa.

Just one more doubt, any idea how the "Report Orgin" and "Levels" fields are being populated ?- it does not look like a excel combo box.

kind regards
Pradeep

[kpradeep25]

No worries - I have managed to figure this out- Anyway thanks alot lotsa