SQL Server Reporting Services allow your users to subscribe to reports. Using report subscriptions, you can deliver reports to your end users' mailbox in the desired format on a scheduled interval.
Before you can make use of report subscriptions, e-mail delivery options need to be configured for SQL Server Reporting Services:
- Log on to the server that hosts SQL Server Reporting Services with an account that has administrative privileges on both the local computer and SQL Server.
- Start Reporting Services Configuration Manager.
- Check the Server Name and Report Server Instance to make sure that you are connecting to the correct instance of SQL Server Reporting Services. Click on Connect.
- Under E-mail Settings, enter the e-mail address that you want to use as the sender address for report delivery.
- Enter the SMTP Server hostname or IP address, and then click on Apply. Click on Exit to close the Reporting Services Configuration Manager window.
SQL Server Reporting Services uses SQL Server Agent to schedule and run the report subscriptions. For this to work, SQL Server Agent needs to be configured to start automatically when the operating system starts:
- Log on to the server that hosts SQL Server Reporting Services with an account that has administrative privileges on both the local computer and SQL Server.
- Start SQL Services Configuration Manager.
- Under SQL Server Services, double-click on SQL Server Agent.
- Under the Service tab, choose Automatic as the Start Mode. Click on OK.
- Right-click on SQL Server Agent and click on Start.
Next, we will walk you through the steps required for creating and managing report subscriptions in SQL Server Reporting Services:
- Open your web browser and navigate to the following URL:
http://[SCSMDWSQL]/Reports. - Replace [SCSMDWSQL] with the fully qualified domain name of the SQL server, which is used for reporting. If SQL Server Reporting Service is running as a named instance, the syntax of the URL will be the following:
http://[SCSMDWSQL]/Reports_[InstanceName]. - Navigate to the report that you would like to create a subscription for. In this example, we are creating a subscription for the report we created in the Creating reports with Report Builder recipe. Navigate to the SystemCenter | ServiceManager | Custom Reports | Incident Management folder.
- Hover over the report file and click on the arrow next to the report; then click on Subscribe.
- Enter the e-mail address you would like the report to be delivered to. Optionally, enter e-mail addresses in the Cc and Bcc fields. You can also enter a custom Reply-To address, if replies from users should be sent to a different e-mail address from the sender address.
- If you want the report to be attached to the e-mail, check the Include Report option and choose the desired render format. We are going to choose PDF in this example.
- Optionally, check the Include Link option, if you would like a URL to be added to the e-mail that allows the recipient to navigate directly to the report using a web browser.
- Click Select Schedule and define the delivery frequency for this subscription. Click on OK.
- Click OK to save and activate the subscription.
To manage your subscriptions, click on the My Subscriptions link from the top-right menu bar of the SQL Server Reporting Services web page. From here you can edit and delete the existing report subscriptions.
SQL Server Reporting Services uses SQL Server Agent for scheduling and running report subscriptions. Each subscription creates a job for SQL Server Agent with a random GUID as the job name. When subscription jobs are executed, SQL Server reads the configuration of the subscription from the ReportServer database, calls SQL Server Reporting Services to run the report, and then sends the result to the recipients using the SMTP server configured for SQL Server Reporting Services.
A common request when working with report subscriptions is to offer the ability to send reports to user-defined recipients. This section walks you through the steps required to fulfill this request.
When users that are not a member of the Content Manager security role create subscriptions, the recipient name in the To: field is self-addressed using the domain user account of the person creating the subscription.
If you are using an SMTP server or forwarder that uses e-mail accounts that are different from the domain user account, the report delivery will fail when the SMTP server tries to deliver the report to that user.
To work around this issue, you can modify configuration settings that allow users to enter a name in the To: field:
- Open
RSReportServer.configwith a text editor. The file can be found on the server hosting SQL Server Reporting Services in the SQL Server installation directory under theMSRS10_50.[InstanceName]Reporting ServicesReportServersub-folder. - Set
SendEmailToUserAliastoFalse. - Set
DefaultHostNameto the DNS name or IP address of the SMTP server or forwarder. - Save the file.