January 6, 2010 at 2:50 pm
Hi all,
I've been loving writing SQL queries for a few yeas, but am now trying a bit of SQL Reporting Services and loving it! My question relates to Matrix's (I'm using SQL RS 2005). If you create a pivot table in MS Excel, then you can double click on the pivot data to drill down to the actual data being pivoted. So lets say my pivot table is summing sales by product category and then by day. By double clicking on the total sales for a particular day / product category, Excel will reveal all the individual orders that make up that days sales total.
I would like to reproduce this sort of functionality within a Matrix report, but I am struggling with finding what to research (I'm not looking for step by step instructions but only a point in the right direction!).
I thought I could write a sub-report to display the individual orders making up the days sales. I would then click on a cell in the matrix which would pass the corresponding day and product category to the subreport as parameters which would then show the orders. But I don't think this is possible after doing a bit of reading.
Then I thought of writing the dataset query to calculate the totals, but then the order detail goes out of the window
I'm really stuck as to what to look at next! Does anybody have any advice?
Many thanks
Andy
January 7, 2010 at 5:12 am
Ok what I did was to add in a table below to the same report (no subreport needed). Then I added a parameter which is used to identify the product category being summed. If this parameter is blank then the detail table is conditionally hidden, and if it is populated then the order detail is visible, and filtered to show only what is put into the parameter. Not the best solution but it is a workaround
January 8, 2010 at 7:33 am
With SSRS 2005, I have a matrix report that shows months going down the left side (y axis) and years across the top (x axis). If you click on the data cell, it will bring you to a drill through report to show the sales for that particular month and year.
On the drill through report I created two hidden parameters, one for month and one for year. When you navigate to the drill through, it passes the month and year to those parameters and then the dataset for the drill through uses them.
It works slick, but please let me know how I can further clarify.
January 8, 2010 at 7:38 am
What I've been using for drill down in situation where I can't build it into the report is to use the Action->Go to Report to go to another report. I set it up on the cell that holds the value you want to drill down to and pass any parameters that are needed. If you want to get real fancy, you can check this link out.
http://qa.sqlservercentral.com/Forums/Topic690143-147-1.aspx
It explains how to make the report open in another window. I haven't tried this one yet but it's on my list of things to try.
Steve
January 10, 2010 at 1:15 pm
Thanks for the reply both - given me some stuff to go on!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply