Reporting Services Subscriptions in SQL Server 2019


By:   |   Updated: 2020-11-03   |   Comments (1)   |   Related: > Reporting Services Administration


Problem

Subscriptions are useful in SQL Server Reporting Services (SSRS) for report scheduling and delivering reports as per a defined criterion. Users might want the reports in a specific format such as Word, Excel, HTML, PDF, etc. In this tip, we are going to explore different subscription options for SQL Server 2019 SSRS.

Solution

SSRS provides rich customization reports for enhancing the user reporting experience. You can include graphs, charts, visuals or tables in the report. Once we create a report, we need to send it to the recipients. We can send the report manually if it is an ad-hoc requirement. However, it is not feasible to fetch the report every time and send it to the users manually.

We can deliver the reports in the following ways using SSRS:

  • Email delivery: We can add the email address of the individual recipients as well as user groups for delivering reports in the user's mailbox.
  • File Share delivery: You can export the reports using a subscription and place it on a shared directory. It helps you to share the extensive reports (above your email size restrictions) and saving mailbox space requirements.

Before we move further, we need to configure the SQL Server Reporting Services for subscriptions. Let's do it in the below section.

SSRS 2019 configurations for subscriptions

As we know, SSRS is a standalone application starting with SQL Server 2016. It does not come with the regular SQL Server installation media. You can download, install and configure it for your environment.  You can install the latest Microsoft SQL Server 2019 Reporting Services to get the comprehensive reports functionality.

SSRS 2019 configurations for subscriptions

We launch the installation and start the installation, as shown below.

Install Reporting Services 2019

Here, we can see it installs only Reporting Services. We need to have a database engine instance on the same or different server.

Install Reporting Service Only

Once you have done the initial configurations for the Service Account, Web Service URL and Web Portal URL, we need to do the following configurations for subscriptions.

Email settings: In the Report Server Configuration Manager, we need to specify the SMTP configurations for sending emails.  Here, specify the sender address, SMTP server (hostname or IP address), and authentication details as shown below.

Email Configurations

Subscription Settings

In the subscription settings, we can specify account credentials to access the file shares. This account should have read and write permissions on the file share that we specify in the report subscription.

Subscription Settings

For this tip, I deployed a sample SSRS report for which we will explore the subscriptions.

Sample SSRS report

Explore subscriptions for the SSRS reports

To access the subscriptions option, click on the three dots in the top right corner of the report and go to Manage.

subscriptions for the SSRS reports

In the Manage section, we get a menu on the left-hand side. Here, click on Subscriptions. If you already have a report subscription, you will see the list on this page.  As you can see, we do not have any subscriptions for this report.

Manage a SSRS report

Click on New Subscription, and it opens a page for configurations.

In this page, we see two types of subscriptions:

  • Standard subscription
  • Data-driven subscription
Types of subscription

Standard subscription

In the standard report subscription, SSRS generates a report and delivers it based on schedule and delivery method (email or file share).

Description: Specify a report subscription name

Standard subscription

Schedule

Click on Edit schedule and specify the details as per your requirements.

  • Define the subscription frequency in terms of hour, day, week, month or once.
  • Define a daily schedule.
    • You can select specific days for report subscription
    • Every weekday
    • Start time for the report
    • You can also set the start and end dates for the report subscription.  If you do not have any end date, report subscription runs as a defined schedule.
Report Schedule

Once you set the schedule, scroll down and select the delivery method.

Report destination

Windows File Share: If you select the Windows File Share delivery method, it opens the following additional configurations.

  • File Name: Specify a file name to export your SSRS report
  • Path: Specify a Windows file share path. This path should exist, and you should have permissions to access the file share.
Windows File Share

If the Windows shared path does not exist, you get the following error message.

windows fire share not found error

Render Format: This is the format in which we want to export the report to the file share. SSRS gives many options to choose the format. It can be Word, Excel, PowerPoint, PDF, TIFF, MHTML, CSV, XML and data feed as shown below.

Render Format
  • Credentials used to access the file share: Here, we can either use the file share account (configured in the report server configuration) or specify a new account with permissions to access the shared location.
  • Overwrite options:  Here, we instruct SSRS to choose actions in case the report already exists in the file share.
    • Overwrite the existing file
    • Do not overwrite the file if the file already exists
    • We can also ask SSRS to increment file name once it exports a new file

Email Delivery

You can also deliver the reports in the user mailbox. Change the delivery options to Email, and it opens the following configurations.

  • Specify the email addresses in the To, CC, BCC sections. If you need to specify multiple email address, you need to use the semicolon to separate them.
  • Specify a subject for your email report
  • We can include a report with the render format and include an SSRS report link. You can use both options – Include Report and Include Link for email report delivery.
  • Comments: It is the subject part of your Email. You should specify comments that you want your users to read in the email body.
Email Delivery

Click on Create subscription. If the Agent service is not running for your SSRS database instance, you get the following error because SSRS configures a SQL Server Agent job for the subscriptions.

SQL agent error

Start the SQL Server Agent service from SQL Server Configuration Manager, and it creates the subscription as shown below. You can select the subscription, and it enables the options to enable, disable, run or delete the subscription.

View the subscription

Data-Driven Subscription

Previously, we used a standard subscription for our SSRS report. In a data-driven subscription, we can use data or the report parameters values from the SQL query. Suppose you have hundreds of reports in your SSRS. It is not easy to manage the subscriptions manually for each report. You need to open the individual report properties and change the configurations. For example, we usually get a request to change the recipient's email address of the reports.

Click on the New subscription and select the data-driven subscription.

Data-Driven Subscription

Select the report delivery method. For example, if we select the email delivery method, it asks us for the data set and parameters values. You can compare it with the standard subscription that opens the page where you specify all details such as email address, subject and comments.

Before we move further, connect to your SQL Server database in SSMS and run the following query.

CREATE TABLE MySubscriptions
(
ToEmailAddress VARCHAR(200) ,
CCEmailAddress VARCHAR(200) NULL,
BccEmailAddress VARCHAR(200) NULL, 
[ReplyToEmailAddress] VARCHAR(200) NULL,
[IncludeReport] BIT DEFAULT 1, 
[RenderFormat] VARCHAR(20),
[Priority] VARCHAR(15),
[Subject] VARCHAR(150), 
[Comment]VARCHAR(150), 
[IncludeLink] BIT DEFAULT 1,
Active BIT DEFAULT 1
)
 
Insert into MySubscriptions values ('rajendra.gupta16@gmail.com',NULL,NULL,NULL,1,
'PDF','Normal','Test Data report','Sample data subscription',0,1)

It creates a table [MySubscription] and inserts values for the fields required for an email subscription.

Now, come back to your SSRS report, click on edit dataset.

Map data-driven subscription

Here, either create a new data set or use a shared data set. Let's make a new data set. Here, you can specify the connection string in the following format.

Data Source=SQLNODE1\INST1;Initial Catalog=AdventureWorks2019
Create a data source

Specify your credentials to connect with the data source.

Specify report credentials

Now, you need to specify the query to use the data-driven subscription values.

select * from ReportServer.dbo.MySubscriptions where active=1
Specify report query

Validate this query, and you can see the columns values it gets from the data set for data-driven subscriptions.

Validation t-SQL

Click Apply.

Now, you need to map the columns and their values with the report parameters. To map the values from your database table, select the source as Get Value from dataset. You can select the field from the drop-down in the next column.

You can leave the fields to use the default values. For example, I left Reply-To and Priority columns values as their default.

Map the source field with its value field

Click on Create subscription, and you can see both standard and data-driven subscriptions shown below.

report subscriptions

Conclusion

In this tip, we explored different types of subscription available in SQL Server Reporting Service 2019. You should evaluate the requirements and configure the required subscription for scheduling report delivery.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2020-11-03

Comments For This Article




Thursday, May 13, 2021 - 4:50:45 AM - Madhu Back To Top (88679)
Hi,

Will it be possible to send report name dynamically with Email subscription?
Like Myreport_@Parameter?

Thanks.


download














get free sql tips
agree to terms