So for my first technical blog post I decided that I would pull something from the Twitterverse and come up with a solution. So I had a quick exchange with my good friend and fellow MVP Jessica Moss ( blog | twitter ) about a problem that Brian Tromburg ( twitter ) was trying to solve.
He described the problem like this “Is it possible to group diff. tables by common ID on same page and page break after each group?” and “Yes. If Table 1 and Table 2 share Group 1 and Group 2. I want T1G1 & T2G1 on Pg 1, then T1G2 and T2G2 on Pg2, etc” So simple enough, I recommended using a list and Jessica correctly reminded me that a list cannot target more that one dataset. That just seems wrong so I submitted a Connect Item suggestion that it should be allowed. If you think it should too, feel free to go here ( Connect Item ) and vote. But, I figured we could come up with a workaround.
Luckily, there does exist a solution using Sub Reports. A little less elegant than my suggestion above, but it works. So let’s walk through setting this up.
Here is my setup of reports and what they contain:
- Report #1 – Driver
- Dataset returning a list of Products and the associated Category and Sub Category
- List Control tied to the list of Products Dataset
- List control contains three sub reports pointing to one of the reports below.
- Report #2 – Product Summary
- Dataset returning total sales by Product per year
- Table returning the Product Sales filtered by a Product ID Parameter
- Report #3 – Category Summary
- Dataset returning total sales by Category per year
- Table returning the Product Category Sales filtered by a Product Category ID
- Report #4 – Sub Category Summary
- Same as Report #3 except at the Sub Category level
I have a link below to the full project. The data source is tied to Adventure Works 2008 OLTP database so you can run all the queries yourself. So let’s look at some screen shots.
Here is the design surface of the Driver report showing the List Control along with the three sub reports:
Now we can see the setup of one of the sub reports. They are essentially all the same, but I wanted to show the flexibility that you could really do anything you need to here.
Here is the design of the Sub Category Summary Report
And here is the screen shot showing the filtering of the data set by the Product Sub Category ID. I could have put the filtering directly in the query as well, but left it here for demo purposes.
So now let’s look at the final result:
So as you can see we have our product information at the top for each product and the tables from the Sub Reports showing appropriate sales summary data for the Product, Product Category, and Product Sub Category. If you page through each page is for a different product and the associated Sales Summary tables.
So some pretty neat stuff I think on how you can use Sub Reports to provide some advanced SSRS functionality which should be able to be used to solve Brian’s problem. There might be some other ways to solve it, but this was the first one that I came up with.