Report Subscription is a powerful option available in SSRS or SQL Reporting Services. Subscriptions allow users to create reports without interacting with Report Manager, BIDS, or Report Builder.
This article will show you how to apply a Report subscription with an example. It includes both the SSRS Email and File Subscriptions.
Report Subscription in SSRS
Before we start applying Subscriptions, Let me open my Report Manager to show the existing reports on our server.
For this demonstration, we will 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.
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 option, the report manager will navigate to the following page.
Subscriptions in SSRS support 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 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 required to access the folder 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 one.
- Subscription processing Options: This is the Schedule chosen.
Let me show you the folder we will use for this Subscription. Remember, it should be on Network (Shared Folder). And the User name and Password you will provide in the Subscription must have access to this folder.
Next, we are selecting the render Format as PDF. The screenshot below shows you 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 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.
FLetme schedule this for 2 minutes. For the SSRS subscription demonstration, 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 report page.
From the below screenshot, you can see that it is running successfully.
After a successful run, it will save the Report in PDF format. From the below, you can see the PDF that was generated by the Subscription.
Let me open PDF to check whether it is correct or not.
The Email subscription allows sending the Report through Email at regular intervals or specified timings. So, 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 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 chart)
- 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.
- Processing Options: This is to schedule the report subscription.
Let me add some dummy details, and Click OK.
Now you can see both the Email and File subscription in SSRS Reports.