Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Generate multiple SSRS reports by grouped field

Posted on 2011-11-21
Medium Priority
Last Modified: 2012-05-12
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?
Question by:silent_waters
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

meumax earned 2000 total points
ID: 37174791
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.

Author Closing Comment

ID: 37175896
Beautiful! So simple when you know how.
Thanks very much.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question