Export (0) Print
Expand All
Expand Minimize

DrillupMember (MDX)

Returns the members in a specified set that are not descendants of members in a second specified set.


DrillupMember(Set_Expression1, Set_Expression2) 

Set_Expression1

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

Set_Expression2

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

The DrillupMember function returns a set of members based on the members specified in the first set that are descendants of members in the second set. 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. The function constructs the set by including only those members in the first set that are immediate descendants of members in the second set. If the immediate ancestor of a member in the first set is not present in the second set, the member in the first set is included in the set returned by this function. Descendants in the first set that precede an ancestor member in the second set are also included.

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

Important noteImportant

A member will get drilled up only if it is immediately followed by a child or a descendant. The order of members in the set matters for both the Drilldown* and Drillup* families of functions. Consider using the Hierarchize function to appropriately order the members of the first set.

Example

The following example drills up on the United States member, meaning that the member Colorado is not displayed on rows:

SELECT DrillUpMember

(

{[Geography].[Geography].[Country].[Canada]

,[Geography].[Geography].[Country].[United States]

,[Geography].[Geography].[State-Province].[Colorado]

,[Geography].[Geography].[State-Province].[Alberta]

,[Geography].[Geography].[State-Province].[Brunswick]

}

, {[Geography].[Geography].[Country].[United States]}

)

ON 0

FROM [Adventure Works]

However, since DrillupMember only drills up on those members that are followed immediately by descendants in the first set, it does not drill up on the Canada member in the following example:

SELECT DrillUpMember

(

{[Geography].[Geography].[Country].[Canada]

,[Geography].[Geography].[Country].[United States]

,[Geography].[Geography].[State-Province].[Colorado]

,[Geography].[Geography].[State-Province].[Alberta]

,[Geography].[Geography].[State-Province].[Brunswick]

}

, {[Geography].[Geography].[Country].[Canada]}

)

ON 0

FROM [Adventure Works]

The following example shows how the use of Hierarchize can avoid this issue, and drills up on the Canada member.

SELECT DrillUpMember

(

Hierarchize

(

{[Geography].[Geography].[Country].[Canada]

,[Geography].[Geography].[Country].[United States]

,[Geography].[Geography].[State-Province].[Colorado]

,[Geography].[Geography].[State-Province].[Alberta]

,[Geography].[Geography].[State-Province].[Brunswick]

}

), {[Geography].[Geography].[Country].[Canada]}

)

ON 0

FROM [Adventure Works]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft