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 [WITHIN] ]
             dimension | hierarchy)

Arguments

member is a value of hierarchy whose top ancestor is calculated. Otherwise, the top ancestors of all members of the hierarchy are calculated.

dimension is the dimension whose members are used in the calculation.

hierarchy is a hierarchy of dimension. Otherwise, the default hierarchy is used.

Example

The following example returns the top member of the Calendar hierarchy for all members in that hierarchy.

HIER_TOP(GLOBAL.TIME.CALENDAR)

Example 1
Time Member	Top Member
2002	2002
Q3-02	2002
Jul-02	2002
2003	2003
Q3-03	2003
Jul-03	2003

The next example returns the top member of the default Fiscal hierarchy for Jul-03.

HIER_TOP('Jul-03' WITHIN GLOBAL.TIME)

Example 2
Time Member	Top Member
Jul-03	FY-04