Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

Part Number E23381-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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)

Time Ancestor
2006 --
Q1.06 CY2006.Q1
Q2.06 CY2006.Q2
Q3.06 CY2006.Q3
Q4.06 CY2006.Q4
JAN-06 CY2006.Q1
FEB-06 CY2006.Q1
MAR-06 CY2006.Q1
APR-06 CY2006.Q2
MAY-06 CY2006.Q2
JUN-06 CY2006.Q2
JUL-06 CY2006.Q3
AUG-06 CY2006.Q3
SEP-06 CY2006.Q3
OCT-06 CY2006.Q4
NOV-06 CY2006.Q4
DEC-06 CY2006.Q4

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)