Active report - Filter down to Level 0 based on selection

[wypwong]

Hi all

I have an unbalanced dimension (Country), and I want to show level 0 items only, but it has to be based on user's selection.
e.g.
C: Total
C: Region 1
N: Country 1
N: Country 2
C: Region 2
N: Country 3
N: Country 4

For example, if the user select Level 0 Country 1, then I want my report to show Country 1 only.

If the user select Region 1 (Consolidated element), then I want my report to show Country 1 & Country 2 (level 0) under this region

If the user select Total, then the report will show Country 1, 2, 3 and 4. (all level 0)

I am using TM1RPTROW for the report, am thinking to use MDX to do it. But I am not sure how can I just filter down to level 0 based on user's selection.

Thanks in advance.

Regards
Phyllis

[wypwong]

if I put in the Region, then it works.
TM1RPTROW($C$9,pServer&":Country",,,,,"{DRILLDOWNLEVEL( {[Country].[Region 1]})}")

But if I like it to a cell like below, then it doesn't work.
=TM1RPTROW($C$9,pServer&":Country",,,,,"{DRILLDOWNLEVEL( {[Country].$D$18})}")

[wypwong]

I found the way to do it now, so happy.

=TM1RPTROW($C$9,pServer&":Country",,,,,"{DRILLDOWNLEVEL( {[Country].[" & $D$18 & "] })}")

[wypwong]

Actually should be using TM1DRILLDOWNMEMBER instead of DRILLDOWNLEVEL

[rmackenzie]

I found the way to do it now, so happy.

Good for you!

The general syntax for that sort of dynamic expression is:

Code: Select all

{TM1FILTERBYLEVEL(
  {TM1DRILLDOWNMEMBER( {[YOUR_DIMENSION].[YOUR_ELEMENT]} , ALL, RECURSIVE)},
  0
)}