Oracle® OLAP Expression Syntax Reference Release 11.2 Part Number E23381-01 |
|
|
PDF · Mobi · ePub |
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.
VARCHAR2
HIER_ANCESTOR( [member_expression] [WITHIN] {DIMENSION dimension_id | HIERARCHY hierarchy_id} {DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id} )
OLAXS219member_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.
OLAXS220dimension_id
The dimension over which the values are calculated using the default hierarchy.
OLAXS221hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
OLAXS222dim_level_id
The level of the ancestor in dimension_id
.
OLAXS223hier_level_id
The level of the ancestor in hierarchy_id
.
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)