Ancestors

Analysis Services

Analysis Services

Ancestors

Returns a set of all the ancestors of a member at a specified level or distance.

Syntax
Level

Ancestors(«Member», «Level»)

Returns all ancestors of «Member» at the level specified in «Level».

The set of returned members must all be from the same hierarchy, but «Level» does not need to be a level of the same hierarchy as «Member».

Distance

Ancestors(«Member», «Numeric Expression»)

Returns all members of the hierarchy that are «Numeric Expression» steps above «Member» in the hierarchy. This form of the Ancestors function is intended for cases in which the level of the parent is unknown or cannot be named. The set of returned members must all be from the same hierarchy.

Note  Ancestors(«Member», 0) returns «Member».

Remarks

Unlike the Ancestor function, Ancestors is a set value expression; it returns a set, not a member.

Examples

If the Geography dimension includes levels Country, State, and City, the following functions return the following values.

Expression Returns
Ancestors([Los Angeles], Country)
{ USA }
Ancestors([Los Angeles], State)
{ California }
Ancestors([Los Angeles], 0)
{ [Los Angeles] }
Ancestors([Los Angeles], 1)
{ California }
Ancestors([Los Angeles], 2)
{ USA }