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

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 was specified.
For all flags, SELF refers to layer; therefore, BEFORE indicates "before the layer" and AFTER indicates "after the layer." Values:
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.

Examples

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

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com