Collapse the table of content
Expand the table of content


SQL Server 2000


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


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».


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».


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


If the Geography dimension includes levels named 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 }

© 2015 Microsoft