Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

Ancestor (MDX)

A function that returns the ancestor of a specified member at a specified level or at a specified distance from the member.


Level syntax
Ancestor(Member_Expression, Level_Expression)

Numeric syntax
Ancestor(Member_Expression, Distance)
Member_Expression

A valid Multidimensional Expressions (MDX) expression that returns a member.

Level_Expression

A valid Multidimensional Expressions (MDX) expression that returns a level.

Distance

A valid numeric expression that specifies the distance from the specified member.

With the Ancestor function, you provide the function with an MDX member expression and then provide either an MDX expression of a level that is an ancestor of the member or a numeric expression that represents the number of levels above that member. With this information, the Ancestors function returns the ancestor member at that level.

NoteNote

To return a set containing the ancestor member, instead of just the ancestor member, use the Ancestors (MDX) function.

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.

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.

NoteNote

Use this form of the Ancestor function for cases in which the level of the parent is unknown or cannot be named.

The following example uses a level expression and returns the Internet Sales Amount for each State-Province in Australia and its percent of the total Internet Sales Amount for Australia.

WITH MEMBER Measures.x AS [Measures].[Internet Sales Amount] / 
   (
   [Measures].[Internet Sales Amount],  
      Ancestor 
         (
         [Customer].[Customer Geography].CurrentMember,
            [Customer].[Customer Geography].[Country]
         )
   ), FORMAT_STRING = '0%'
SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0,
{
   Descendants 
      (
        [Customer].[Customer Geography].[Country].&[Australia],
           [Customer].[Customer Geography].[State-Province], SELF 
      )
} ON 1
FROM [Adventure Works]

The following example uses a numeric expression and returns the Internet Sales Amount for each State-Province in Australia and its percent of the total Internet Sales Amount for all countries.

WITH MEMBER Measures.x AS [Measures].[Internet Sales Amount] / 
   (
      [Measures].[Internet Sales Amount],
         Ancestor 
            ([Customer].[Customer Geography].CurrentMember, 2)
   ), FORMAT_STRING = '0%'
SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0,
{
   Descendants 
      (
         [Customer].[Customer Geography].[Country].&[Australia],
            [Customer].[Customer Geography].[State-Province], SELF 
      )
} ON 1
FROM [Adventure Works]
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.