![]() Previous |
![]() Next |
HIER_LEVEL
returns the level of either all members of a hierarchy or a particular member. The hierarchy must be level-based.
Return Value
VARCHAR2
Syntax
HIER_LEVEL ( [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 level of each member of the default hierarchy of the Time dimension.
HIER_LEVEL(DIMENSION "TIME")
Time | Level |
---|---|
2006 | CALENDAR_YEAR |
Q1.06 | CALENDAR_QUARTER |
Q2.06 | CALENDAR_QUARTER |
Q3.06 | CALENDAR_QUARTER |
Q4.06 | CALENDAR_QUARTER |
JAN-06 | MONTH |
FEB-06 | MONTH |
MAR-06 | MONTH |
APR-06 | MONTH |
MAY-06 | MONTH |
JUN-06 | MONTH |
JUL-06 | MONTH |
AUG-06 | MONTH |
SEP-06 | MONTH |
OCT-06 | MONTH |
NOV-06 | MONTH |
DEC-06 | MONTH |
The next example returns ACCOUNT
as the level of Business World in the Market hierarchy of the Customer dimension.
HIER_LEVEL('BUSN WRLD' WITHIN HIERARCHY CUSTOMER.MARKET)