# MDX Set Functions: The ToggleDrillState() Function - Page 2

May 1, 2006

### The ToggleDrillState() Function

#### Introduction

According to the Books Online, the ToggleDrillState() function "toggles the drill state of each member of" a specified secondary set that is also present in a specified primary set. Moreover, "the ToggleDrillState() function is a combination of the DrillUpMember() and DrilldownMember() functions." If indeed such a member of the secondary set is present in the primary set we specify, and if that member is in a drilled down state (thereby having a descendant), the DrillUpMember() function is applied to the primary set. If the member is already drilled up (thereby presenting no descendant immediately following the member), DrillDownMember() is, in effect, applied to the primary set.

ToggleDrillState() has many applications, and pairing it with other MDX functions can help us to leverage its power even further. I frequently use it in combination with various functions, including the Descendants() function and numerous others. We will examine in detail the syntax for the ToggleDrillState() function after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the function. This will afford us an opportunity to explore some of the delivery options that ToggleDrillState() can offer the knowledgeable user. Handson practice with ToggleDrillState(), where we will create queries that leverage the function, will help us to activate what we learn in the Discussion and Syntax sections.

#### Discussion

To restate our initial explanation of its operation, the ToggleDrillState() function "reverses" the drilled state ("drilled up" or drilled down") of any member that we specify within a secondary set that also resides in a specified primary set. In short, if the specified member in the primary set has no descendants (is in a "drilled up" state), then the function applies the action of the DrillDownMember() function. If the specified member in the primary set has descendants (is in a "drilled down" state), then the function applies the action of the DrillUpMember() function.

In returning a set of member(s) from the primary set whose drilled state is, in effect, reversed (assuming that the member(s) appear in the specified secondary set within the function), ToggleDrillState() allows the primary set to contain "mixed dimensionality." The members of the secondary set, however, must be of the same dimension.

ToggleDrillState() also makes available an optional RECURSIVE flag. RECURSIVE is applicable only in scenarios where we are achieving DrillDownMember() action, as many of us might have concluded from the fact that MDX makes a RECURSIVE flag available for the DrillDownMember() function, but not for the DrillUpMember() function. (We might also have concluded that the concept of recursion is not meaningful in a drillup scenario, anyway). When we specify RECURSIVE in our ToggleDrillState() function, the set that results from the initial "pass" through the members / tuples of the specified primary set is compared to each member of the specified secondary set, further adding to the returned set based upon the results of the second "pass." A match is performed with each successive result set until no more additional members in the newly assembled set are determined to exist in the secondary set. When we do not add the RECURSIVE keyword, only a single "pass" is performed through the primary set, returning the set that results from drilling down upon each member / tuple in the specified primary set that that is resident in the specified secondary set – giving the effect of a singlelevel drilldown for matches, as we shall see in our Practice section.

NOTE: For a more involved discussion of the workings of RECURSIVE (as well as other considerations) within the DrillDownMember() function, see my article Set Functions: The DrillDownMember() Function, a member of the MDX Essentials series here at Database Journal.

ToggleDrillState() has much in common with other members of the "drilling" family of MDX functions, within the myriad opportunities it affords to couple it with other functions, and to deliver a wide range of analysis and reporting utility. Moreover, as we have noted to be the case in many scenarios within the Microsoft integrated Business Intelligence solution (consisting of MSSQL Server, Analysis Services and Reporting Services), this function, residing within the Analysis Services layer, can be extended to support capabilities and attributes in the Reporting Services layer. Knowing "where to put the intelligence" among the various layers is critical to optimization, in many cases. For more of my observations on this subject, see MultiLayered Business Intelligence Solutions ... Require MultiLayered Architects.

Let's look at syntax specifics to further clarify the operation of ToggleDrillState().

#### Syntax

Syntactically, anytime we employ the ToggleDrillState() function to "reverse" the current drilled state of members or tuples of a specified primary set, we return drilled up or drilled down members, based upon the existence of those same members within the specified secondary set. We specify the primary Set Expression (which can be multidimensional) within the parentheses to the right of the ToggleDrillState keyword. We then follow the primary Set Expression with the secondary Set Expression. Finally, we can add the optional RECURSIVE keyword to the right of the Set Expressions within the parentheses, as desired. The general syntax is shown in the following string:

`ToggleDrillState(Set_Expression1, Set_Expression2 [ , RECURSIVE] )`

When we omit the RECURSIVE flag, ToggleDrillState() performs a single pass through the primary set in its match of each member / tuple with the respective member in the secondary set. Adding the RECURSIVE keyword results in the sequential matching of the result set being constructed by the function, passbypass as it is assembled, until no further members with a match in the secondary set remain.

Let's take a look at an illustration. The following snippet employs the ToggleDrillState() function:

```
TOGGLEDRILLSTATE(
{[Geography].[Geography].[United States].CHILDREN},
{[Geography].[Geography].[United States].[Georgia]}
) ON AXIS(1)
```

This rowsaxis specification, within a proper query executed against the Adventure Works sample cube that, say, specified a crossjoin of all Calendar Years with the Reseller Sales Amount measure within the columns axis, might produce a results dataset similar to that partially depicted in Illustration 1.

Illustration 1: Results Dataset – Georgia Drilled Down to Constituent Cities (Partial View)

In the example dataset, we see that the each of the cities of StateProvince Georgia contained in the cube appear alongside the summary Reseller Sales Amount for each for the respective Calendar Years. Our placement of the sets directs the ToggleDrillState() function to drill down into StateProvince Georgia ([Geography].[Geography].[United States].[Georgia]]), because member Georgia is present in the secondary set, while it does not present descendants (and is thus in "drilled up" state) in the primary set. The other stateprovinces of the United States are displayed, but in summary ("drilled up") state, because they are not specified in the secondary set, although the "States of the United States" are specified within the primary set ([Geography].[Geography].[United States].CHILDREN).

We will examine the results of a query containing the above as part of our practice with the ToggleDrillState() function in the section that follows.