HIERHEIGHT function

The HIERHEIGHT function returns the value of a node at a specified level for the first value in the current status list of a hierarchical dimension.

To populate a previously-defined relation with the values of a specified hierarchical dimension by level, use the HIERHEIGHT command.

Syntax

HIERHEIGHT(family-relation [,] level)

Return Value

The data type returned by HIERHEIGHT is the data type of the dimension value of family-relation.

Arguments

family-relation: A child-parent self-relation for the hierarchical dimension. The values of family-relation are the parents.
level: An INTEGER value that represents a level of the hierarchical dimension. The value 1 (one) represents the lowest-level of the hierarchical dimension.

Notes

Limiting the Hierarchical Dimension

The HIERHEIGHT function always returns a single value of the hierarchical dimension. When you do not limit the hierarchical dimension to a single value before calling the HIERHEIGHT function, the HIERHEIGHT function executes against the first value in the current status list of the dimension. Typically, you either limit the hierarchical dimension to a single value before you call the HIERHEIGHT function or you use the HIERHEIGHT function after FOR command in order to execute the HIERHEIGHT function for each value of the hierarchical dimension.

Examples

Example 14-11 Using HIERHEIGHT as a Simple Command

Assume that your analytic workspace has a hierarchical dimension named geography and a relation named g0.stanparent that is a self-relation of the geography values for the Standard hierarchy of geography.

DEFINE g0.newparent RELATION geography <geography>
LD Parent-child when hierarchy of geography is 1

Issuing a report command like REPORT g0.stanparent displays the values in g0.stanparent.

GEOGRAPHY          G0.STANPARENT
---------------- ----------------
World            NA
Americas         World
Canada           Americas
Toronto          Canada
Montreal         Canada
Ottawa           Canada
...              ...
USA              Americas
Boston           USA
LosAngeles       USA
...              ...
Mexico           Americas
Mexicocity       Mexico
Argentina        Americas
BuenosAires      Argentina
Brazil           Americas
Saopaulo         Brazil
Colombia         Americas
Bogota           Colombia
Australia        World
East.Aust        Australia
Sydney           East.Aust
Madrid           Spain
Budapest         Hungary
Athens           Greece
Vienna           Austria
Melbourne        East.Aust
Central.aust     Australia
Tai-pei          Taiwan
Singapore        Asia
Adelaide         Central.Aust
Bangkok          Thailand
West.aust        Australia
Newdelhi         India
Perth            West.Aust
Bombay           India
Malaysia         Asia
Europe           World
France           Europe
Caen             France
Paris            France

Now you limit GEOGRAPHY to the value Americas by issuing the following OLAP DML statement.

LIMIT geography TO 'Americas'

When you use the HIERHEIGHT function to find the node for Americas for the lowest-level of the hierarchy (level 1) by issuing the following OLAP DML statement.

REPORT HIERHEIGHT(g0.stanparent 1)

The following report is produced.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

When you use the HIERHEIGHT function to find the node for Americas for the highest-level of the hierarchy (level 4) by issuing the following OLAP DML statement.

REPORT HIERHEIGHT(g0.stanparent 4)

The following report is produced.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World

When you use the HIERHEIGHT function to find the node for Americas for the levels 2 and 3 of the hierarchy by issuing the following OLAP DML statements.

REPORT HIERHEIGHT(g0.stanparent 2)
REPORT HIERHEIGHT(g0.stanparent 3)

The following reports are produced.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Americas

Notice that the output for each level corresponds in between the values that are created for a relation created using HIERHEIGHT command. For example, assume you created a relation named geog.stanhierrel for the standard hierarchy for geography and limit geography to 'Americas. A report of geog.stanhierrel would show the same geography values for each level.

LIMIT geography TO 'AMERICAS'
REPORT DOWN geography geog.stanhierrel

                 ---------------------------GEOG.STANHIERREL--------------------
                 ----------------------------GEOG.LVLDIM------------------------
GEOGRAPHY               1                2                3                4
---------------- ---------------- ---------------- ---------------- ------------
Americas         NA               NA               Americas         World

Example 14-12 Using HIERHEIGHT After a FOR Command

Assume that your analytic workspace has a program named findnodes that finds the nodes of all of the geography values in status.

DEFINE FINDNODES PROGRAM
PROGRAM
VARIABLE level INTEGER
FOR geography
DO
counter = 1
WHILE counter LE statlen(geog.lvldim)
DO
REPORT HIERHEIGHT(g0.stanparent level)
level = level + 1
DOEND
DOEND
END

Assume also that you limit geography to Americas and Asia and call the HIERHEIGHT function for each level of the Standard hierarchy by issuing the following OLAP statements.

LIMIT geography TO 'Americas', 'Asia'
CALL findnodes

The output of the findnodes program for the geography values Americas and Asia is follows. The program first reports on the value of each level for Americas is provided. Then it reports on the value of each level for Asia.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Americas
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Asia
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World

Notice that the output for each level corresponds in between the values that are created for a relation created using the HIERHEIGHT command

LIMIT geography TO 'Americas' 'Asia'
REPORT DOWN geography geog.stanhierrel

                 ---------------------------GEOG.STANHIERREL--------------------
                 ----------------------------GEOG.LVLDIM------------------------
GEOGRAPHY               1                2                3                4
---------------- ---------------- ---------------- ---------------- ------------
Americas         NA               NA               Americas         World
Asia             NA               NA               Asia             World