Given the input member, this function returns an ancestor at the specified layer.
Syntax
Ancestor ( member , layer | index [, hierarchy ] )
Parameter | Description |
---|---|
The member for which an ancestor is sought. | |
Layer specification. | |
index | A number of hierarchical steps up from member, locating the ancestor you want returned. |
Optional. A specific hierarchy within the time dimension. |
Notes
The return value of this function is a member. If you want the return value to be a set, use Ancestors.
Do not use negative numbers for index. If you want to return lower members, use Descendants instead of Ancestor. Ancestor([Qtr1], -1) would return an empty member, not a descendant.
If you use layer to specify a level but no ancestor exists at that level, then the return value is an empty member. For example, in the Sample Basic database, consider the level numbers of the ancestors of the member [Additions] in the [Measures] dimension:
[Additions], being a leaf-level member, has level number 0.
[Inventory] has level number 1.
[Measures] has level number 3, as one of its children [Profit] has level number 2.
The level number of a member = (highest level number among its children) + 1. Therefore, Ancestor ([Measures].[Additions], [Measures].Levels(2)) returns an empty member, because [Additions] does not have an ancestor with level number 2.
Example
Ancestor ( [New York], [Market].levels(2) )
returns the member [Market], which is the ancestor of [New York] that is located at level 2 in the outline.
Ancestor ([Year].[Jan], [Year].generations(2))
returns the member [Qtr1], which is the ancestor of Jan that is located in the second generation of the Year dimension.
Ancestor ( [Feb], 2 )
returns the member [Year], which is the grandparent of Feb.
Ancestor ( [Feb], 0 )
returns the member [Feb]. An "ancestor" that is zero steps away is considered to be the member itself.