../E23381-01.epub /> ../E23381-01.mobi />

HIER_TOP

HIER_TOP returns the topmost ancestor of either all members of a hierarchy or a particular member. The hierarchy can be either level-based or value-based.

Return Value

VARCHAR2

Syntax

HIER_TOP ( 
   [member_expression] [WITHIN]
   {DIMENSION dimension_id | HIERARCHY hierarchy_id} ) 

Arguments

member_expression

Identifies a single dimension member within the hierarchy used for the calculation. 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.

Example

This example returns the top member of the default hierarchy of the Time dimension.

HIER_TOP(DIMENSION "TIME")

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

The next example returns TOTAL, which is the top member for Europe in the default hierarchy of the Customer dimension.

HIER_TOP('EMEA' WITHIN DIMENSION CUSTOMER)