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 |
|---|---|
|
All states, counties and cities in USA |
|
All counties in USA |
|
All counties in USA |
|
All states in USA |
|
All cities in USA |
|
All states and cities in USA |
|
All states, counties, and cities in USA |
|
All states in USA and any leaf members between the Countries level and the States level. |
|
All states in USA |
|
All states, counties and cities in USA |