Descendants

Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels. The members are returned in hierarchized order; for example, parent members are followed by child members.

Syntax

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

Parameter	Description
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.
AFTER—Include members below layer, but not the members of layer.
BEFORE—Include member and all its descendants that are higher in the hierarchy than layer, excluding layer and anything below it.
BEFORE_AND_AFTER—Include member and all its descendants, down to level 0, but excluding members in layer.
SELF_AND_AFTER—Include members in layer and all descendants below layer.
SELF_AND_BEFORE—Include member and all its descendants, down to and including layer.
SELF_BEFORE_AFTER—Include member and all its descendants.
LEAVES—Include only level-0 descendants between member and layer.

Example

The following query

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

returns the grid:

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]}