ToggleDrillState (MDX)

Toggles the drill state of members.

Syntax

ToggleDrillState(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

The ToggleDrillState function toggles the drill state of each member of the second set that is present in the first set. The first set can contain tuples with any dimensionality, but the second set must contain members of a single dimension. The ToggleDrillState function is a combination of the DrillupMember and DrilldownMember functions. If the member, m, of the second set is present in the first set, and that member is drilled down (that is, has a descendant immediately following it), then DrillupMember(Set_Expression1, {m}) is applied to the member or tuple in the first set. If that m member is drilled up (that is, there is no descendant of m that immediately follows m), DrilldownMember(Set_Expression1, {m}[, RECURSIVE]) is applied to the first set.

If the optional RECURSIVE flag is used, drill up and drill down are applied recursively. For more information about the recursive flag, see the DrillupMember and DrilldownMember functions.

Example

The following example drills down on the Australia member of the first set, and drills up on the United States member of the first set.

SELECT ToggleDrillState
   ([Geography].[Geography].[Country].Members,
      {[Geography].[Geography].[Country].[Australia]
      , [Geography].[Geography].[State-Province].[California]}
      --, RECURSIVE
   ) ON 0
   FROM [Adventure Works]

See Also

Reference

MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.