The ToggleDrillState() Function
Introduction
According to the Books Online, the ToggleDrillState()
function "toggles the drill state of each
member of" a specified secondary set that is also present in a
specified primary set. Moreover, "the ToggleDrillState()
function is a combination of the DrillUpMember() and DrilldownMember()
functions." If indeed such a member of the secondary set is
present in the primary set we specify, and if that member is in a
drilled down state (thereby having a descendant), the DrillUpMember()
function is applied to the primary set. If the member is already
drilled up (thereby presenting no descendant immediately following the member),
DrillDownMember() is, in effect, applied to the primary set.
ToggleDrillState() has
many applications, and pairing it with other MDX functions can help us to leverage
its power even further. I frequently use it in combination with various functions,
including the Descendants() function and numerous others. We will
examine in detail the syntax for the ToggleDrillState() function
after our customary overview in the Discussion section that
follows. Following that, we will conduct practice examples within a couple of
scenarios, constructed to support hypothetical business needs that illustrate
uses for the function. This will afford us an opportunity to explore some of
the delivery options that ToggleDrillState() can offer the
knowledgeable user. Handson practice with ToggleDrillState(), where we
will create queries that leverage the function, will help us to activate what
we learn in the Discussion and Syntax sections.
Discussion
To restate our initial explanation of its operation, the ToggleDrillState()
function "reverses" the drilled state ("drilled up"
or drilled down") of any member that we specify within a secondary set
that also resides in a specified primary set. In short, if the
specified member in the primary set has no descendants (is in a "drilled
up" state), then the function applies the action of the DrillDownMember()
function. If the specified member in the primary set has descendants
(is in a "drilled down" state), then the function applies the action
of the DrillUpMember() function.
In returning a set of member(s) from the primary set whose
drilled state is, in effect, reversed (assuming that the member(s) appear in
the specified secondary set within the function), ToggleDrillState() allows
the primary set to contain "mixed dimensionality." The
members of the secondary set, however, must be of the same dimension.
ToggleDrillState() also makes available an optional RECURSIVE
flag. RECURSIVE is applicable only in scenarios where we are
achieving DrillDownMember() action, as many of us might have concluded
from the fact that MDX makes a RECURSIVE flag available for the DrillDownMember()
function, but not for the DrillUpMember() function. (We might also have
concluded that the concept of recursion is not meaningful in a drillup
scenario, anyway). When we specify RECURSIVE in our ToggleDrillState()
function, the set that results from the initial "pass" through the
members / tuples of the specified primary set is compared to each member
of the specified secondary set, further adding to the returned set based
upon the results of the second "pass." A match is performed with
each successive result set until no more additional members in the newly assembled
set are determined to exist in the secondary set. When we do not add
the RECURSIVE keyword, only a single "pass" is performed
through the primary set, returning the set that results from drilling
down upon each member / tuple in the specified primary set that that is
resident in the specified secondary set – giving the effect of a singlelevel
drilldown for matches, as we shall see in our Practice section.
NOTE: For a more involved discussion of the
workings of RECURSIVE (as well as other considerations) within the DrillDownMember() function, see my article Set
Functions: The DrillDownMember() Function,
a member of the MDX Essentials series
here at Database Journal.
ToggleDrillState() has much in common with other
members of the "drilling" family of MDX functions, within the
myriad opportunities it affords to couple it with other functions, and to
deliver a wide range of analysis and reporting utility. Moreover, as we have
noted to be the case in many scenarios within the Microsoft integrated Business
Intelligence solution (consisting of MSSQL Server, Analysis Services
and Reporting Services), this function, residing within the Analysis
Services layer, can be extended to support capabilities and attributes in
the Reporting Services layer. Knowing "where to put the
intelligence" among the various layers is critical to optimization, in
many cases. For more of my observations on this subject, see MultiLayered
Business Intelligence Solutions ... Require MultiLayered Architects.
Let's look at syntax specifics to further clarify the
operation of ToggleDrillState().
Syntax
Syntactically, anytime we
employ the ToggleDrillState() function to "reverse" the
current drilled state of members or tuples of a specified primary set,
we return drilled up or drilled down members, based upon the existence of those
same members within the specified secondary set. We specify the primary
Set Expression (which can be multidimensional) within the parentheses
to the right of the ToggleDrillState keyword. We then follow the
primary Set Expression with the secondary Set Expression. Finally,
we can add the optional RECURSIVE keyword to the right of the Set
Expressions within the parentheses, as desired. The general syntax is
shown in the following string:
ToggleDrillState(Set_Expression1, Set_Expression2 [ , RECURSIVE] )
When we omit the RECURSIVE
flag, ToggleDrillState() performs a single pass through the primary
set in its match of each member / tuple with the respective member in the secondary
set. Adding the RECURSIVE keyword results in the sequential
matching of the result set being constructed by the function, passbypass as
it is assembled, until no further members with a match in the secondary set
remain.
Let's take a look at an
illustration. The following snippet employs the ToggleDrillState() function:
TOGGLEDRILLSTATE(
{[Geography].[Geography].[United States].CHILDREN},
{[Geography].[Geography].[United States].[Georgia]}
) ON AXIS(1)
This rowsaxis specification,
within a proper query executed against the Adventure Works sample cube
that, say, specified a crossjoin of all Calendar Years with the Reseller
Sales Amount measure within the columns axis, might produce a results
dataset similar to that partially depicted in Illustration 1.
Illustration 1: Results Dataset – Georgia Drilled Down to
Constituent Cities (Partial View)
In the example dataset, we see
that the each of the cities of StateProvince Georgia contained
in the cube appear alongside the summary Reseller Sales Amount for each
for the respective Calendar Years. Our placement of the sets directs the
ToggleDrillState() function to drill down into StateProvince Georgia
([Geography].[Geography].[United States].[Georgia]]),
because member Georgia is present in the secondary set, while it
does not present descendants (and is thus in "drilled up" state) in
the primary set. The other stateprovinces of the United States
are displayed, but in summary ("drilled up") state, because they are
not specified in the secondary set, although the "States of the
United States" are specified within the primary set ([Geography].[Geography].[United
States].CHILDREN).
We will examine the results of a query containing the above
as part of our practice with the ToggleDrillState() function in the
section that follows.
» 
Comment and Contribute
