Let's assume, for our practice example, that we have
received a call from the Reporting department of the Adventure Works organization,
requesting our assistance in meeting a specific report presentation need that,
while involving a given drilldown scenario in the immediate term, will manifest
itself in numerous similar scenarios that the report authors are aware that
they will face in the long term. The authors want to perform drilldowns using
MDX functions as the mechanism. Without getting too heavily into the details
of the approach for this at the reporting application level, we can suffice it
to say that this could be easily accommodated within the realm of the Analysis
Services and Reporting Services components of the Microsoft integrated
Business Intelligence solution. We would simply create a targeted "drilldown"
report that would be triggered from a primary, summary level report, and then "connect"
the two in a manner similar to that which we followed in a drillthrough
scenario in a recent article in my MSSQL Server Reporting
Services series, Mastering OLAP
Reporting: Drilling Through Using MDX.

What lies at the heart of the
request, we note in our early discussions, is the need to perform drilldown on
a group of members. In the case at hand, the authors tell us that they need to
provide a query that enables drilldown for the Calendar Year 2003Reseller
Sales of a specific group of the bicycle models that Adventure Works
builds. Out of three Product Model Lines within the Product
dimension of the Adventure Works cube, the immediate need is to return a
dataset that presents two of the lines, Mountain and Road models,
in summary, while drilling down the Touring model to the level of its (non-empty)
children (the various types of bicycle components for the Touring model)
and presenting the Reseller Sales Amounts at that level.

We work with the Reporting
department representatives to prepare a quick draft of the requirement, to
corroborate the business need. The result of our joint efforts is the sample
data, laid out in a small spreadsheet, shown in Illustration 8.

This represents a simple, yet
practical, need that we can readily answer using the DrillDownMember() function.
We are confident that, once we construct and demonstrate the workings of the
function, we can count on the authors to both grasp the concepts and to extend
their requirements with further questions. This is an excellent starting point
for an evolving examination of possible uses.

Let's construct a simple query,
therefore, to return the requested Calendar Year 2003 Reseller Sales information
for the three selected Product Model Lines, presenting the summarized
sales for each of the Mountain and Road models, together with the
sales values for each of the individually broken out component types that make
up the Touring model.

The Query pane appears, with our input, as depicted in Illustration
9.

Illustration 9: Our Initial
Query in the Query Pane ...

The above query meets the specific requirement beautifully,
but also presents us with opportunities for extending the drilldown
capability that it imparts even further within reporting, and other,
consumer-facing applications. One such opportunity lies within the parameterization
of various components of the axis definitions, together with the slicer. An
obvious example might be to reference a parameter in the last set, {[Product].[Product
Model Lines].[Product Line].[Touring]}, of the row axis, through Reporting
Services. The parameter might then be passed from a "launch"
point in a summary report to a target report containing a query similar to the
above within its dataset, generating a drilldown report upon, say,
the click of a summary item within the former by an information consumer. (This
is but one approach; see my MSSQL
Server Reporting Services series for articles that expose
practical approaches to this and other methods for accomplishing similar
effects.)

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.

In the returned dataset, we see that Mountain and Road
(the top two rows of the dataset) are presented in "rolled up" state,
as is Touring, the third row. However, the rows underneath Touring
are composed of its children, the various components of the Touring
Model Line. (Moreover, we can verify easily that the values of the child
rows in the dataset add to the rolled up Touring total of 616, 268.06.)

3.
Select File
-> Save MDXQuery1.mdx As ..., name the file MDX037-001, and
place it in a meaningful location.