Query returning only the leaf nodes from SSAS Cube using MDX

3
votes

I have a requirement to list out the parent levels of the resultant leaf node into their corresponding columns into a single row.

The below query return the expected result

  SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, 
    NON EMPTY { ([Account].[Account List].[Account List].ALLMEMBERS ) } 
        DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [My Cube]

This return me result of the format

My Child | 1234

But what i am looking for the something similar to

Parent 5| Parent 4| Parent 3| My Child| 1234

Basically i will need the leaf node ONLY

So i tried the below query which does return the leaf, but it also include its parents as well which i am not interested in.

 SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, 
    NON EMPTY { (DESCENDANTS([Account].[Account].[Level 02].ALLMEMBERS) ) } 
        DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [My Cube]

The above returns the result something of the format

> Parent 5| Null| Null| Null| 1
> Parent 5| Parent 4| Null| Null| 12
> Parent 5| Parent 4| Parent 3| Null| 123
> Parent 5| Parent 4| Parent 3|My Child| 1234

From the above result i need only the last row. Of course one solution is to write it to a table and filter out the "null rows". But is there a better way to get the leaf only?

Just fyi, our solution is to use SSIS to query the Olap using MDX and extract it out to 2 dimensional table.

2
ssasmdxolap-cubemdx-query

2 Answers

3
votes

The following LEAVES parameter in the DESCENDANTS function will do what you want. Documentation for this function is here:

SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, 
    NON EMPTY { DESCENDANTS([Account].[Account].[Level 02].ALLMEMBERS, , LEAVES) } 
        DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [My Cube]
1
votes

I'm not sure what exactly you are trying to achieve. If you want to return the value for leafs only, try the following:

With 
Member [Measures].[ValueLeaf] as
IIF(IsLeaf([Account].[Accounts].CurrentMember),[Measures].[Value],Null)

Select 
Non Empty [Measures].[ValueLeaf] on 0, 
Non Empty Descendants([Account].[Account].[Level 02].ALLMEMBERS) DIMENSION PROPERTIES MEMBER_CAPTION on 1 
From [My Cube]