Ancestor Function in MDX

Friends,

In this post we are gonna discuss about a Navigation function i.e Ancestor. This function works on Hierarchies. If you pass a member and a level then it will  return the ancestor of a specified member at a specified level. PFB the syntax of the Ancestor function.

Ancestor(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 Ancestor 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 Ancestor function returns the ancestor 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 Ancestor function returns the ancestor 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 –

Ancestor(Member_Expression,Distance)

PFB the query sample which is using Level_Expression –

select [Measures].[Sales Amount] on Columns,
Ancestor([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,
Ancestor([Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September],1) on Rows
From [Adventure Works]

You can use Ancestor function to navigate to any level above to the level to which the member passed as parameter belongs to. There is one more function which acts in the same fashion i.e Parent. Learn more about Parent Function here.

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V