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.
The data type returned by HIERHEIGHT is the data type of the dimension value of parentrel.
A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.
An INTEGER value that represents a level of the hierarchical dimension. The value 1 (one) represents the lowest-level of the hierarchical dimension.
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 a FOR statement to execute the HIERHEIGHT function for each value of the hierarchical dimension.
Example 7-109 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 statement 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 ... ... 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 7-110 Using HIERHEIGHT After a FOR Statement
Assume that your analytic workspace has the following 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