MDX Set Functions: DrillDownLevel() - Page 4

Procedure: Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request, once again, from the Reporting department of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance such as this. We typically provide on-site augmentation for business requirements gathering and training, performing workshops in many cases that illustrate approaches to meeting specific needs. These combined development workshops / "train the trainer" events have worked well in the past for all concerned.

As usual, the authors and developers in the group are aware that the need will manifest itself in recurring similar situations as they work to meet the needs of the Adventure Works information consumers. This particular request for assistance involves a drilldown scenario. We have supported the group recently in performing drilldown (see Set Functions: The DRILLDOWNMEMBER() Function and Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), which, we believe, will be of benefit as we return to assist them with the level drilldown capabilities in which they are now interested.

The Reporting department representatives explain that they want to perform drilldowns, but in the present case, they wish to base the drilldowns upon levels, instead of members, within their cube structure. They wish to use an MDX function as the mechanism, for the same reason that they wanted to perform member drilldowns with MDX. They wish to leverage the Analysis Services and Reporting Services components of the Microsoft integrated Business Intelligence solution, whereby they can create a targeted "drill down" report that will be triggered from a primary, higher level ("Home") report, and then "connect" the two in a manner similar to that which we have followed in other articles. They might also parameterize the dataset(s) within an OLAP report to "swap" DrillDownLevel() and DrillUpLevel() functions (we explore the latter in another article of the series) to achieve drilled down or drilled up perspectives within the report(s) resting upon those datasets, or perhaps to take another approach entirely to deliver drilling capability within their reports. Suffice it to say that, whichever approach the developers take to support a given reporting or analysis need, in a manner similar to that we have examined from the perspective of members, they will likely work with combinations of drilldown and drillup functions with levels to achieve versatility in the end presentations. While the functions differ in syntactical structure and operation, the uses to which we put the functions can be very similar indeed.

What lies at the heart of the request, we note in our early discussions, is the need to perform drilldown on a hierarchical level. In the case at hand, the authors tell us that they need to provide a query that enables drilldown for the Calendar Year 2003 Reseller Sales of the Adventure Works Product Lines. The immediate need is to return a dataset that presents each of the lines with summary Reseller Sales Amounts at that level.

We work with the Reporting team 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 DrillDownLevel() function. This is an excellent starting point for an evolving examination of possible uses for DrillDownLevel(). 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.

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Reseller Sales information for all Product Lines, presenting the summarized total Product sales for the organization, together with the sales values for each of the individually broken out Product Lines that make up the 2003 total.

-- MDX040-001 Initial DrillDown With Selected Level
SELECT
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,
NON EMPTY(DRILLDOWNLEVEL( {
   [Product].[Product Model Lines]}))ON ROWS
 
FROM 
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].[CY 2003])

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

The above query meets the specific requirement quite well, 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 specified Set Expression, {[Product].[Product Model Lines]} 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.)

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 11 appears.

In the returned dataset, we see that All Products (the top row of the dataset) are presented in "rolled up" state. The rows underneath All Products are composed of its children, the various Product Lines that the organization sells (And we can verify easily that the values of the child rows in the dataset add to the rolled up Touring total of 32,202,669.43.)

Our report author colleagues have expressed satisfaction with the example we have presented. They present a similar request at this point for assistance in crafting another drilldown scenario involving levels. This example will further activate what we have discussed and seen thus far, employing DrillDownLevel() in a simple scenario, somewhat like our first example, initially, and then leveraging the optional Level Expression. This will give us a hands-on, "before and after" look at how DrillDownLevel () behaves with and without a specified Level Expression.