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 |
---|---|
The member for which descendants are sought. | |
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. |
Notes
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]}