dcsimg

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.

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:

 TOGGLEDRILLSTATE(

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

TOGGLEDRILLSTATE(

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:

ON AXIS(1) 

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
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]

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers