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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 1, 2006

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

By William Pearson

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.

MS SQL Archives

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