DrilldownMember (MDX)

Drills down the members in a specified set that are present in a second specified set.

Alternatively, the function drills down on a set of tuples.

Syntax

DrilldownMember(Set_Expression1, Set_Expression2 [ , RECURSIVE ] )

Arguments

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

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

Remarks

This function returns a set of child members that are ordered by hierarchy, and includes members specified in the first set that are also present in the second set.**Parent members will not be drilled down if the first set contains the parent member and one or more children. The first set can have any dimensionality, but the second set must contain a one-dimensional set. Order is preserved among the original members in the first set, except that all child members included in the result set of the function are included immediately under their parent member. The function constructs the result set by retrieving the children for each member in the first set that is also present in the second set. If RECURSIVE is specified, the function continues to recursively compare the members of the result set against the second set, retrieving the children for each member in the result set that is also present in the second set until no more members from the result set can be found in the second set.

The first set can contain tuples instead of members. Tuple drilldown is an extension of OLE DB, and it returns a set of tuples instead of members.

Examples

The following example drills down into Australia, which is the member of the first set which is also present in the second set.

SELECT DrilldownMember 
   ( [Geography].[Geography].Children,
      {[Geography].[Geography].[Country].[Australia],
        [Geography].[Geography].[State-Province].[New South Wales]}
   )
   ON 0
   FROM [Adventure Works]

The following example drills down into Australia, which is the member of the first set which is also present in the second set. However, because the RECURSIVE argument is present, the function continues to recursively compare the members of the result set (members of the State-Province level) against the second set, retrieving the children for each member in the result set (members of the City level) that is also present in the second set until no more members from the result set can be found in the second set.

SELECT DrilldownMember 
   ( [Geography].[Geography].Children,
      {[Geography].[Geography].[Country].[Australia],
        [Geography].[Geography].[State-Province].[New South Wales]}
   ,RECURSIVE)
   ON 0
   FROM [Adventure Works]

See Also

Reference

MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History