Report Subscription in SSRS

An SSRS report subscription is a request to deliver the subscribed one to an Email or File System location at a specific time. Unlike executing a report manually, you can automate the process by scheduling the Date, Time, Report type, and location(Email or Hard drive). Additionally, you can specify the end time to stop the subscription permanently. For example, save weekly sales PDF files every Monday on a hard drive or every 15 days, email the Manager about the product’s performance, etc. These SSRS subscriptions help the business members check the Mailbox report and prepare for meetings or quick decision-making.

A single SSRS report allows you to create single or multiple subscriptions based on the requirement. For instance, you can create a Region Wise, country-wise, and city-based sales subscription if you have a Sales report. It all depends on the Parameters.

There are two types of subscriptions in SSRS: Standard and Data-Driven. For a standard subscription, you must provide all the details, including the parameter values, delivering report format, etc., when configuring the subscription. Whereas the SQL Server Integration Services Data-driven subscription provides the dynamic feature, it fetches the details of parameters, email address, etc., from the query time of execution.

Report Subscription is a powerful option available in SSRS or SQL Reporting Services and it includes both the Email and File Subscriptions. They allow users to create reports without interacting with Report Manager, BIDS, or Report Builder. This article will show you how to apply for a Report subscription with an example.

Report Subscription in SSRS

Before we start applying Subscriptions, Let me open my Report Manager to show the existing reports on our server.

To create a report subscription in SSRS, Clicking the down arrow beside the Report will open the menu items. Please select the Manage option from the menu items.

For this demonstration, we will use the previously created Report called List Reports. So please refer to the same to understand the Report data.

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 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.

Subscription 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 in SSRS

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 you want to save the Report—for example, PDF, XML, Excel etc.
• User name: Please specify the user name required to access the folder 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 SSRS 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 select the render Format as PDF. The screenshot below shows you the list of available render options.

Lastly, we must schedule the time to save the report in the given path. For this, please click on the Select Schedule button.

Once you click 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.

Let me schedule this for 2 minutes. For the subscription demonstration, In real-time, it might be Daily, 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 on the SSRS 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 the PDF to check whether it is correct or not.

SSRS Email Subscription

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 configuring the Email subscription, we must configure the SMTP setting in SQL Configuration Manager. 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.

Mail Information

Once you select the Email Delivery option, the Report Manager displays the following options on the subscriptions page. And the properties are:

• To: Email Id or SMTP address of the receiver. (To whom do you want to send the chart)
• Subject: Email subject.
• Render Format: Please select the format 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 Reports.