MDX Set Functions: The ToggleDrillState() Function - Page 4

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.

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.