Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize
1 out of 3 rated this helpful - Rate this topic

DrilldownMember (MDX)

Updated: 17 July 2006

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.


DrilldownMember(Set_Expression1, Set_Expression2 [ , RECURSIVE ] )
Set_Expression1

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

Set_Expression2

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

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.

ms145580.note(en-US,SQL.90).gifImportant:
A member will not get drilled down into if it is immediately followed by one of its children. The order of members in the set matters for both the Drilldown* and Drillup* families of functions.

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]

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.