Descendants (MDX)

Syntax

Member expression syntax using a level expression
Descendants(Member_Expression [ , Level_Expression [ ,Desc_Flag ] ] )

Member expression syntax using a numeric expression
Descendants(Member_Expression [ , Distance [ ,Desc_Flag ] ] )

Set expression syntax using a level expression
Descendants(Set_Expression [ , Level_Expression [ ,Desc_Flag ] ] )

Member expression syntax using a numeric expression
Descendants(Set_Expression [ , Distance [ ,Desc_Flag ] ] )

Arguments

• Member_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a member.

• Set_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a set.

• Level_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a level.

• Distance
  A valid numeric expression that specifies the distance from the specified member.

• Desc_Flag
  A valid string expression specifying a description flag that distinguishes among possible sets of descendants.

Remarks

If a level is specified, the Descendants function returns a set that contains the descendants of the specified member or the members of the specified set, at a specified level, optionally modified by a flag specified in Desc_Flag.

If Distance is specified, the Descendants function returns a set that contains the descendants of the specified member or the members of the specified set that are the specified number of levels away in the hierarchy of the specified member, optionally modified by a flag specified in Desc_Flag. Typically, you use this function with the Distance argument to deal with ragged hierarchies. If the specified distance is zero (0), the function returns a set that consists only of the specified member or the specified set.

If a set expression is specified, the Descendants function is resolved individually for each member of the set, and the set is created again. In other words, the syntax used for the Descendants function is functionally equivalent to the MDX Generate function.

If no level or distance is specified, the default value for the level used by the function is determined by calling the Level function (<<Member>>.Level) for the specified member (if a member is specified) or by calling the Level function for each member of the specified set (if a set is specified). If no level expression, distance or flags are specified, the function performs as if the following syntax were used:

Descendants

(

Member_Expression ,

Member_Expression.Level ,

SELF_BEFORE_AFTER

)

If a level is specified and a description flag is not specified, the function performs as if the following syntax were used.

Descendants

(

Member_Expression ,

Level_Expression,

SELF

)

By changing the value of the description flag, you can include or exclude descendants at the specified level or distance, the children before or after the specified level or distance (until the leaf node), and the leaf children regardless of the specified level or distance. The following table describes the flags allowed in the Desc_Flag argument.

Examples

SELECT Descendants
   ([Geography].[Geography].[Country].&[United States]
      //, [Geography].[Geography].[Country]
   , [Geography].[Geography].[City]
      //, [Geography].[Geography].Levels (3)
      //, SELF 
      //, AFTER
      , BEFORE
      // BEFORE_AND_AFTER
      //, SELF_AND_AFTER
      //, SELF_AND_BEFORE
      //,SELF_BEFORE_AFTER
      //,LEAVES 
   ) ON 0
FROM [Adventure Works] 

WITH MEMBER Measures.[Avg Gross Profit Margin] AS Avg
   (
      Descendants( [Date].[Fiscal].CurrentMember, 
           [Date].[Fiscal].[Date]
          ), 
        Measures.[Gross Profit Margin]
   )
SELECT
   Measures.[Avg Gross Profit Margin] ON COLUMNS,
   [Date].[Fiscal].[Month].Members ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Fiscal Year].&[2003])

WITH MEMBER Measures.x AS 
   [Measures].[Internet Sales Amount] / 
   ( [Measures].[Internet Sales Amount],
      Ancestors 
         ( [Customer].[Customer Geography].CurrentMember, 
           [Customer].[Customer Geography].[Country]
         ).Item (0)
   ), FORMAT_STRING = '0%'
SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0,
{Descendants 
   ( [Customer].[Customer Geography].[Country].&[Australia], 
     [Customer].[Customer Geography].[State-Province], SELF 
   )  
} ON 1
FROM [Adventure Works]

See Also

Reference