HELP with Function in DMR Report

[richiearora]

Hi all,

I am Very new to Dimensional Reports, Due to Performance optimization we have started to build the Reports on top of Cognos Cube.

I have a Requirement, below is my Requirement to Achieve in the most Dynamic Way to write an MDX Expression:
Level 1
  SUB Level 1.1
     SUB Level 1.1.1
  SUB Level 1.2
     SUB Level 1.2.1
  SUB Level 1.3
     SUB Level 1.3.1
Level 2
  SUB Level 2.1
     SUB Level 2.1.1
  SUB Level 2.2
     SUB Level 2.2.1
  SUB Level 2.3
     SUB Level 2.3.1

I need to Show my report by using only 1 Cross Tab, to show the Level 1 and Sub Level (Which is Level 2 in Hierarchy) and Enable Drill Behavior on Sub Level.

Please advise, I am really Lost in functions like hierarchize or Descendants.

Report output

Level 1
   Sub Level 1.1(Drill Enabled)
Level 2
   Sub Level 2.1(Drill Enabled)

Any Sort of help is Really Appreciated.

Thanks,
Richie

[richiearora]

So, I Figured out the Function which will help is

descendants

However Unable to write an Expression which gives me Desired Result:

i am using

descendants(set(Level 1,Level 2,Level 3),1,self), However This Doesn't Give me Level 1 Level 2 Level 3 Roll up's

The out put i Get is

SUB Level 1.1.1 XX
SUB Level 1.2.1 XX
SUB Level 1.3.1 XX

I need it as :

Level 1
   Sub Level 1.1(Drill Enabled)
Level 2
   Sub Level 2.1(Drill Enabled)


Thanks For help in Advance.

[rockytopmark]

Try Hierarchy() function:

hierarchy
Returns the hierarchy that contains "level", "member", or "set_expression".
Syntax
hierarchy ( level|member|set_expression )