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
SELECT
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)
FROM
[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:
TOGGLEDRILLSTATE(
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:
ON AXIS(1)
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"
SELECT
CROSSJOIN({[Date].[Calendar Year].Members},
{[Measures].[Internet Sales Amount]}) ON AXIS(0),
NON EMPTY(
{[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)})
ON AXIS (1)
FROM
[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.
» 