Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize

Ancestors (MDX)

Updated: 17 July 2006

A function that returns the set of all ancestors of a specified member at a specified level or at a specified distance from the member. With Microsoft SQL Server 2005 Analysis Services (SSAS), the set returned will always consist of a single member - Analysis Services does not support multiple parents for a single member.


Level syntax
Ancestors(Member_Expression, Level_Expression)

Numeric syntax
Ancestors(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 Ancestors 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 that member or a numeric expression that represents the number of levels above that member. With this information, the Ancestors function returns the set of members (which will be a set consisting of one member) at that level.

ms145620.note(en-US,SQL.90).gifNote:
To return an ancestor member, rather than an ancestor set, use the Ancestor function.

If a level expression is specified, the Ancestors function returns the set of all ancestors of the specified member at the specified level. If the specified member is not within the same hierarchy as the specified level, the function returns an error.

If a distance is specified, the Ancestors function returns the set of all members that are 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 the set of members at the parent level and a number of 2 returns the set of members at the grandparent level (if one exists). A number of 0 returns the set including only the member itself.

ms145620.note(en-US,SQL.90).gifNote:
Use this form of the Ancestors function for cases in which the level of the parent is unknown or cannot be named.

The following example uses the Ancestors function to return the Internet Sales Amount measure for a member, its parent, and its grandparent. This example uses level expressions to specify the levels to be returned. The levels are in the same hierarchy as the member specified in the member expression.

SELECT {
    Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver, 38],[Product].[Product Categories].[Category]),
    Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver, 38],[Product].[Product Categories].[Subcategory]),
    Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver, 38],[Product].[Product Categories].[Product])
    } ON 0,
[Measures].[Internet Sales Amount] ON 1
FROM [Adventure Works]

The following example uses the Ancestors function to return the Internet Sales Amount measure for a member, its parent, and its grandparent. This example uses numeric expressions to specify the levels being returned. The levels are in the same hierarchy as the member specified in the member expression.

SELECT {
   Ancestors(
      [Product].[Product Categories].[Product].[Mountain-100 Silver, 38],2
      ),
   Ancestors(
      [Product].[Product Categories].[Product].[Mountain-100 Silver, 38],1
      ),
   Ancestors(
      [Product].[Product Categories].[Product].[Mountain-100 Silver, 38],0
      )
   } ON 0,
[Measures].[Internet Sales Amount] ON 1
FROM  [Adventure Works]

The following example uses the Ancestors function to return the Internet Sales Amount measure for the parent of a member of an attribute hierarchy. This example uses a numeric expression to specify the level being returned. Since the member in the member expression is a member of an attribute hierarchy, its parent is the [All] level.

SELECT {
   Ancestors(
      [Product].[Product].[Mountain-100 Silver, 38],1
      )
   } ON 0,
[Measures].[Internet Sales Amount] ON 1
FROM [Adventure Works]

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft