Ancestors Function in MDX

Friends,

This is Very similar to the Function Ancestor in MDX.  The major difference is that Ancestor returns Ancestor where as Ancestors returns SET OF ALL ANCESTORS at the specified level. With Microsoft SQL Server Analysis Services, the set returned will always consist of a single member – Analysis Services does not support multiple parents for a single member. So, this is not a useful one in SSAS MDX. This function works on Hierarchies. If you pass a member and a level then it will  return the ALL the ANCESTORS of a specified member at a specified level. PFB the syntax of the Ancestors function.

Ancestors(Member_Expression,Level_Expression)

Member_Expression – A valid MDX expression that returns a member from a Hierarchy Level.

Level_Expression – A valid MDX expression that returns a level from a Hierarchy Level.

For Ancestors function when you pass a member from a level of hierarchy then the level parameter value can be any from the same hierarchy but it should be above the level to which the member belongs. For example consider time hierarchy –

Year –> Quarter –> Month

If you pass a member from Quarter level then the level parameter should be either Quarter and Year but not Month. If a level expression is specified, the Ancestors function returns ALL the MEMBERS(PARENTS) of specified member at the specified level. If the specified member is not within the same hierarchy as specified level, the function returns an error.

You can also specify Distance instead of LEVEL as parameter. If a distance is specified, the Ancestors function returns ALL the ANCESTORS of the specified member that is the number of steps specified up in the hierarchy specified by the member expression. A member may be specified as a member of an attribute hierarchy, a user-defined hierarchy, or in some cases, a parent-child hierarchy. A number of 1 returns a member’s parent and a number of 2 returns a member’s grandparent (if one exists). A number of 0 returns the member itself. The syntax looks like given below –

PFB the query sample which is using Level_Expression –

select [Measures].[Sales Amount] on Columns,
Ancestors([Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September],[Dim Time].[Hierarchy].[Calendar Quarter]) on Rows
From [Adventure Works]

PFB the same query as above but using Distance –

select [Measures].[Sales Amount] on Columns,
Ancestors([Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September],1) on Rows
From [Adventure Works]

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V