Reporting Services drill-through for a matrix report

One of the great things about working in Microsoft is the huge e-mail threads with where we share problems and resolve them, as you get to learn loads of tips and tricks. One of the nightmares of working at Microsoft is the huge e-mail threads with where we share problems and resolve them, as you have to wade though loads of stuff to find the good stuff.

So this is one little problem  I came across from Mike McGeehan a Technology Solutions Specialist in New York, which was then fixed by Robert Bruckner in the SQL Server product team.

Top Tip. Mike was able to show us the problem he was having by using adventure works and then enclosing the report files based on that so we can all reproduce the problem.

Mike was trying to create a drill-through report whereby report A has a matrix on it and when you click on a cell it takes you to report B passing parameters on the way so that the right context comes up in report B.

Report A is a modified version of the Company sales report, to include a subtotal in its matrix format:

The report produces a total column as shown below:

When a user clicks on the detail for “Mountain Frames” and “Q2” (the blue circle), a second report is called and results in the following:

This was done by passing the values from the Company Sales report to the subreport:

However, when the user clicks on the subtotal column in the Company Sales report (the red circle), it seems to arbitrarily select “Q3” when passing parameters to the subreport.

Mike’s question was “Is there a way to pass all quarters to the subreport (wildcard?) in order to get all four quarters in the subreport? ”

Robert fires up the report on his machine does some digging around and comes back with an answer in 2 hours

Currently, your drillthrough actions on the matrix cells pass a single value for Year, Qtr, etc.

First you will need to modify your drillthrough target report so that a NULL value can be passed as Qtr etc.  The meaning of passing a NULL value is that you want the data for all quarters – and you will need to write the dataset query accordingly in your drillthrough target report.

Then, in the main report, modify the drillthrough action by using the InScope function to determine whether you want to pass a specific value (i.e. detail cell) or a NULL (i.e. subtotal cell).

For example (YearGroup is the name of the matrix group that groups by OrderYear, etc.):

• pOrderYear:    =iif(InScope(“YearGroup”), Fields!OrderYear.Value, Nothing)
• pOrderQtr:      =iif(InScope(“QtrGroup”), Fields!OrderQtr.Value, Nothing)

His parting shot is also important -

“BTW, Note that this kind of report layout is simplified in RS 2008 due to tablix – you have separate cell definitions for detail cells and subtotal cells and can then setup the drillthrough actions without the need to use the InScope function.”

I posted this for two reasons:

1. This might be something you are struggling with , but more importantly,

2. If you are stuck don’t struggle on alone, even if you are an ‘expert’ and work for Microsoft you don’t have all the answers, there’s always someone else who might have the answers.

Of course not everyone works in a multi-billion dollar software company with technical resources coming out its ears, and this is why I am really keen on the many forums, and community sites out there such as the UK SQL Server community as they can give you the same kind of access to really good advice.

Technorati Tags: SQL Server2005,SQL Server 2008,Reporting Services,drillthrough