Ancestors

The MDX Ancestor function for Essbase takes the input member and a layer or distance, and returns a set of ancestors along with the input member.

When the layer specification is a level, this function returns all ancestors having a level no greater than the input level. For example, Ancestors ([Additions], [Measures].Levels(2)) returns {[Inventory] , [Additions]}.

Syntax

Ancestors ( member , layer | index )

Parameters

member

The member for which a set of ancestors is sought.

layer

Layer specification.

index

A number of hierarchical steps up from member, locating the highest ancestor you want returned in the result set.

Notes

• Do not use negative numbers for index. If you want to return lower members, use Descendants instead of Ancestors. Ancestors([Qtr1], -1) would return an empty member, not a descendant.

• If you use layer to specify a level but no ancestors exist at that level, then the return value is an empty member.

Example

Ancestors ( [New York], [Market].levels(2) )

returns {[Market], [East], [New York]}, the self-inclusive set of [New York] ancestors beginning with the ancestor that is located at level 2 of the Market dimension.

Ancestors ( [Feb], 1 )

returns {[Qtr1],[Feb]}, the self-inclusive set of ancestors beginning with the ancestor one step higher than Feb.

Ancestors ( [Feb], 0 )

returns {[Feb]}.

Using the ASOSamp.Basic database,

Ancestors ([94089], [Geography].generations(2))

returns {[West], [CA], [SUNNYVALE - CA], [94089]}, the self-inclusive set of 94089 ancestors beginning with the second generation of the Geography dimension.