David Bickley

I was tasked with creating a master report to list all of our subreports. After way too much time experimenting and researching I finally figured it out. Below is the quick and dirty, I apologize for not having enough time to do get into the nitty gritty.

First create a multivalue parameter holding the report names, lets call it @Reports.

Next create a dataset that unions all the names together. Example:

SELECT     reportname
FROM         (
SELECT     'report1' AS reportname
UNION ALL
SELECT     'report2' AS reportname
UNION ALL
SELECT     'report3' AS reportname
) AS sq1
WHERE     (reportname IN (@Reports))

Now we are going to add a table with a parent group and a sub group for each sub report.
Add a table to the report and set the dataset to the dataset you just created.
Then add a parent row group and add another parent row group.
Next add an adjacent group for each subreport and set the group filter to reportname=”yoursubreportname”.
Finally on the main parent group set the page breaks to be “between”.

Hopefully that makes sense.

This entry was posted on Friday, January 21st, 2011 at 8:04 pm and is filed under SSRS, Tips. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.