HIER_ORDER

HIER_ORDER sorts the members of a dimension with children immediately after their parents, and returns a sequential number for each member.

Return Value

NUMBER

Syntax

HIER_ORDER ( 
   [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 orders the values of the Time dimension:

HIER_ORDER(DIMENSION "TIME")

The next example returns 78 as the order number of Business World in the Market hierarchy of the Customer dimension.

HIER_ORDER('BUSN WRLD' WITHIN HIERARCHY CUSTOMER.MARKET)