Reporting Services drill-through for a matrix report

re: Reporting Services drill-through for a matrix report

Al — Thu, 05 Feb 2009 18:43:20 GMT

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

re: Reporting Services drill-through for a matrix report

Jim — Thu, 20 Nov 2008 20:17:34 GMT

How would this example look if you had both quarters and months?

re: Reporting Services drill-through for a matrix report

Al — Fri, 05 Sep 2008 16:24:15 GMT

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

re: Reporting Services drill-through for a matrix report

YAK — Wed, 03 Sep 2008 20:09:15 GMT

>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 */

re: Reporting Services drill-through for a matrix report

Dawn — Thu, 14 Aug 2008 23:57:15 GMT

How would you "write the dataset query accordingly in your drillthrough target report"

Thanks