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.
We launch the installation and start the installation, as shown below.
Here, we can see it installs only Reporting Services. We need to have a database engine instance on the same or different server.
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.
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.
For this tip, I deployed a sample SSRS report for which we will explore the subscriptions.
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.
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.
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
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
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.
Once you set the schedule, scroll down and select the delivery method.
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.
If the Windows shared path does not exist, you get the following error message.
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.
- 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.
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.
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.
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.
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.
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.
Specify your credentials to connect with the data source.
Now, you need to specify the query to use the data-driven subscription values.
select * from ReportServer.dbo.MySubscriptions where active=1
Validate this query, and you can see the columns values it gets from the data set for data-driven subscriptions.
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.
Click on Create subscription, and you can see both standard and data-driven subscriptions shown below.
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
- You should create the standard and data-driven subscription as per your requirements in the email or file share delivery.
- Go through the Microsoft SQL Server Reporting Services tutorial on MSSQLTips.
- Use the following tips to use the SSIS send mail task with the foreach loop container.
