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 http://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 FilesMicrosoft SQL Server100ToolsSamplesAdventureWorks Analysis Services Projectenterprise

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:

image

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:

image

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

image

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.

image

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:

image

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.

image

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:

image

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 Smile)

image

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

image

And that does it for our reports:

image image

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 http://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 http://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.

.NET Debugging Quick Start

Many a time I am asked how to get a head start with debugging .NET issues – hangs / crashes and most commonly, memory issues. So I assembled this list of resources which helps you get started. In addition, if you are a Premier Support subscriber, you can avail of our most excellent .NET Debugging WorkshopPLUS conducted by my team (Premier Field Engineering).

I hope you find the links useful and if you do I would appreciate some comments and / or you rating this post!

Failover cluster (group) maximum failures limit

My colleague reported that during testing forced failover for a SQL database engine instance, it just ‘failed’ and refused to fail over to the other node in a 2-node cluster. The failure in this case was initiated by shutting down the local service for the clustered instance – which is tantamount to failing the clustered instance itself.

This behavior was slightly unexpected. After some research, we traced it to the ‘Maximum failures in the specified period’ setting at the cluster group (service application) level:

image

It turns out that the testing activity involved some failures already due to which the above limit was already reached. Subsequently, when the SQL service level failure was initiated, the cluster service did not fail it over to the other node and left it in the failed state.

During this investigation I found some articles which are very useful in this:

Moral of the story: When performing failover drills or testing, it may be appropriate to increase this value to a higher number, such as 5 or 10 for the duration of the testing. Subsequently the value may be reset back to 1 or 2.

FYI when you look at the cluster log, this is the message which is recorded when the limit has been reached (and is therefore disallowing restarts):

0000088c.00001038::2012/03/07-06:09:09.834 WARN  [RCM] Not failing over group <groupname>, failoverCount 8, failover threshold 4294967295, nodeAvailCount 1.

Hope this helps!