Implementing MDX Drillthrough in SSRS

Let’s say for some reason your users want to implement MDX DRILLTHROUGH statement (equivalent to the cube action provided by most OLAP browsers) in SSRS. Here’s a step-by-step way to implement this.

For the purposes of this walkthrough, we are using the standard AdventureWorks Analysis Services database. You can obtain this and the related DW database from https://msftdbprodsamples.codeplex.com/. Do note that you have to manually deploy and process the database after opening the Adventure Works.sln file from its default location of C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project\enterprise

Our objective is to implement two reports:

1. The parent report will list the top 10 products along with their sales amount totals

2. The child (drillthrough) report will display a raw DRILLTHROUGH for the first 500 rows and get back the product quantity of the individual order, along with the order number and date the order was placed

Step 1. Verify the AS database is deployed and processed

Step 2. Here is our (pretty basic) MDX query for the Top 10 listing for current products:

WITH MEMBER PKey AS
[Product].[Product].CurrentMember.Properties("Key0")
select {[Measures].[Reseller Sales Amount], PKey } on 0,
(TopCount([Product].[Product Categories].[Product], 10, [Measures].[Reseller Sales Amount])) on 1
from [Adventure Works]
where [Product].[Status].[Current]

Why the calculated member PKey? You will see later.

Step 3. Capture the DRILLTHROUGH query in Profiler

Since writing the DRILLTHROUGH statement by hand can be a bit of a challenge, here it is captured from SQL Profiler:

DRILLTHROUGH  Select  ([Measures].[Reseller Sales Amount],[Product].[Product].&[358])  on 0 From [Adventure Works] RETURN [Reseller Sales].[Reseller Order Quantity],[$Employee].[Employee],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Reseller Sales Order Details].[Sales Order Number]

You can see the product key is highlighted. This is actually the key column syntax as can be seen from the ampersand prefix. So now you know why we selected the PKey calculated member in the previous query.

Step 4. Create the parent report

The first step is to create a shared data source to the SSAS database:

Next, we actually create the report. We select the New Report option and use the above shared data source. In the Query Designer, we switch to Query Mode and type in the MDX query we developed earlier:

Once this is done, select Tabular report, and move all the columns into the Details section of the report. Title the report as MainReport.

In the generated report, delete the PKey column.

Step 5. Create the drillthrough report

This is the tricky part. Normally, the MDX query designer does not support DRILLTHROUGH SELECT syntax. Hence we need to use a workaround. Please note that this workaround may not be officially supported by Microsoft.

We start the same way, by creating a new report and referencing the shared data source. But when you launch the query designer please switch the query type to DMX instead of MDX.

After this is switched, also switch to query mode as done before. In the space at the bottom, type in the ‘DMX’ query which is actually our MDX DRILLTHROUGH SELECT query. However now we do need to parameterize the query to accommodate the PKey parameter:

DRILLTHROUGH MAXROWS 50 Select  ([Measures].[Reseller Sales Amount], StrToMember('[Product].[Product].&[' + @PKey + ']'))  on 0 From [Adventure Works] RETURN [Reseller Sales].[Reseller Order Quantity],[$Employee].[Employee],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Reseller Sales Order Details].[Sales Order Number]

Before you can click on OK though, you need to setup the parameter as well. You do that by clicking on the Query Parameters button:

We provide a dummy value of 394 so that the fields can be retrieved. The actual value, of course, will be provided by the drillthrough action later.

Select the tabular layout and move all the fields to the Details section. Name the report DrillThroughReport.rdl. Finally, set the report parameter PKey type as Hidden:

Step 6. Create the drillthrough action

Back to MainReport.rdl, right click on the Product text box and select Text Box properties. In the Action tab, you need to set up the Drillthrough (SSRS this time… don’t get confused by the similar term )

We also change the hyperlink look and feel for good measure:

And that does it for our reports:

 

Final Notes

How ‘legitimate’ is it to masquerade a MDX DRILLTHROUGH as a DMX query? As I said before, this is not officially supported. You can refer the to the MS Connect posting at https://connect.microsoft.com/SQLServer/feedback/details/126175/reporting-services-drillthrough-mdx-queries for the ‘won’t fix’ response.

The main reason for my documenting the approach above is that in the specific case I was interested in, the MAXROWS clause is very important. Of course one can argue that using MDX the same can be achieved, but then it is a matter of preference in the end.

BTW, Chris Webb also shares his perspectives on this at https://cwebbbi.wordpress.com/2009/06/16/implementing-analysis-services-drillthrough-in-reporting-services/

If you liked this posting, please rate it! In any case please do take a minute and leave comments, questions etc.