Illustrating Examples for MDX Navigation Functions for Accessing Extended Relatives

The following are illustrating examples that show the difference between the following MDX navigation functions:

Function	Description
Ancestor( Member, Level | Distance )	Returns a member’s ancestor from a given level
Ancestors( Member, Level | Distance )	Returns a member’s ancestor from a given level as a single-member set
Ascendants( Member )	Returns the complete set of a member’s ancestors, including the given member itself
Descendants(Member | {Set}[, Level | Distance [, Flag ]])	Returns the descendants of a member(s) from a given level as modified by one of eight optional flags
Cousin( Member1, Member2 )	Returns the member in the same relative position as the (first) specified member but under the (second) member of a higher level

In the following examples we will use the date dimension [D Date], which contains a [Hierarchy]: [YAER] -> [MONTH NO] -> [Day], and we will use [Measures].[Normal] as a measure.

No navigation function

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],[D Date].[Hierarchy].[Month No] ) ON ROWS

from [TC Cube]

_________________________________________________________________________________

Ancestor and Ancestors

Almost identical, the difference between them is that the Ancestor function returns a reference to the ancestor member, whereas the Ancestors function returns the ancestor member as a single-member set.

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],Ancestor([D Date].[Hierarchy].[Year].[2012].[5],[D Date].[Hierarchy].[Year] )) ON ROWS

from [TC Cube]

_________________________________________________________________________________

Ascendants

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],Ascendants([D Date].[Hierarchy].[Year].[2012].[5] )) ON ROWS

from [TC Cube]

_________________________________________________________________________________

Cousin

In the following query we want to get one of 5/2012 cousins which is 5/2011. 

Remember that the Cousin function returns the member in the same relative position as the (first) specified member but under the (second) member of a higher level.

select [Measures].[Normal] on 0,

([D Date].[Year].[2011],Cousin([D Date].[Hierarchy].[Year].[2012].[5],[D Date].[Hierarchy].[Year].[2011] )) ON ROWS

from [TC Cube]

      

_________________________________________________________________________________

Descendants

And the following are the flags that can be used to determine the returned set:

Flag	Description
SELF (or no flag)	Instructs the function to return the descendant members of the identified level
AFTER	Instructs the function to return the descendant members in the levels below the identified level
BEFORE	Instructs the function to return the descendant members in the levels prior to the specified level and including the specified member
SELF_AND_AFTER	Instructs the function to return the set representing the combination of those returned by the SELF and AFTER flags
SELF_AND_BEFORE	Instructs the function to return the set representing the combination of those returned by the SELF and BEFORE flags
SELF_BEFORE_AFTER	Instructs the function to return the set representing the combination of those returned by the SELF, BEFORE, and AFTER fl ags. The set returned is the complete set of descendants of the specifi ed member.
BEFORE_AND_AFTER	Instructs the function to return the set representing the combination of those returned by the BEFORE and AFTER flags. This set returns the complete set of descendants of the specified member excluding the members of the identified level.
LEAVES	Instructs the function to return the leaf-level descendants of the specified member between the specified member and the identified level

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],Descendants([D Date].[Hierarchy].[Year].[2012],[D Date].[Hierarchy].[Month no],self )) ON ROWS

from [TC Cube]

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],Descendants([D Date].[Hierarchy].[Year].[2012],[D Date].[Hierarchy].[Month no],After )) ON ROWS

from [TC Cube]

Gets all the days of 2012

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],Descendants([D Date].[Hierarchy].[Year].[2012],[D Date].[Hierarchy].[Month no],before )) ON ROWS

from [TC Cube]

select [Measures].[Normal] on 0,

([D Date].[Year].[2012],Descendants([D Date].[Hierarchy].[Year].[2012],[D Date].[Hierarchy].[Month no],LEAVES )) ON ROWS

from [TC Cube]