Enhancing Customer Experiences with Subscriptions in SSRS

Introduction

We are going to discuss a very important option in SQL Server Reporting Services (SSRS), which is Subscriptions in SSRS. Typically, Reporting service is used to view reports. However, most users would prefer to receive the report to their inbox in the preferred report format, such as Word, Excel, or PDF in a preferred time. Further, you might want these reports to be delivered to a file share. Let us see how we can achieve these options using Subscriptions in SSRS and what are the challenges and pre-configurations.

Pre-configuration

There are two important configurations that need to be done for SSRS subscriptions. These configurations are done through the Report Service Configuration Manager. In the Report Service Configuration Manager, E-mail settings and Subscription Settings should be defined.

For e-mail settings, the sender address, an SMTP server, and the proper authentication method should be provided. Further subscription settings have to be configured. After these configurations, SSRS service should be restarted that can be done either from the Report Server Configuration Manager or from the windows services.

Sample Report

Let us create a sample report from SSRS and deploy the report to the SSRS server.

Then let us access the report from the SSRS Web Portal URL. In the Reporting Server portal, choose the Manage option, as shown in the below screenshot.

After the Manage option is selected, next is to configure the Data Source option so that report can be used for Subscriptions in SSRS.

To enable subscription, the credential has to be stored with the report, as shown in the below screenshot.

Another important pre-request in the Subscription in SSRS is the need for the SQL Server Agent Service running. Now we are ready to create subscriptions for the report.

Standard Subscriptions

Let us look at the Standard Subscription in SSRS. After selecting the Subscription option, you need to provide a name for the subscription name.

Let us select the Standard subscription, and we will leave the discussion for the Data-driven subscription, later in the article.

An important configuration in the subscription is selecting the schedule. This is the schedule at which the subscription is executed. You can either create a new schedule or used an existing schedule. Let us see how a new schedule is created, as shown in the below screenshot.

Though the SSRS subscription does not have the robustness to the scheduling, it has some basic features of a schedule. For example, you can define whether the subscription to be executed in Hourly, Daily, Weekly, Monthly, or Once. Further, you can define what are the relevant parameters according to the selection of the type of the schedule. For example, if you select hours, you need to choose the schedule of hours. If you select the Day or Week, you need to choose what are the weekdays (Sunday, Monday, Tuesday, etc.) that it should be executed. If it is Monthly, you need to choose which months that this schedule should be executed.

You further have the option of defining the beginning and end date for the schedule. However, the end date is optional as you can leave the end date blank if you wish to have not ending schedule.

After the schedule is created, next is to select the delivery method. Subscription in SSRS has two options: E-mail and File Share. Please note that the E-mail option will appear only if you configure the E-mail option in the Reporting Service Configuration Tool.

The following are the options for the E-mail delivery method.

Apart from the standard e-mail parameters such as To, Cc, Bcc, Subject, Priority, there are relevant configurations to the SSRS subscription. In the Subject, you can include the SSRS subscription parameters such as ReportName and ExecutionTime, etc.

In the content, you can include the link where users will be taken to the Report Server to view the current data. Apart from the link, you can send the report attached to the e-mail with Word, Excel, PDF, PowerPoint, Tiff, CSV format.

Similar to E-mail. The other option is File share in Subscription in SSRS.

In the file share option, you need to provide the UNC path and the format Word, PDF, etc. For the access credential, either you can use the credentials you provided from the Reporting Service Configuration tool or else you can use the provide the customized credentials.

There are overwrite options that provide whether you want to overwrite the existing file or not. The last option in this is the ability to create versions of existing files so that you can keep the previous file names as well.

When the subscriptions are created, you have the list of subscriptions in the list, as shown in the below screenshot.

You can enable, disable, or delete the subscriptions in SSRS. After the subscription is created, you can execute the subscription to test rather than wait till the schedule.

Let us look at the other features in subscriptions in SSRS.

Subscriptions for Parameterized Reports

Typically, there are parameters that are built into the report from which users have the option of choosing data according to their needs. For example, let us say that we have built Product Category in the report. This means that users can choose the parameter, and report data will be filtered according to the parameter selection.

Now the question is how subscriptions will work when there are parameters in the report.

When the parameters are included, there will be an additional option available at the Subscriptions in SSRS, as shown in the below screenshot.

Parameters will be displayed along with the possible values which were defined in the report. If it is a single value parameter, you have to enter one value, and if the parameter is multi-value, then you can choose multiple values.

Data-Driven Subscriptions

In a typical real-world environment, you want to send a report to different people with different parameters. For example, in the above sample report, there are four categories those are Components, Bikes, Clothing, and Accessories. You want to send reports with these parameters to different managers. That means, Manager A will receive the report Components while Manager B will receive a report for category Accessories. Further, depending on the usage, one user might need the report in PDF while the other may need it in Microsoft Excel format.

These configurations can be stored into a table, and that table can be linked to the subscription. This means that the subscription will work on the data configuration rather than hardcoded values as in standard subscription.

Further, you can enable or disable data subscriptions using the column in the table. First, let us create a table to store subscription data.

CREATE TABLE SubscriptionData

([To] VARCHAR(150) ,

Cc VARCHAR(150),

Bcc VARCHAR(150),

[Reply-To] VARCHAR(150),

[IncludeReport] BIT DEFAULT 0x1,

[RenderFormat] VARCHAR(15),

[Priority] VARCHAR(15) DEFAULT 'Normal',

[FileSharePath] VARCHAR(25),

[Subject] VARCHAR(150),

[Comment]VARCHAR(150),

[IncludeLink] BIT DEFAULT 0x1,

Parameter1 VARCHAR(50),

Active BIT DEFAULT 0x1

)

The above table is created so that you can enable e-mail or file share option for Data-Driven Subscription in SSRS.

Next, let us insert a couple of records to verify the data-driven subscription.

INSERT INTO [dbo].[SubscriptionData]

([To]

,[RenderFormat]

,[FileSharePath]

,Parameter1)

VALUES

('ManagerA@datos.com'

,'PDF'

,'\\LAPTOP-F514PK2P\Report'

,'Bikes')

INSERT INTO [dbo].[SubscriptionData]

([To]

,[RenderFormat]

,[FileSharePath]

,Parameter1)

VALUES

('ManagerB@datos.com'

,'Word'

,'\\LAPTOP-F514PK2P\Report'

,'Accessories')

According to the above two records, two reports should be created with different parameters and different render format. Now, let us configure this dataset to the SSRS subscription.

Once the data-driven subscription option is selected, next, you need to select the delivery option. To demonstrate, let us select the Windows File Share option, as shown in the below screenshot.

Next, we need to create the dataset of the subscription data or need to configure the above table into the data-driven subscription.

Where clause is included in the query so that you can enable or disable from the table without modifying anything in the SSRS end. After validating the query, you need to map the parameters to the query values.

You can either enter a value, or you can map from the dataset. With these configurations, now you are ready to create the subscription in SSRS.

When the created data-driven subscription is executed, you will see that two reports were created for different parameters and different rendering formats.

Other Notes

When subscriptions are created, SQL Server Agent Job is created under the SQL Server Agent, as shown below.

These jobs are created under the Report Server category. Make sure that you do not delete these jobs as they are an integral part of the Subscriptions in SSRS.

Conclusion

Subscriptions are created to enhance the usability of SQL Server Reporting Services. There are two types of subscriptions in SSRS. The subscription allows us to generate a file in a shared location or e-mail. You can generate reports for different rendering formats such as Word, PDF, Excel, etc.

Data-Driven Subscriptions in SSRS will allow you to create subscriptions in which relevant parameters are stored in a database table so that configurations can be done outside the SSRS.

Author
Recent Posts

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka