[SOLVED] Query returning only the leaf nodes from SSAS Cube using MDX

Query returning only the leaf nodes from SSAS Cube using MDX

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.

Solution

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]