Report Subscription is a powerful option available in SSRS or SQL Reporting Services. SSRS subscriptions allow the user to create reports without interacting with Report Manager, BIDS or Report Builder.
In this article, we will show you how to apply a Report subscription in SSRS with an example. It includes both the SSRS Email Subscription and File Subscription.
Report Subscription in SSRS
Before we start applying Subscriptions in SSRS, Let me open my Report Manager to show the existing reports on our server.
For this subscriptions demonstration, we are going to use the previously created Report called List Reports. So please refer to the same to understand the Report data.
To create a report subscription in SSRS, Click the down arrow beside the Report will open the menu items. Please select the Manage option from the menu items.
Once you select the Manage option, the Report manager will navigate you to a new page with the following tabs. You can use this page to manage the advanced setting of this Report.
Under the properties tab, it will show the Report Created user, created and modified dates, and the Report size.
Please navigate yourself to the Subscriptions tab and select the New Subscription option in SSRS.
Once you choose the New Subscription option, the report manager will navigate to the following page.
Subscriptions in SSRS supports two types of Delivery options: Windows File Share, and Email. Currently, we haven’t configured the SMTP Email settings. That is the reason; it is displaying only one delivery option.
Windows File Share Subscription
The Windows File share subscription in SSRS allows saving the Report in the file system (Hard Drive) at regular intervals or specified timings. For example, Reports for Saturday Meetings, Monthly Progress, Temperature of the Last batch, etc.
Once you select the Windows File Share option, the Report manager will display the following page. And the properties of this Windows File share subscription in SSRS are:
- File Name: Please specify the file name
- Path: Specify the Network path where you want to save the Report.
- Render Format: Please select the format that you want to save the Report. For example, PDF, XML, Excel etc.
- User name: Please specify the user name that is required to access the folder that you specified in the Path section.
- Password: Specify the Password for the above-specified user.
- Overwrite an existing file with a newer version: This option will always keep a fresh copy of the subscribed Report.
- Do not Overwrite the file if a previous version exists: This option will not overwrite the existing file.
- Increment file names as newer versions are added: This option will add different names for a fresh copy of the subscribed Report.
- Subscription processing Options: This is Schedule the report subscription.
Let me show you the folder that we are going to use for this Subscription. Remember, it should be on Network (Shared Folder). And the User name, Password that you are going to provide in the Subscription must have access the privilege to this folder.
Next, we are selecting the render Format as PDF. From the below screenshot, you can see the list of available render options.
Lastly, we have to schedule the time – when we want the Report to save in the given path?. For this, please click on the Select Schedule button.
Once you click on the Select Schedule button, the report manager will navigate you to a new page. You can use this page to schedule your subscriptions in SSRS.
- Schedule Details: Use this section to schedule the report subscription on Hourly, Daily, Weekly, Monthly, or Once in a lifetime. Don’t forget to mention the start time.
- Start Date: please specify when you want to start the Report subscription.
- Stop this Schedule on: When you want to stop this subscription.
For the SSRS subscription demonstration purpose, let me schedule this for 2 minutes. In real-time, it might be Daily, or Every Monday, etc.
Let me provide the User name and password to access the report folder.
Now you can see the newly created file subscription in the SSRS subscriptions report page.
From the below screenshot, you can see that the subscription is running successfully.
After a successful run, Reporting Service will save the Report in PDF format. From the below, you can see the PDF that was generated by the SSRS subscription.
Let me open PDF to check whether it is correct or not.
The Email subscription in SSRS allows sending the Report through Email at regular intervals or specified timings. For example, You can create a subscription that will send you last week’s progress report to your Email, etc.
Before we start configuring the SSRS Email subscription, we have to configure the SMTP setting in SQL Reporting Services Configuration Manager. And to do this, navigate to the Email Settings tab and provide the Sender Address and SMTP server name. For now, we are entering the dummy details.
Once you select the Email Delivery option, The Report manager displays the following options in the SSRS subscriptions page. And the properties are:
- To: Email Id, or SMTP address of the receiver. (To whom you want to send the report)
- Subject: Email subject.
- Render Format: Please select the format that you want to save the report. For example, PDF, XML, Excel etc.
- Priority: Please specify the Email priority. Normal will do the work for you. If not, go for High.
- Subscription processing Options: This is schedule the report subscription.
Let me add some dummy details, and Click OK
Now you can see both the Email Subscription and File subscription in SSRS Reports.