1. Calculation and Query Reference for Oracle Essbase
  2. MDX
  3. MDX Function List
  4. Descendants

Descendants

The MDX Descendants function for Essbase returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.

This function returns the members in hierarchized order; for example, parent members are followed by child members.

Syntax

Descendants ( member , [{ layer | index }[, Desc_flags ]])

Parameters

member

The member for which descendants are sought.

layer

Optional. Layer specification indicating the depth of the descendants to return.

index

Optional. A number of hierarchical steps down from member, locating the descendants you want returned.

Desc_flags

Optional. Keywords which further indicate which members to return. These keywords are available only if layer or index is specified.

See Values for Desc_flags

Notes

Values for Desc_flags

For all flags, SELF refers to layer; therefore, BEFORE indicates "before the layer" and AFTER indicates "after the layer."

  • SELF—Include only members in layer, including member only if member is in layer.


    Hierarchy diagram with layer selected.

  • AFTER—Include members below layer, but not the members of layer.


    Hierarchy diagram with the level below layer selected.

  • BEFORE—Include member and all its descendants that are higher in the hierarchy than layer, excluding layer and anything below it.


    Hierarchy diagram with selection of member and its descendants up to but not including layer.

  • BEFORE_AND_AFTER—Include member and all its descendants, down to level 0, but excluding members in layer.


    Hierarchy diagram with selection of member and all its descendants, excepting layer.

  • SELF_AND_AFTER—Include members in layer and all descendants below layer.


    Hierarchy diagram with layer and the levels below layer selected.

  • SELF_AND_BEFORE—Include member and all its descendants, down to and including layer.


    Hierarchy diagram with the member and the levels above layer selected, including layer.

  • SELF_BEFORE_AFTER—Include member and all its descendants.


    Hierarchy diagram with member, layer, and all other levels selected.

  • LEAVES (Not pictured) — Include all descendants of member in the defined layer, and the level-0 descendants between member and layer.

Example

The following query

SELECT
 Descendants ( [Year] )
ON COLUMNS
FROM sample.basic

returns the grid:

Table 4-55 Output Grid from MDX Example

Year Qtr1 Jan Feb Mar Qtr2 Apr May Jun Qtr3 Jul Aug Sep Qtr4 Oct Nov Dec
12656 2747 924 888 935 3352 1011 1071 1270 3740 1334 1304 1102 2817 907 884 1026

The following expressions return the following sets

Descendants ( [Year], 2 )

returns {([Jan]:[Dec])}, which is the range of members found two steps below Year. 

Descendants ( [Year], 2, BEFORE )

returns {[Year], [Qtr1], [Qtr2], [Qtr3], [Qtr4]}, which is the set of Year and its descendants that occur BEFORE the layer that is two steps below Year. 

Descendants ( [Market], [West].level )

returns {[East], [West], [South], [Central]}, which is the set of Market's descendants found at the level of West. 

Descendants([Market])

is equivalent to Descendants([Market], [Market].level, SELF_BEFORE_AFTER). It returns all descendants of Market: 

{[Market],
 [East], [New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire],
 [West], [California], [Oregon], [Washington], [Utah], [Nevada],
 [South], [Texas], [Oklahoma], [Louisiana], [New Mexico],
 [Central], [Illinois], [Ohio], [Wisconsin], [Missouri], [Iowa], [Colorado] }
Descendants([Market], [Region])

is equivalent to Descendants([Market], [Region]), SELF), where [Region] is an alias. It returns all members at [Region] level: 

{[East], [West], [South], [Central]}
Descendants([Market], [State], SELF)

returns all descendants of [Market] at [State] level: 

{[New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire],
 [California], [Oregon], [Washington], [Utah], [Nevada], [Texas],
 [Oklahoma], [Louisiana], [New Mexico], [Illinois], [Ohio], [Wisconsin],
 [Missouri], [Iowa], [Colorado]}
Descendants([Market], [State], BEFORE)

returns all regions and [Market]: 

{[Market], [East], [West], [South], [Central]}
Descendants([Market], [State], AFTER)

returns an empty set, because there are no levels below [State] level in the [Market] dimension: 

{}
Descendants([Market], [Region], AFTER)

returns all states in the [Market] dimension: 

{[New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire],
 [California], [Oregon], [Washington], [Utah], [Nevada], [Texas],
 [Oklahoma], [Louisiana], [New Mexico], [Illinois], [Ohio], [Wisconsin],
 [Missouri], [Iowa], [Colorado]}
Descendants([Market], [State], LEAVES)

returns all level-0 members between [Market] level and [State] level, including both levels: 

{[New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire],
 [California], [Oregon], [Washington], [Utah], [Nevada], [Texas],
 [Oklahoma], [Louisiana], [New Mexico], [Illinois], [Ohio], [Wisconsin],
 [Missouri], [Iowa], [Colorado]}
Descendants([Market], 1)

The second argument specifies a distance of 1 from [Market] level, which is [Region] level. So this expression is equivalent to Descendants([Market], [Region]). It returns: 

{[East], [West], [South], [Central]}
Descendants([Market], 2, SELF_BEFORE_AFTER)

is equivalent to Descendants([Market], [State], SELF_BEFORE_AFTER). It returns: 

{[Market], 
 [East], [New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire]
 [West], [California], [Oregon], [Washington], [Utah], [Nevada],
 [South], [Texas], [Oklahoma], [Louisiana], [New Mexico],
 [Central], [Illinois], [Ohio], [Wisconsin], [Missouri], [Iowa], [Colorado] }
Descendants([Market], -1, SELF_BEFORE_AFTER)

prints a warning in application log, because a negative distance argument is not valid. The expression returns an empty set: 

{}
Descendants([Market], 10, SELF)

returns an empty set, because there are no descendants of [Market] at a distance of 10 from [Market] level.

Descendants([Market], 10, BEFORE)

returns all descendants of [Market]: 

{[Market],
 [East], [New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire]
 [West], [California], [Oregon], [Washington], [Utah], [Nevada],
 [South], [Texas], [Oklahoma], [Louisiana], [New Mexico],
 [Central], [Illinois], [Ohio], [Wisconsin], [Missouri], [Iowa], [Colorado] }
Descendants([Market], 10, LEAVES)

returns all level-0 descendants of [Market]: 

{[New York], [Massachusetts], [Florida], [Connecticut], [New Hampshire],
 [California], [Oregon], [Washington], [Utah], [Nevada], [Texas],
 [Oklahoma], [Louisiana], [New Mexico], [Illinois], [Ohio], [Wisconsin],
 [Missouri], [Iowa], [Colorado]}