Generate multiple SSRS reports by grouped field

I have a report that is sent out as a pdf by email every night.
It contains a list of records with errors and goes to every department in the company, with the records grouped by department.
The report is quite large, and the company is expanding, so the report will soon become a pain for the managers, who will have to scroll through pages and pages of irrelevtant data to find their department's section.
I would like to have a separate report go out to a separate email address for each department, however I don't want to have to create a separate report and subscription for every department manually, nor do I want to have to keep this system manually updated in the future as the business changes.
I have a table in the database with department email addresses which can be added to the report data as a field or accessed separately. Is there any way to have the report run in such a way that instead of grouping by department it creates separate reports by department, and then emails them to the department email addresses?
Who is Participating?
meumaxConnect With a Mentor Commented:
You can do this using the data drivensubscription feature. Basically, you parameterize your report to filter on department. Then you have a source table containing rows of email addresses which are the recipients and a column with the department they look after. The report is run once per row in the table and each time, the department parameter is passed to the report so each manager will get their own filtered version of the report.

1. Set up and populate a recipient list table with two columns, email and department.
2. Parameterize the report so it accepts department as a parameter and filters the report by department
3. Browse to the report server http://<your report server>/reports
4. Open your report. Click the Subscriptions tab. Click New Data-driven subscription
5. At step 3, enter an SQL query which will query your recipient table and return the email addresses and departments
6. At step 4, choose to get the "To" address using a value from the database. Select the email column from your table. Set all the other options to taste such as output format and whether to include a link
7. In step 5, you specify that the department parameter gets its value from the database as well. Choose the department column from your recipient table
8. The last step is the create a schedule for your report to run

Once you've set it up, the schedule will run at the time specified and loop through the recipient list and emailing a customised report for each row in your recipient list. If you need to change recipients or departments, you simply update the recipient table.
silent_watersAuthor Commented:
Beautiful! So simple when you know how.
Thanks very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.