Tag Archives: Leaf Members

MDX, SSAS

Using the Descendants Function

1 Comment

The MDX function, Descendants, is a very versatile and commonly used function that returns the members at, below, or even above a specified level.

The first argument of the descendants function is a member or set expression. The second argument can either be a specific level expression or a number indicating the number of levels to travel. The following queries return the same results:

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    2) 
    on 1
From [Adventure Works]

GO

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter]
    ) 
    on 1
From [Adventure Works]

 

Results:
image

The Descendants also accepts a third optional argument: a string expression to distinguish between possible descendants to return in the results. This query uses the SELF_AND_AFTER flag to returns members at the specified level as well as members subordinate to the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    SELF_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:
image 

And this query uses the BEFORE_AND_AFTER flag to return all members except for the members of the specified level.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    [Date].[Calendar].[Calendar Quarter],
    BEFORE_AND_AFTER
    ) 
    on 1
From [Adventure Works]

Results:
image

Notice that the specified level is excluded.

Also, if I use the LEAVES flag, I can return all the leaf members between the specified member and the specified level. Alternatively, if you don’t specify a level, the members at the lowest level (the leaf members) will be returned.

With Member [Measures].[Level Name] AS 
[Date].[Calendar].CURRENTMEMBER.LEVEL.NAME

Select {[Measures].[Level Name]} on 0,
Descendants(
    [Date].[Calendar].[Calendar Year].&[2007],    
    ,
    LEAVES
    ) 
    on 1
From [Adventure Works]

Results:
image

Check out MSDN for more information on the Descendants function.