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_expression] [WITHIN]
  {DIMENSION dimension_id | HIERARCHY hierarchy_id}
  {DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id} ) 

Arguments

member_expression

Identifies a dimension member within the hierarchy whose ancestor is returned. If this optional argument is specified, then the result does not vary across dimension members.

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The level of the ancestor in dimension_id.

hier_level_id

The level of the ancestor in hierarchy_id.

Example

This example returns the ancestor at the Calendar Quarter level for members of the default Calendar hierarchy of the Time dimension.

HIER_ANCESTOR(DIMENSION "TIME" DIMENSION LEVEL TIME.CALENDAR_QUARTER)

The next example returns GOV as the ancestor of the US Department of Labor at the Customer Market Segment level in the Market hierarchy of the Customer dimension.

HIER_ANCESTOR('US DPT LBR' WITHIN HIERARCHY CUSTOMER.MARKET DIMENSION LEVEL CUSTOMER.MARKET_SEGMENT)