ssasmdxdescendant

Descendants (MDX) of root member

Environment: SSAS v12.

Why the following function

DESCENDANTS([Dim1].[Hier1].[(All)])

returns only [Dim1].[Hier1].[(All)] ?

Expectation is to return the root member and all it's descendants.

Or how to achieve that?

PS Specifying [ , Level_Expression [ ,Desc_Flag ] ] does not help...

Many thanks in advance.

Solution

  • You can try adding one of the flags:

    DESCENDANTS(
    [Dim1].[Hier1].[All]   //<<member
  , [Dim1].[Hier1].[(All)] //<<level
  , SELF_BEFORE_AFTER      //<<flag
)

    Documentation of the function is here: https://learn.microsoft.com/en-us/sql/mdx/descendants-mdx

    The documentation gives a way of experimenting with the different flags:

    SELECT Descendants  
   ([Geography].[Geography].[Country].&[United States]  
      //, [Geography].[Geography].[Country]  
   , [Geography].[Geography].[City]  
      //, [Geography].[Geography].Levels (3)  
      //, SELF   
      //, AFTER  
      , BEFORE  
      // BEFORE_AND_AFTER  
      //, SELF_AND_AFTER  
      //, SELF_AND_BEFORE  
      //,SELF_BEFORE_AFTER  
      //,LEAVES   
   ) ON 0  
FROM [Adventure Works]