HIER_ANCESTOR

HIER_ANCESTOR returns the ancestor at a particular level of a hierarchy for either all members in the hierarchy or a particular member. The hierarchy must be level-based.

Return Value

VARCHAR2

Syntax

HIER_ANCESTOR( [ member [WITHIN] ]
                 dimension | hierarchy
                 LEVEL level )

Arguments

member is a value of hierarchy whose ancestor is calculated. Otherwise, the ancestor of all members of the hierarchy are calculated.

dimension is the dimension whose members are used in the calculation.

hierarchy is a hierarchy of dimension. Otherwise, the default hierarchy is used.

level is the level of the ancestor.

Example

The following example returns the ancestor at the Quarter level for members of the Calendar hierarchy.

HIER_ANCESTOR(GLOBAL.TIME LEVEL GLOBAL.TIME.CALENDAR_QUARTER)

Example 1
Member	Quarter
2003	null
Q1-03	Q1-03
Q3-03	Q3-03
Mar-03	Q1-03
Jul-03	Q3-03

The next example returns the ancestor at the Fiscal Quarter level for Jul-03.

HIER_ANCESTOR('Jul-03' WITHIN GLOBAL.TIME LEVEL GLOBAL.TIME.FISCAL_QUARTER)

Example 2
Member	Quarter
Jul-03	FY-Q1-04