Recently, we had a customer that wanted us to create and email 90 reports a month on a schedule. The reports were existing Microsoft SQL Server Reporting Services reports – SSRS. Most of the reports had a small number of recipients, except for the territory report which has a large number of recipients. The reports were to be delivered in Excel format.
We envisioned creating each of the reports using the SSRS web service and then writing some kind of console application to create emails, find recipients and then include the right reports as attachments. The code would need to schedule creation and delivery, as well as complete descriptive email subject captions. We found a better way. Microsoft Server Data Driven Subscriptions.
Microsoft Server Data Driven Subscriptions is paid-for system code that will create and email reports on a schedule that you define. The configuration is through SSRS menu screens. These screens include functionality for recipients, cc, the subject line of the email and have very rich scheduling options. Once your stored procedure is written, you use these screens to map the output parameters of your stored procedures to the input parameters of the SSRS report. The stored procedure also provides email addresses.
You do need to create a shared data source to point at your database and you need to write your stored procedures. As a rough sketch, our stored procedure for the territories report returned a row for each territory that included the territory database id, the time period for the report, the subject line of the email (including territory name and datestamp) and the recipients for that territory. We had no trouble getting multiple emails to work – we had the stored procedure create them as:
When we exceeded 10 reports per stored procedure, we had to increase the number of reports that SSRS allowed one user to have active at one time. This was accomplished through changing the MaxActiveReqForOneUser line in rsreportserver.config.
We are quite happy to have Microsoft system code do our heavy lifting for us and perhaps this is something you can benefit from as well.