Descendants

Analysis Services

Analysis Services

Descendants

Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.

Syntax
Level

Descendants(«Member», [«Level»[, «Desc_flags»]])

Returns the set of descendants of a member, specified by «Member», at the level specified by «Level», optionally modified by a flag specified in «Desc_flags».

If no «Level» or «Desc-flags» arguments are specified, the function behaves as in following syntax:

Descendants(«Member», «Member».Level, SELF_BEFORE_AND_AFTER)

Distance

Descendants(«Member», «Distance»[, «Desc_flags»])

Returns the descendants of a member, specified by «Member», that are «Distance» steps away in the hierarchy, optionally modified by a flag specified in «Desc_flags». This syntax is typically used to deal with ragged hierarchies. Specifying a «Distance» of 0 returns a set consisting only of the member specified in «Member».

Flags
Flag Description
SELF Default. Returns descendant members from «Level» only. Includes «Member», if and only if «Level» specified is the level of «Member».
AFTER Returns descendant members from all levels subordinate to «Level».
BEFORE Returns descendant members from all levels between «Member» and «Level», not including members from «Level».
BEFORE_AND_AFTER Returns descendant members from all levels subordinate to the level of «Member» except members from «Level».
SELF_AND_AFTER Returns descendant members from «Level» and all levels subordinate to «Level».
SELF_AND_BEFORE Returns descendant members from «Level» and all levels between «Member» and «Level».
SELF_BEFORE_AFTER Returns descendant members from all levels subordinate to the level of «Member».
LEAVES Returns leaf descendant members between «Member» and «Level» or «Distance».
Remarks

By default, only members at the specified level or distance will be included. This function corresponds to a «desc_flags» value of SELF. By changing the value of «desc_flags», you can include or exclude descendants at the specified level or distance, the children before or the children after the specified level or distance (until the leaf node), as well as all of the leaf children regardless of the specified level or distance.

Example

Assume the levels in the Location dimension are named (in hierarchical order) Countries, States, Counties, and Cities.

Expression Returns
Descendants(USA)
All states, counties and cities in USA
Descendants(USA, Counties)
All counties in USA
Descendants(USA, Counties, SELF)
All counties in USA
Descendants(USA, Counties, BEFORE)
All states in USA
Descendants(USA, Counties, AFTER)
All cities in USA
Descendants(USA, Counties, BEFORE_AND_AFTER)
All states and cities in USA
Descendants(USA, Counties, SELF_BEFORE_AFTER)
All states, counties, and cities in USA
Descendants(USA, States, LEAVES)
All states in USA and any leaf members between the Countries level and the States level.
Descendants(USA, 1)
All states in USA
Descendants(USA, 2, SELF_BEFORE_AFTER)
All states, counties and cities in USA

See Also

Level