Hierarchize

The MDX Hierarchize function returns members of a set in their hierarchical order as represented in the Essbase database outline.

Syntax

Hierarchize ( set [,POST] )

Parameters

set: Set specification.
POST: If this keyword is used, child members are returned before their parents.

Notes

This function returns members of a set in their hierarchical order as represented in the database outline (viewed from top-down by default, meaning that parent members are returned before their children).

If POST is used, child members are returned before their parents (the view changes to bottom-up). For example,

Hierarchize({Child, Grandparent, Parent})

returns {Grandparent, Parent, Child}.

Hierarchize({Child, Grandparent, Parent}, POST)

returns {Child, Parent, Grandparent}.

Example

Example 1

The following expression

Hierarchize({May, Apr, Jun})

returns the set:

{Apr, May, Jun}

Therefore, the following query

Select 
Hierarchize({May, Apr, Jun})
on columns from sample.basic

returns the grid:

Table 4-77 Output Grid from MDX Example

Apr	May	Jun
8644	8929	9534

Example 2

The following expression

Hierarchize({May, Qtr2, Apr, Jun})

returns the set:

{ Qtr2 Apr May Jun }

Therefore, the following query

Select 
Hierarchize({May, Qtr2, Apr, Jun})
on columns from sample.basic

returns the grid:

Table 4-78 Output Grid from MDX Example

Qtr2	Apr	May	Jun
27107	8644	8929	9534

Example 3

The following expression

Hierarchize({May, Qtr2, Apr, Jun}, POST)

returns the set:

{Apr, May, Jun, Qtr2}

Therefore, the following query

Select 
Hierarchize({May, Qtr2, Apr, Jun}, POST)
on columns from sample.basic

returns the grid:

Table 4-79 Output Grid from MDX Example

Apr	May	Jun	Qtr2
8644	8929	9534	27107

Example 4

The following query

Select 
Hierarchize({Dec, Year, Feb, Apr, Qtr1, Jun, Qtr2}, POST)
on columns,
Hierarchize({Margin, Sales})
on rows
from sample.basic

returns the grid:

Table 4-80 Output Grid from MDX Example

(axis)	Feb	Qtr1	Apr	Jun	Qtr2	Dec	Year
Margin	17762	52943	18242	19457	56317	18435	221519
Sales	32069	95820	32917	35088	101679	33342	400855