Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 1, 2006

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

By William Pearson

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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM