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

May 1, 2006

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.

 MDX0430022 "Focus Drill Down" Example  GEORGIA with Recursion

 TOGGLEDRILLSTATE(

TOGGLEDRILLSTATE(

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

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

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

ON AXIS(1) 

          , RECURSIVE

 ON AXIS(1) 

          ))

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

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
SELECT
   CROSSJOIN({[Date].[Calendar Year].Members},
      {[Measures].[Internet Sales Amount]}) ON AXIS(0),
    NON EMPTY(
    TOGGLEDRILLSTATE(
       {[Customer].[Customer Geography].[State-Province].[Georgia]},
         {[Customer].[Customer Geography].[Country].[United States],
            DESCENDANTS(
               {[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)
FROM 
   [Adventure Works]

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

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.

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

 MDX0430023 "Focus Drill Down" Example GEORGIA without Recursion

            , RECURSIVE

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

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.

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

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.

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.