Descendants

Descendants

SQL Server 2000

Descendants

  New Information - SQL Server 2000 SP3.

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» | «Set», [«Level»[, «Desc_flags»]])

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

If a set is specified, the Descendants function is resolved individually for each member of the set, then the set is recreated. In other words, the syntax used for the Descendants statement is functionally equivalent to the following MDX statement:

Generate(«Set», Descendants(«Set».CurrentMember, «Level», «Desc_flags»), ALL)

If the «Level» parameter is not specified, the default value for the parameter is determined by calling the Level MDX function for the specified member (if «Member» is specified) or by calling the Level MDX function for each member of the specified set (if «Set» is specified.)

If the «Desc_flags» parameter is not specified, the default value for the parameter is set to SELF.

Distance

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

Returns the descendants of a member specified by «Member» or of a set specified by «Set», 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».

If a set is specified, the Descendants function is resolved individually for each member of the set, then the set is recreated. In other words, the syntax used for the Descendants statement is functionally equivalent to the following MDX statement:

Generate(«Set», Descendants(«Set».CurrentMember, «Distance», «Desc_flags»), ALL)

If the «Desc_flags» parameter is not specified, the default value for the parameter is set to SELF.

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
Descendants({USA, France}, Cities)
All cities in USA and France.

See Also

Level

Show:
© 2016 Microsoft