Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions - Page 4

Procedure: Satisfy Business Requirements with MDX

Let's assume, for our practice example, that we have received another call from members of the Reporting department of a client, the AdventureWorks organization, requesting our assistance in meeting a report presentation need. As has been the case in numerous requests for help in the past, the report authors realize that, while they are seeking assistance with a specific scenario in the immediate term, the need will manifest itself in numerous similar scenarios that they expect to face in the long term. As always, they seek working examples, which they intend to extrapolate to recurring business needs they encounter within the enterprise.

The authors want to perform drilldowns from a level perspective, much as they did in our earlier session in Set Functions: The DrillDownLevel() Function. However, they inform us that they now wish to extend the basic level drilldowns we performed there to include the capability to present "top" and "bottom" results based upon level drilldowns, using the related MDX functions as the mechanism, in a manner similar to the way they performed top" and "bottom" drilldowns on members with the DrillDownMemberTop() and DrillDownMemberBottom() functions. (See Set Functions: The DrillDownMemberTop() and DrillDownMemberBottom() Functions within my Database Journal MDX Essentials series for our activities with these functions).

As we have noted in similar scenarios within recent articles of our series, and without getting too heavily into the details of approaches for this that we might undertake at the reporting application level, it is safe to say that this can 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 "drilldowntop" or "drilldownbottom" report (or perhaps even a single drilldown report with a parameterized "top" or "bottom" modifier) that would be triggered from a primary, summary level report, and then "connect" the primary and secondary report(s) 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 with the client representatives, is the need to perform drilldown upon members of a given hierarchical level to members to a lower level (most often the next lower level, but possibly to another level that we wish to be able to specify). In the example case at hand, the authors tell us that they need to provide a query that enables drilldown for the Calendar Year 2003 Internet Order Quantity and Internet Sales Amount for three Adventure Works Product Categories. While, as we have said, the ultimate objective might be to make the "top" or "bottom" number returned within our report datasets variable, we decide to begin with examples of the routine usage of the DrillDownLevelTop() and DrillDownLevelBottom() functions.

The report authors tell us that they first wish to drill down, from the perspective of Internet Order Quantity and Internet Sales Amount, upon the Bikes, Clothing and Accessories Product Categories, returning the top two (2) seller Subcategories for CY2003. They wish to rank the Subcategories, and thus derive the "top" Subcategories, based upon Internet Sales Amount.

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.

We suggest beginning with the generic DrillDownLevel() function, and then morphing it into its DrilldownLevelTop() derivative, so as to build upon our efforts with DrilldownLevel() in an earlier session. (This may also prove beneficial for readers who are joining us with this article, and have not had an opportunity to explore our work with the more general function in Set Functions: The DrillDownLevel() Function.) Based upon this reasoning, therefore, we propose to begin by returning the three Product Categories in summary, displaying beneath each summary the drilled down children of the respective Category (themselves Product Subcategories) together with the Internet Order Quantity and Internet Sales Amount values related to each Subcategory for CY2003. Once this is accomplished, we will have a core query upon which to base our subsequent efforts with the DrillDownLevelTop() and DrillDownLevelBottom() functions.

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Internet Order Quantity and Internet Sales Amount information for the three selected Product Categories, presenting the summarized quantity and sales values for Bikes, Clothing, and Accessories, together with the corresponding values for each of the individually broken out component Subcategories that make up each of the Bikes, Clothing, and Accessories categories.

-- MDX041-001 Initial DrillDown With Selected Level
SELECT
   {[Measures].[Internet Order Quantity], [Measures].[Internet Sales Amount]} 
         ON AXIS(0),
DRILLDOWNLEVEL( 
 {[Product].[Product Categories].[Category].[Bikes], 
 
 [Product].[Product Categories].[Category].[Clothing],
  
 [Product].[Product Categories].[Category].[Accessories]})ON AXIS(1)
  
FROM 
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].[CY 2003])

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

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

In the returned dataset, we see that Bikes, Clothing, and Accessories are presented in "rolled up" state. Moreover, the rows underneath each of Bikes, Clothing, and Accessories are composed of the respective Category's children, the various Subcategory level members. (And we can verify easily that the values of the child rows in the dataset add to the rolled up totals.)