How to create Standard Subscriptions in SSRS
Posted by Sunil Reddy Enugala on June 11, 2013
Subscription is used to execute a report on scheduled time and deliver it to the End User as an E-mail attachment or to be dropped in a file share in any specific format (like pdf, doc, etc.,). If the report server is configured for SharePoint integrated mode, you can also send a report to a SharePoint library.
Simply we can say that subscriptions can be used to schedule and then automate the delivery of a report.
SSRS supports two types of subscriptions, Standard and Data Driven Subscription.
Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.
We cannot use the expression to specify the values for the parameter when creating subscriptions.
Creating Standard Subscription:
I have a report which gives last 24 hours Report Execution log details. Please go through below figure which shows the preview of the report.
Please go through the below script which uses ReportServer database to get last 24 hours Execution log details of Reports .
I have @Date parameter in the above script and configured default value(which gives yesterday’s date as a value) as shown in the below figure.
The first thing while creating subscription is to add Credentials for the DataSource of the Report.
Open Report Manager and Click on the Data Source(ReportServerDB) of the
report(ReportsExecutionLog) to which we need to create subscription.
Check Credentials stored securely in the report server option and specify windows credentials. also select the checkbox use as Windows credentials when connecting to the data source. Click on Test Connection button to check the connection. Now click on Apply button.
Click on Drop down button of the Report to which we need to create subscription and select Subscribe option.
On the Subscription page, select the delivery option as E-Mail/Windows File Share.
Windows File Share Delivery
Select Windows File Share as delivery option and create a shared folder and specify in the path tab. Specify the credentials to access the file and click on Select Schedule to automate the Report Execution.
Specify the time and day of current week and select radio button to repeat the schedule.Click OK button.
Now we have scheduled the report, Click on check box Use Default option as we can’t pass value to the parameter of the Report. Click OK button.
Subscription is created and is scheduled to 03:03PM, check the status of the subscription in the below figure. It has generated pdf file of the report and placed in the mentioned shared folder path.
Please go through the below figure of shared path folder.
Again click on the drop down button of the Report. Select Subscribe option.
Note: E-Mail option will be available only after setting mail configuration in the Reporting Services Configuration Manager. Please go through the below figure to know the process.
After setting the Sender Address and SMTP Server property values, click on Apply button to complete the mail configuration.
Select the Delivery by Option as Email. Specify the Render Format and click on Select Schedule button to automate the report execution.
Specify the schedule time and click on OK button.
Check Use Default option to pass default value as the parameter to the report. Click on OK button to complete the Subscription configuration.
Subscription is created and is scheduled at 4:10PM. Check the status of the subscription in the below figure. Mail was sent to given E-mail ID.
Now we have created Standard Subscriptions in two possible way. Once the Subscription is created for a Report, you’ll find a job under Jobs folder in the SQL Server Management Studio as shown in the below figure.
As we have created two subscription, we have seen two jobs in the above figure.
To know the details of exists Subscription , please run the below script using ReportServer database.
Let me know your comments and queries.
I’ll meet you again with my post on Data Driven Subscription.
This entry was posted on June 11, 2013 at 1:35 pm and is filed under MS BI Related. 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.