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

Comments

• Anonymous
  August 14, 2008
  How would you "write the dataset query accordingly in your drillthrough target report" Thanks

• Anonymous
  September 03, 2008
  >Dawn said:   >How would you "write the dataset query accordingly in your drillthrough target report" Just include in your query in the where part something like that : SELECT ... FROM Table WHERE (@Quarter IS NULL) /* NULL value means ALL the quarter / OR Table.Quarter = @Quarter / Standard Quarter restriction */

• Anonymous
  September 05, 2008
  Hi. Thanks for this blog. I have nearly got this working on one of our matrix reports apart from one thing. I'd be grateful if you could help. My matrix looks something like this:                 Error1  Error2 Error3  Totals: Provider1 Prog1     3       0      4       7          Prog2     0       0      1       1          Prog3     2       2      0       4          Totals:   5       2      5      12 Provider2 etc. The matrix and detail reports share the same stored procedure, and both have parameters for provider and contract month. I have added parameters and put filters on the detail report for programme and errortype to restrict the results based on the selected row and column in the matrix. As specified above, I have told these 2 new parameters to allow NULL values. My matrix report links the parameters as follows: "Provider=Fields!Provider.Value" "ContractMonth=Fields!ContractMonth.Value" (the provider and contract month must be specified) "Programme=IIF(InScope("Grp_Programme"),Fields!Programme.Value,Nothing)" "ErrorType=IIF(InScope("Grp_ErrorType"),Fields!ErrorType.Value,Nothing)" The filters on the detail report specify "Fields!Programme.Value=IIF(Parameters!Programme.Value=Nothing,Fields!Programme.Value,Parameters!Programme.Value)" and similarly for ErrorType. I can drill-through to the detail table report from the matrix detail cells and the row subtotal (5,2,5 above) cells. However, if I try to drill-through on any of the column subtotal cells (7,1,4 above), the detail report returns the entire data set, which I would only expect after clicking the bottom-right corner cell (12 above) I have put textboxes onto the detail report to see what is being passed from the matrix report. "IIF(Parameters!Programme.Value=Nothing,"True","False")" always returns True, so the problem seems to be getting the parameter out of the matrix report rather than anything on the detail report. I expect that the problem is due to the Programme group being a second-level group but can't think why. If you have any suggestions, I would be grateful. Thanks

• Anonymous
  November 20, 2008
  How would this example look if you had both quarters and months?

• Anonymous
  February 05, 2009
  Last week we found the solution to the problem I mentioned above. It was a known fault which was fixed in SP2 cumulative pack 5. We have just updated the server to the new SP3, and the report now works as we want it to. See http://support.microsoft.com/kb/945910/ for details