MDX Set Functions: The ToggleDrillState() Function

Monday May 1st 2006 by William Pearson

Architect Bill Pearson returns to expose the MDX TOGGLEDRILLSTATE() function, continuing his extended examination of the mechanics of drilling up and down within MDX.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide handson application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific realworld needs.

Virtually all of the MDX we have constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and various other areas within the Microsoft integrated Business Intelligence solution, and much of what we construct going forward can be executed in the Analysis Services 2000 MDX Sample Application (assuming connection to an appropriate Analysis Services data source). MDX as a language continues to evolve and expand: we will focus on many new features in articles to come, while still continuing to examine business uses of MDX in general. The use of MDX to meet the realworld needs of our business environments will continue to be my primary concentration within the MDX Essentials series.

For more information about the series in general, as well as the software and systems requirements for getting the most out of its member lessons, please see Set Functions: The DrillDownMember() Function, where important information is detailed regarding the applications, samples and other components required to complete our practice exercises.


In this article, we will continue the examination of "MDX for drilling up and down" that we began in Set Functions: The DrillDownMember() Function. We have discussed the nature of drilling, in general, in previous articles of our subseries, stating that it comprises an analytical technique through which means an information consumer can maneuver between summarized ("drilling up") and detailed ("drilling down") levels of data. We noted that either of drilling up or drilling down occurs along the lines of drilling paths that are defined within the structure of our cubes, and which are typically specified by the cube's dimensional hierarchies. We mentioned, too, that these paths can be based upon alternative relationships that exist within or between dimensions.

To repeat an example I have used to illustrate the general concept, drilling down might be described as follows, based upon an engagement within which I have recently been involved: An executive within a state Department of Education wishes to examine annual Adequate Yearly Progress (AYP) data within a cube constructed primarily to allow comparison and analysis of those results at various levels. The executive can examine national averages, numbers of test takers, and various other measures, and then perform a drilldown operation within a Geography dimension that might then present the data by states. He could then perform subsequent drilldowns to display state regions (or perhaps even congressional districts), counties / school systems, and more. Depending upon the design of the cube, the drilldown process could ultimately take the consumer to the level of the individual schools – perhaps even to the individual students themselves.

Drilling up is a capability that might be taken for granted in a drilldown scenario such as the foregoing. From any of the drilled down levels at which the information consumer finds himself or herself, a corresponding capability to navigate upward, from more detailed levels of data to more summarized levels of data, would be an expected complementary feature.

In the earlier articles, Set Functions: The DrillDownMember() Function and Set Functions: The DrillUpMember() Function, we introduced the "primary," memberdirected MDX drilldown functions, DrillDownMember() and DrillUpMember(), respectively. In this article, we will examine a function, ToggleDrillState(), that compounds the actions of these two functions, and whose objective, like that of most of the other drill functions that we have examined within this series, is to serve as a mechanism to transform the actions of information consumers, within an end user application (at, say, the reporting layer), into a corresponding MDX operation.

In a manner similar to that of other functions that we have examined in the MDX Essentials series, ToggleDrillState() can be useful in a host of different reporting and analysis applications. Along with other "navigational" functions like it (virtually all of which we examine in other articles of this series) ToggleDrillState() allows us to exercise a great deal of presentation sleight of hand, in working with MDX in Analysis Services, as well as within Reporting Services and various other reporting applications that can access an Analysis Services cube. Like other MDX functions in general, the ToggleDrillState() function can be leveraged, within and among the various "layers" of the Microsoft integrated Business Intelligence solution, to support sophisticated presentations and features. We will introduce the function, commenting upon its operation and touching upon examples of effects that we can employ it to deliver. As a part of our discussion, we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

The ToggleDrillState() Function


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.


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().


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:

   {[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.


Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the ToggleDrillState() function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place ToggleDrillState() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent, as in all the practice sessions of this series, is to demonstrate the operation of the ToggleDrillState() function in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 2.

Click for larger image

Illustration 2: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 3.

Illustration 3: Exposing the Analysis Services Databases in the Object Browser ...

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 3 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 4.

Illustration 4: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens. We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.

Illustration 5: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 6.

Illustration 6: Click the New Query Button with the Adventure Works Cube Selected

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as depicted in Illustration 7.

Illustration 7: Adventure Works Cube Metadata Appears ...

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among others, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions. Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient. We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct. For more information on our use of the query editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series' explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

Procedure: Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of clientfacing authors and developers, often requests assistance such as this. As a part of our relationship with Adventure Works, as well as with other clients, we provide onsite augmentation for business requirements gathering and training, performing workshops, in many cases, that illustrate approaches to meeting specific needs. These combined development workshops / "train the trainer" events have worked well in the past for all concerned.

As usual, the authors and developers in the group are aware that the particular need that they are currently expressing will manifest itself in recurring situations as they work to meet the daily requirements of the Adventure Works information consumers. This particular request for assistance involves scenarios where they feel that the ToggleDrillState() function might be highly useful.

We have previously assisted the Reporting department representatives in the performance of drilldowns (see Set Functions: The DRILLDOWNMEMBER() Function and Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), as well as with drillups (MDX Set Functions: The DRILLUPMEMBER() Function) within the context of members. Moreover, we have supported them specifically with using MDX to support levelbased drilling capabilities (MDX Set Functions: DrillDownLevel(), Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions and Set Functions: The DrillUpLevel() Function). Having been exposed to the majority of the drilling functions in previous visits, the client representatives now make known their wish to extend these recent excursions into an examination of the "capstone" MDX drilling function, ToggleDrillState(), since they understand that the function can be used to "reverse" the current drilled state of members of a specified primary set. They are looking forward to extending the use of the function to take advantage of parameterization within Reporting Services.

In short, and quite expectedly, the Reporting department representatives explain that they want to perform these "reverse drills", using ToggleDrillState() as the mechanism, for the same reason that they wanted to perform member and level drilling (up and down) with the other MDX drilling functions. They wish to leverage the Analysis Services and Reporting Services components of the Microsoft integrated Business Intelligence solution, whereby they can create a targeted "drill up" or "drill down" report that will be triggered from a primary, lower level report (and whose purpose is to "reverse" the drill state within the primary report). They then whish to "connect" the two in a manner similar to that to which we have alluded in other articles. They might also parameterize the dataset(s) within an OLAP report to use conditional logic, based upon a parameter, to add or remove the ToggleDrillState() function from around an existing rows or columnsaxis specification in a way that the desired drill state can be enacted, to achieve drilled up or drilled down member perspectives within the report(s) resting upon those datasets, or to perhaps take another approach entirely to deliver drilling capability within their reports. As we stated in previous articles surrounding the MDX "drilling functions," whichever approach the developers might take to support a given reporting or analysis need, they would likely work with combinations of drilldown and drillup functions to achieve versatility in the end presentations. In the current scenario, they might achieve the "reversal" of an existing drilled state by simply enclosing the appropriate part of an existing MDX query with the ToggleDrillState() function – a circumstance that, in itself, presents a scenario that affords easy and flexible parameterization.

We convince the authors that they will initially want to examine a case where we establish a drilled state within a query, upon which we can then apply the ToggleDrillState() function to "reverse" the existing state. A simple way to accomplish this will be to use the .Children function in the definition of a rows axis, as we shall see, to generate a "drilled down" state within the results dataset. A subsequent step will then be to introduce ToggleDrillState() within the scenario to reverse the "drilled down" effects that result from our existing axis definition. (We also note that this approach will leave the developers with a complementary "pair" of examples that can be used to illustrate the use of the .Children and ToggleDrillState() functions in tandem – a means to the parameterization ends that I discussed earlier that I often leverage).

In the case at hand, the authors ask that we begin with a query that returns Calendar Year Reseller Sales information for each of the States in which Adventure Works experienced activity, presenting the organization's summarized total sales for the United States, together with the sales values for each of the individually broken out States that make up the United States totals. To simplify setup, as well as to demonstrate the complementary use of the .Children function with the ToggleDrillState() function in cases of this sort, we will begin by creating this simple query to show the "drilled down" States that make up the requested United States total. Next, the authors wish to see a drilled up view of the same data, based upon the application of the ToggleDrillState() function to the query generating the dataset presented in the drilled down view.

We work with the Reporting department representatives to prepare a quick draft of the requirements, to corroborate the business need. The result of our joint efforts is a "drilled down" (the upper box) and a "drilled up" (the lower box) view of the sample data, laid out in a small spreadsheet, shown in Illustration 8.

Illustration 8: "Confirmation Draft" of the Proposed Dataset Objectives

This represents a simple need that we can readily answer using the .Children and ToggleDrillState() functions in conjunction: We will apply .Children to the specified data to bring about, in effect, the drilldown of the desired Geography level (exposing the member States of the United States), and then apply the ToggleDrillState() function to return the same data to its "rolled up" state, as we see in the bottom portion of the image above. We are confident that, by thus creating a "drilled down" scenario as an initial step, we can more effectively demonstrate the simplest workings of ToggleDrillState() within a meaningful context.

Preparation: Create a Drilled Down State from Which to Toggle

Let's construct a simple query to provide the "starting point" for our subsequent work with the ToggleDrillState() function. Our intent here, again, is simply to return the requested Calendar Year Reseller Sales information for each of the United States in which we experienced activity, presenting Adventure Works' summarized total sales for the United States, together with the total sales values for each of the individually broken out States that comprise the United States totals.

1.  Type (or cut and paste) the following query into the Query pane:

 MDX0430011 Simple "Drilled Down" Scenario
  CROSSJOIN({[Date].[Calendar Year].Members},{[Measures].
    [Reseller Sales Amount]}) ON AXIS(0),
     {[Geography].[Geography].[Country].[United States],
       [Geography].[Geography].[Country].[United States].CHILDREN} 
    ON AXIS(1)      
 [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 9.

Illustration 9: Our Initial Query in the Query Pane ...

The above query sets the stage for a drillup action (or, in this case, the "reversal of a drilldown state"), while also (as we have noted to be the case in sister articles where we discuss drilling functions) presenting us with opportunities for extending the drilling capability that it imparts even further within reporting, and other, consumerfacing applications.

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 10.

Illustration 10: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 11 appears.

Illustration 11: Results Dataset – Initial "Drilled Down" Scenario

In the returned dataset, we see that the Country level member of the Geography dimension, United States, is presented in "summarized" state. The rows underneath United States are composed of its children, the various States within which Reseller Sales have occurred for the Adventure Works organization in each of the available Calendar Years.

3.  Select File > Save MDXQuery1.mdx As ..., name the file MDX0430011, and place it in a meaningful location.

4.  Leave the query open for the next step.

Our developer / author colleagues express satisfaction with the contextual backdrop we have established for introducing the ToggleDrillState() function. We note that we have established a "drilled down" scenario without using an MDX drilling function, which we are certainly free to do in conjunction with toggling the drilled state, as long as we obey the syntax rules we have already discussed. We will undertake using the function in our next steps, in the procedural section that follows, first with the foregoing example, and then within a "fresh" query we will construct.

Procedure: Toggle to a "Drilled Up" State Using the ToggleDrillState() Function

Having arrived at a good conceptual starting point, we are positioned to leverage the capability to toggle from the "drilled down" results dataset we have generated to a "drilled up" status. After obtaining consensus on the dataset, and corroborating the fact that the group with which we are working has gained an understanding of what we have accomplished within the simple dataset we have initially generated, we will next set out to show the "reversal" of the "drilled down" state to a "drilled up" state. We can thus exploit the same data structures, and the query that generated them, to establish a frame of reference. We will simply enclose the existing syntax within the rowsaxis specification, which defines the "drilled down" nature of the results dataset, within the ToggleDrillState() function to gain an appreciation for how the function does its work.

5.  Replace the comment line in query MDX0430011 with the following:

 MDX0430012 Using ToggleDrillState()
  to Reverse Simple "Drilled Down" Scenario

6.  Select File > Save MDX0430011.mdx As ..., name the file MDX0430012.mdx, and place it in the same location as its predecessor, to protect the former query.

7.  Place the cursor to the right of the comma (",") on the following line of the query:

[Reseller Sales Amount]}) ON AXIS(0),

8.  Press the Enter key twice to create a new line between the line of the query on which you have placed the cursor and the line that currently follows it, namely:

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

9.  Type the following syntax into the new row:


10.  Place the cursor to the right of the right curly brace ( "}" ) on the following line of the query:

[Geography].[Geography].[Country].[United States].CHILDREN}

11.  Add a comma (",") to the right of the right curly brace ( "}" ).

12.  Press the Enter key twice, as before, to create a new line between the line of the query which the cursor currently occupies, and the line that currently follows it, namely:


13.  Type the following syntax into the new row:

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

Here, we are adding the secondary set specification, within the ToggleDrillState() function that we began to add earlier. The secondary set follows the primary set (the set specifications are separated by a comma). Moreover, with the syntax above, we complete the function "wrapping" by adding the right parenthesis ( ")" ) symbol to the right of the secondary set.

The Query pane appears, with our input, as depicted in Illustration 12.

Illustration 12: "Adjusted" Query in the Query Pane (Modifications Circled) ...

14.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 13 appears.

Illustration 13: Results Dataset – ToggleDrillState() at Work ...

And so we see that ToggleDrillState() has the expected effect: It "reverses" the "drilled down" Geography dimension, Country level, with the end result being to roll the various States into the United States summary line in the results dataset. (We would obtain the same results by substituting the DRILLUPMEMBER keyword, thereby substituting the DrillUpMember() function in the place of the ToggleDrillState() function we employed above).

Opportunities to parameterize (within Reporting Services or another end user application) either all or part of the secondary set expression portion of the rowsaxis specification are probably obvious to many of us. Another option might be exploited in hiding or displaying (via, for example, a combination of conditional logic and parameterization) the TOGGLEDRILLSTATE keyword, simulating "on" or "off" behavior. Other opportunities for parameterization exist, as well, surrounding the primary set specification, or within the use of the RECURSIVE keyword (which we omitted above, relying upon the default behavior of ToggleDrillState(), but will examine it in the next example).

We can therefore parameterize the drill up or drill down action flexibly and easily, depending upon the degree of integration with Analysis Services that is available within our end application (Reporting Services is highly integrated, of course, as part of the overall Microsoft enterprise BI solution). While we will not further extend our examination of the MDX ToggleDrillState() function to its physical parameterization in this way, or to the further approaches to physical parameterization in general, within this article, I provide handson guidance in these subjects within my MSSQL Server Reporting Services series, as well as in other of my series' at Database Journal.

15.  Select File > Save MDX0430012.mdx to ensure that that the file is saved.

The client developers and report authors express satisfaction with the results, and confirm their understanding in the operation of the ToggleDrillState() function. They present a similar request at this point for assistance in crafting another scenario, where they can employ ToggleDrillState() in combination with another MDX function to obtain results to meet a slightly more elaborate requirement. This example will further activate what we have discussed and seen thus far, employing ToggleDrillState() in a straightforward scenario, somewhat like our first example, initially, while leveraging the optional RECURSIVE keyword. This will give us a handson, "before and after" look at how ToggleDrillState () behaves with and without RECURSIVE specified.

The report authors outline the next scenario as follows: Using the Adventure Works cube as a data source, they wish to begin, as before, with a "drilled down" scenario (albeit, again, through a function that lies outside the specific "drilling" function family). We will start with an existing query that returns information surrounding the composition of internetgenerated sales from the perspective of select customer locations. Specifically, the current query generates a dataset that presents comparative Internet Sales for all Calendar Years contained in the cube, for customers in three States in the Southeast United States (for which sales are being analyzed), Alabama, Florida, and Georgia. The current query produces a dataset that summarizes Internet Sales, where they exist, at all hierarchical levels for the three States under examination. The report authors remind us, as they have told us in past sessions, that this specific dataset was requested by the information consumers because they want to monitor the gradually growing Adventure Works sales via the Internet in the Southeast, an area within which increased marketing efforts have been applied.

The "drilldown" action within the current query results, therefore, in a dataset that presents the total sales for the extreme Southeast United States, drilled down to the sales totals for the States, which make up the total. In addition, the States are drilled down to the Cities whose sales compose each State's total, with the Cities drilling down, in like fashion, to the constituent Postal Codes involved. Finally, the names of the Customers that comprise the sales for each Postal Code summary are also exposed, as the bottom level of the drilldown.

The report authors / developers have a new appreciation for the fact that, given the correctly constructed core query, the capability to perform ad hoc drilldowns or drillups on the given summaries at runtime will become a matter of parameterizing a key component of the rowsaxis specification (or other components) within the MDX query involved. They understand that one of the results will be a mechanism that can be leveraged, as one example, in a "summary" / "detail" report pairing (as well as through other avenues) within Reporting Services, such as we have discussed earlier. Their objective is to be able to ultimately provide a report that the information consumers can use to perform ad hoc queries at any level, once we provide a working example of how to accomplish "drillups" from the allinclusive hierarchical presentation that they currently have. Moreover, this will give us a handson, "before and after" look at how ToggleDrillState() behaves with and without specifically directed recursion, via the RECURSIVE keyword.

To outline the requirement further, our "confirmation of understanding draft" of the initial, fully drilled down dataset appears in MS Excel as depicted in Illustration 14.

Illustration 14: "Confirmation Draft" of the Fully "Drilled Down" Dataset

We propose that we select the State of Georgia as a "focus drill," for purposes of our demonstrating the use of ToggleDrillState() to render fully "drilled down" results using directed recursion, and then to show its use with recursion left at default behavior (through the omission of the RECURSIVE keyword). Our intent in this example is to accomplish more than a simple "reversal" of an existing drill state with the ToggleDrillState() function. We intend to use the primary set specification within the function to provide a "focused drilldown" from the group of States to a single State (Georgia), which we might then subject to parameterization (potentially allowing single or multiple – even "All" – States as selections).

The two "focus drilled" views we intend to produce as examples appear as shown in Illustration 15.

Illustration 15: "Confirmation Draft" of the Two Proposed "Focus Drilled" Views Examples

We obtain consensus on the proposed target datasets, and set about constructing the query.

16.  Select File > New from the main menu.

17.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 16.

Illustration 16: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

18.  Type (or cut and paste) the following query into the Query pane:

--- MDX043-002-1 Initial Query with Full "Drilldown"
  CROSSJOIN({[Date].[Calendar Year].Members},
     {[Measures].[Internet Sales Amount]}) ON AXIS(0),
     {[Customer].[Customer Geography].[Country].[United States],
          {[Customer].[Customer Geography].[State-Province].[Alabama], 
      [Customer].[Customer Geography].[State- Province].[Florida],
    [Customer].[Customer Geography].[State-Province].[Georgia]}, 
       [Customer].[Customer Geography].[City], SELF_BEFORE_AFTER)})
     ON AXIS (1)
  [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 17.

Illustration 17: Our Initial Query in the Query Pane ...

The above query again sets the stage for a "reversal" of a drilled state, while also presenting us with opportunities to see the combination of MDX level drilling functions and the Descendants() function. Descendants() can be leveraged to achieve many commonly desirable presentation effects and, along with other MDX functions, is very useful to us in drilling up and down. In the present case, Descendants() will afford us a results dataset that accomplishes the same effect as a drilldown function.

19.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset depicted in Illustration 18 appears.

Illustration 18: Results Dataset – Initial "Drilled Down" View – Using DESCENDANTS()

In the returned dataset, we see that, for the selected Southeast States, total Internet Sales appears drilled down to, and summarized for, the States, the Cities, the Postal Codes, and the individual Customers whose purchases made up the Internet Sales totals for each of the "rolled up" levels. At this stage, we have a completely "exploded" view, to which we can apply the ToggleDrillState() function.

20.  Select File > Save MDXQuery2.mdx As ..., name the file MDX0430021.mdx, and place it in the same location used to store the earlier queries.

21.  Leave the query open for the next step.

Our developer colleagues agree that the drilleddown scenario we have established for further examining the ToggleDrillState() function should be effective as a starting point in delivering the two "focus drills" we have discussed and sketched together. We will undertake using ToggleDrillState() again, in the steps that follow. First, we will put the function to work while specifying the optional RECURSIVE keyword, and then we will work through another example where we comment out the keyword, and thus direct the function to revert to default recursion behavior, so as to contrast the difference in the results datasets we obtain.

22.  Replace the comment line in query MDX0430021 with the following:

 MDX0430022 "Focus Drill Down" Example  GEORGIA with Recursion

23.  Select File > Save MDX0430021.mdx As..., name the file MDX0430022.mdx, and save it with the other queries we have constructed.

24.  Click to the immediate right of NON EMPTY( on the fifth row from the top in the existing query to place the cursor there.

25.  Press the Enter key on the PC twice, to create a space between the row and the row underneath it.

26.  Type the following syntax into the new row:


27.  Ensure the cursor is to the right of the syntax typed in immediately above that is, to the right of the following:


28.  Press the Enter key twice, as before, to create a new, blank row between the line of the query which the cursor currently occupies, and the line that currently follows it, namely:

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

29.  Type the following syntax into the new row:

          {[Customer].[Customer Geography].[StateProvince].[Georgia]},

30.  On what is now the thirteenth row, place the cursor to the immediate right of the following:

[Customer].[Customer Geography].[City], SELF_BEFORE_AFTER)})

31.  Remove the rightmost right parenthesis ( ")" ) from the existing line.

32.  Press the Enter key twice to create a new line between the line of the query which the cursor currently occupies, and the line that currently follows it, namely:


33.  Type the following syntax into the new row:

          , RECURSIVE

34.  Press the Enter key twice, once again, to position the cursor on a new, blank row, between the newly added syntax and the line immediately underneath it, namely:

 ON AXIS(1) 

35.  Type the following syntax into the new row:


This encloses the rowsaxis specification of our query within the ToggleDrillState() function. The Query pane appears, with our input, as shown in Illustration 19.

Illustration 19: Our Modified Query in the Query Pane (Alterations Circled) ...

The MDX query follows, with modifications, in its entirety, and can be cut and pasted, if desired.

-- MDX043-002-2 "Focus Drill Down" Example - GEORGIA with Recursion
   CROSSJOIN({[Date].[Calendar Year].Members},
      {[Measures].[Internet Sales Amount]}) ON AXIS(0),
       {[Customer].[Customer Geography].[State-Province].[Georgia]},
         {[Customer].[Customer Geography].[Country].[United States],
               {[Customer].[Customer Geography].[State-Province].[Alabama], 
               [Customer].[Customer Geography].[State- Province].[Florida],
          [Customer].[Customer Geography].[State-Province].[Georgia]}, 
            [Customer].[Customer Geography].[City], SELF_BEFORE_AFTER)}
          , RECURSIVE
    ON AXIS(1)
   [Adventure Works]

36.  Execute the query by clicking the Execute button in the toolbar, as before.

Analysis Services populates the Results pane, as before, and the dataset depicted in Illustration 20 appears.

Illustration 20: Results Dataset – ToggleDrillState() Function with Optional RECURSIVE Keyword

We can see that the that effect of the ToggleDrillState() function has been, effectively, to maintain the preexisting drill state, drilling with recursion down to the lowest level members in the Customer Geography dimensional hierarchy. What has changed is that the full dataset has been restricted (or drilled down to, from the top summary in the dataset) to the state of Georgia, thanks to the insertion of the primary set specification of {[Customer].[Customer Geography].[StateProvince].[Georgia]}. It is easy to see how parameterizing, as an example, the State name in the primary set might offer a basis for ad hoc selection capability at runtime within, say, a report we craft in Reporting Services.

37.  Select File > Save MDX0430022.mdx to save the query in its specified location.

38.  Leave the query open for the next step.

We will perform a final exercise, this time "disabling" the optional RECURSIVE keyword, to confirm our understanding of how recursion works in "default mode" within ToggleDrillState().

39.  Replace the comment line in query MDX042003 with the following:

 MDX0430023 "Focus Drill Down" Example GEORGIA without Recursion

40.  Select File > Save MDX0430022.mdx As..., name the file MDX0430023.mdx, again with the queries created earlier.

41.  Click to the immediate left of the following:

            , RECURSIVE

42.  Type two hyphen ( " " ) characters to the left of the RECURSIVE keyword, to "comment out" the line of the query.

The Query pane appears, with our changes, as shown in Illustration 21.

Illustration 21: The Modified Query in the Query Pane (Modification Circled) ...

We have disabled the optional RECURSIVE keyword, as a shortcut to seeing the operation of the ToggleDrillState() function with default recursion behavior. This will allow us to readily contrast the results "with" (as in the last results dataset we obtained) and "without" (the dataset we obtain once we execute the query with RECURSIVE disabled), in the following steps.

43.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is again populated, as the dataset depicted in Illustration 22 appears.

Illustration 22: Results Dataset – ToggleDrillState() Function without Optional RECURSIVE Keyword

We now see the effects of the physically disabled (and, therefore, an absent) RECURSIVE keyword: the ToggleDrillState() function, combined with the fully "drilled down" scenario delivered by the Descendants() function, returns a "drilled up" dataset, whereby the selected State level member, Georgia, is "drilled" to the next lower level, the available Cities. Recursion is therefore eliminated and the "drilldown" action is limited to a single level, the default in the absence of the RECURSIVE keyword.

One immediate use for this behavior, as we have already alluded, is that the RECURSIVE keyword (or, alternatively, characters "disabling" the keyword) can be parameterized in a flexible end user application like Reporting Services. It becomes apparent, once again, that we can achieve many desirable presentation effects through the use of another MDX "drilling" function, in combination with other MDX functions. As we have noted several times, various components of the functions involved offer opportunities for parameterization within a reporting (or other) application, for farreaching capabilities in the manipulation of the supporting dataset of a target report containing the functions under consideration.

44.  Select File > Save MDX0430023.mdx to ensure that that the file is saved.

45.  Select File > Exit to leave the SQL Server Management Studio, when ready.

The client representatives inform us that their immediate indoctrination goals have been met. We agree to return at a later time to demonstrate approaches to implementing the ToggleDrillState() function, in conjunction with other MDX functions, within Reporting Services, where, as we have noted, the functions lend themselves to easy and flexible parameterization. The group repeats that it is anxious to leverage MDX in this new way to support interactive drillup and drilldown by organizational information consumers.

Summary ...

In this article, we continued our extended examination of the MDX surrounding drilling up and down within our Analysis Services cubes, focusing this time upon the ToggleDrillState() function. We noted throughout our session that, like many MDX functions, ToggleDrillState() can be leveraged within and among the various "layers" of the Microsoft integrated Business Intelligence solution to support sophisticated presentations and features. We introduced the function, touching upon examples of effects that we can employ it to deliver to help information consumers maneuver between detailed and summarized levels of data.

In introducing ToggleDrillState(), we commented upon its operation and discussed the "reversed" drill states within results datasets that we can deliver using specified primary and secondary Set Expressions, together with an optional RECURSIVE keyword, within the function. Next, we examined the syntax involved with ToggleDrillState(), and then undertook a couple of illustrative practice examples within which we met hypothetical business requirements with the function. In each case, we generated initial queries that capitalized on the features of ToggleDrillState() within the context of "drilled down" scenarios, in order to witness the function in action.

We undertook examples where we combined ToggleDrillState() with the MDX .Children and Descendants() functions, to demonstrate how "drilled down" states generated by these functions and others might serve as "starting points" for queries to which we might apply "reversal" logic to meet subsequently determined, or variable, needs for summarization at different levels. Moreover, we discussed points within our query where we might consider the insertion of parameterization, within a reporting application (such as Reporting Services) or other consumer applications, to leverage the ToggleDrillState() function to support "drill reversal" in an ad hoc manner, either with or without recursion – and especially in conjunction with a preexisting drill state. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved