Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 7, 2005

MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function - Page 4

By William Pearson

Procedure: Satisfy Business Requirements with MDX

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 2003 Reseller 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.

Illustration 8: "Confirmation Draft" of the Proposed Dataset Objective

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.

1.  Type (or cut and paste) the following query into the Query pane:

-- MDX037-001 Initial DrillDown With Selected Members
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,
   [Product].[Product Model Lines].[Product Line].[Mountain], 
        [Product].[Product Model Lines].[Product Line].[Road], 
            [Product].[Product Model Lines].[Product Line].[Touring]}, 
        {[Product].[Product Model Lines].[Product Line].[Touring]}))ON ROWS
    [Adventure Works]
              ([Date].[Calendar Year].[CY 2003])

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.

Illustration 11: Results Dataset - Initial Use of DrillDownMember() Function

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM