# Configuring SSRS Subscription

SQL Server Reporting Services (SSRS) subscriptions provide a way to automate the delivery of reports on a scheduled basis. It offers several features and capabilities that enable users to customize how, when, and where reports are delivered.

This article will guide you to efficiently manage the SSRS subscription. It covers the Standard and Data-driven subscriptions, scheduling, and delivery methods via E-mail and Windows File Share. It also addresses report formatting and overwriting options.

# Prerequisites

# Minimum Requirements

Name Version
SQL Server Version 2016 or later
SQL Server Edition Standard
Minimum Requirements

SSRS provides two primary subscription types: Standard Subscriptions, offering a manual approach for fixed recipients and parameters, and Data-Driven Subscriptions, dynamically determining recipients and parameters based on retrieved data, offering a more flexible and automated delivery method. Each subscription type caters to different scenarios, providing users with options tailored to their specific reporting needs.

It has the following parts:

  1. Standard subscription
  2. Data-driven subscription

# Standard Subscription

SSRS Standard Subscriptions provide a manual yet straightforward way to automate report delivery, allowing users to specify fixed recipients and parameters for consistent, scheduled delivery via email or file share.

  1. Open the Analysis Server Web Interface.

  2. Go to the Reporting Services page. Access this by clicking Reporting in the left pane.

  3. Under the View and Manage Reports section, click the link Click here to open. The page will redirect to the Open iT Reporting Services reports portal.

    Open iT Reporting Services

    Open iT Reporting Services

  4. On the Folders section, click LicenseAnalyzer Level 1. This will display the available folders and paginated reports you can configure.

    LicenseAnalyzer Level 1

    LicenseAnalyzer Level 1

  5. To manage report subscriptions, click the three-dot icon on a paginated report and select the Subscribe option.

    Manage Reports

    Manage Reports

  6. Select Subscriptions from the left pane. This page lists created report subscriptions. Suppose there are no report subscriptions created. This page will be empty.

  7. Create a new subscription by clicking the New subscription button.

    Manage Subscription

    Manage Subscription

  8. Under the Type of subscription, select Standard subscription.

    Standard Subscription

    Standard Subscription

  9. Configure the Report-specific schedule by clicking on Edit schedule.

  10. Choose a delivery method between, Windows File Share or E-mail.

  11. Choose among the Render Format options to determine how the report will be formatted and presented in the saved file when it is delivered.

    Render Format Description
    Word Renders in Word format, preserving the document structure and layout that users can edit.
    Excel Renders in Excel format, allowing users to open and edit the data in a spreadsheet.
    Powerpoint Generates slides based on the report content.
    PDF Preserves the layout and formatting of a report.
    TIFF file Renders reports in a high-quality image file.
    MHTML (Web Archive) Renders reports in a web archive format that combines HTML and resources like images into a single file.
    CSV (comma delimited) Renders reports in tabular data that can be easily imported into spreadsheet applications.
    XML file with report data Represents the report definition language, allowing users to save or share the underlying structure of a report.
    Data Feed Renders reports in a continuous stream of data that is provided in a standardized format.
    Render Format - Options

  12. Click Create subscription. If the SQL Agent service is not running in your machine, an error will show. Follow the instructions from this link to start the SQL Agent Service.

    SQL Agent service is not running

    SQL Agent service is not running

  13. You will be directed to a page displaying all available subscriptions upon creation. Selecting a subscription will allow you to modify it, providing options to Enable, Disable, Run, and Delete options.

    Subcriptions

    Subscriptions

# Data-driven subscription

You can use a data-driven subscription to populate data or report parameter values to manage subscriptions for a large number of reports and change configurations, such as recipient email addresses, which require accessing individual report properties.

  1. Open the Analysis Server Web Interface.

  2. Go to the Reporting Services page. Access this by clicking Reporting in the left pane.

  3. Under the View and Manage Reports section, click the link Click here to open. The page will redirect to the Open iT Reporting Services reports portal.

    Open iT Reporting Services

    Open iT Reporting Services

  4. On the Folders section, click LicenseAnalyzer Level 1. This will display the available folders and paginated reports you can configure.

    LicenseAnalyzer Level 1

    LicenseAnalyzer Level 1

  5. To manage report subscriptions, click on the three-dot icon on a paginated report and select the Subscribe option.

    Manage Reports

    Manage Reports

  6. Select Subscriptions from the left pane. This page lists created report subscriptions. Suppose there are no report subscriptions created. This page will be empty.

  7. Create a new subscription by clicking the New subscription button.

    Manage Subscription

    Manage Subscription

  8. Under the Type of subscription, select Data-driven subscription.

    Data-driven Subscription

    Data-driven Subscription

  9. Configure the Report-specific schedule by clicking on Edit schedule.

  10. Choose a delivery method between, Windows File Share and E-mail.

  11. Choose among the Render Format options to determine how the report will be formatted and presented in the saved file when it is delivered.

    Render Format Description
    Word Renders in Word format, preserving the document structure and layout that users can edit.
    Excel Render in Excel format, allowing users to open and edit the data in a spreadsheet.
    Powerpoint Generates slides based on the report content.
    PDF Preserves the layout and formatting of a report.
    TIFF file Render reports in a high-quality image file.
    MHTML (Web Archive) Renders reports in a web archive format that combines HTML and resources like images into a single file.
    CSV (comma delimited) Render reports in tabular data that can be easily imported into spreadsheet applications.
    XML file with report data Represents the report definition language, allowing users to save or share the underlying structure of a report.
    Data Feed Renders reports in a continuous stream of data that is provided in a standardized format.
    Render Format - Options

  12. Click on Edit dataset.

    Edit dataset

    Edit dataset

    Create a table in SSMS before you proceed. Do this by following these instructions.

    1. On your machine, open SSMS and click on Connect then select the Database Engine. Put the Server name and select an Authentication type.

    Connect to Database Engine

    Connect to Database Engine


    Connect to Server

    Connect to Server

    1. Select New Query from the top panel.

    New Query

    New Query

    1. Run the following query.

      Example
      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 ('user@email.com',NULL,NULL,NULL,1,
      'PDF','Normal','Test Data report','Sample data subscription',0,1)
      

      A dialog confirming a successful query will appear below.

    Run Query

    Run Query

  13. Select between A shared data source or A custom data source. Put the following in the Conection String.

    Example
    Data Source=<localhost>; Initial Catalog=<database>; Integrated Security=True;

SSRS Subscription - Data Source

SSRS Subscription - Data Source

  1. Choose the type of credentials to use and fill in the User name and Password.

  2. To use data-driven subscription values, validate the following query from the one you made earlier in SSMS.

    Example
    select * from master.dbo.MySubscriptions
    where active=1
  3. Click on Validate Query, then wait for a Validation successful prompt.

  4. Click Apply.

Validate Query

Validate Query

  1. Configure the Delivery options according to the delivery method you chose. Windows File Share or E-mail.

  2. Click Create subscription. If the SQL Agent service is not running in your machine, an error will show. Follow the instructions from this link to start the SQL Agent Service.

    SQL Agent service is not running

    SQL Agent service is not running

  3. You will be directed to a page displaying all available subscriptions upon creation. Selecting a subscription will allow you to modify it, providing options to Enable, Disable, Run, and Delete options.

    Subcriptions

    Subscriptions

# Report-specific Schedule

SSRS has a Report-specific schedule where you can set up a schedule for a subscription to run at specific intervals, such as daily, weekly, or monthly. This allows you to automate the report generation and delivery process.

To configure, follow these instructions:

Click on Edit schedule and configure the following details:

Hourly Schedule

  • Run the schedule every certain amount of hours and minutes.

    Hourly Schedule

    Hourly Schedule

Daily Schedule

  • Run the report on specific days only.

  • Run the report every weekday (Monday - Friday).

  • Repeat after certain number of days.

    Daily Schedule

    Daily Schedule

Weekly Schedule

  • Repeat after certain number of weeks.

  • Run the report on specific days only.

    Weekly Schedule

    Weekly Schedule

Monthly Schedule

  • Run the report on specific months only.

  • Select on what week of the month and what day of the week to run scheduled report.

  • Run the report on certain calendar day(s) only.

    Monthly Schedule

    Monthly Schedule

One-time schedule

  • Run the scheduled report once.

    One-time Schedule

    One-time Schedule

Set the designated time to run the report, as well as the start and end dates for the report subscription. You can also choose to stop the scheduled report on a specific date.

Set Time and Date

Set Time and Date

# Delivery Method

SSRS Subscriptions offers two types of delivery method for where the report will be delivered:

  • Windows File Share
  • E-mail

# Windows File Share

The Windows File Share delivery method distributes reports by saving them to a shared network folder. This method allows you to specify a shared folder as the destination for the delivered reports.

# Prerequisites

# Configuring File Share Account

In the Report Server Configuration Manager, the Subscription Settings section allows administrators to configure the details of the account used for file share delivery, specifying the necessary credentials and permissions for storing reports on a shared file system in the SSRS.

  1. Open the Report Server Configuration Manager. Access this by typing Report Server Configuration Manager in the Windows Search Box.

    Report Server Configuration Manager

    Report Server Configuration Manager

  2. Go to the Subscription Settings in the left pane.

    Subscription Settings

    Subscription Settings

  3. Provide the Account and Password.

  4. Click Apply, then wait for a confirmation message indicating a successful configuration.

In Standard subscription, follow these instructions:

  1. Specify a file name for the exported SSRS report.

  2. Provide a valid Windows file share path with appropriate permissions that can access the path. It must be in UNC format.

  3. Select from the drop-down which format the report should be in.

  4. Choose whether to use the file share account or provide a Windows user credentials.

  5. Decide what to do to an existing file if a previous version exists.

    Windows File Share - Configuration

    Windows File Share - Configuration

In Data-driven subscription, map the columns and their values with the report parameters. Follow these instructions:

  1. To connect values from your database table, choose Get Value from dataset as the source of value.

  2. Choose a Value/field from the dropdown.

  3. You can leave the values of the Reply-To and Comment fields as is.

Data Driven - Email

Data Driven - Email

# E-mail

Reports can be sent and distributed as email attachments to specified recipients through the SQL Server Reporting Services(SSRS) email delivery method.

# Prerequisites

# Configuring SMTP Email Settings

Administrators can configure the SMTP server details, sender information, and authentication settings for email delivery of reports in the SSRS in the Email Settings section of the Report Server Configuration Manager.

  1. Open the Report Server Configuration Manager. Access this by typing Report Server Configuration Manager in the Windows Search Box.

    Report Server Configuration Manager

    Report Server Configuration Manager

  2. Go to the E-mail Settings in the left pane.

    E-mail Settings

    E-mail Settings

  3. Provide the details for the Sender Address, SMTP Server (hostname), and the Authentication details.

  4. Click Apply, then wait for a confirmation message indicating a successful configuration.

In Standard subscription, follow these instructions:

  1. Specify an email address in the To, Cc, and Bcc fields. Separate multiple email addresses by using a semicolon (;).

  2. Write a subject for the email report.

  3. Choose whether to include a report with the selected render format or the SSRS report link. You can use both options – Include Report and Include Link for email report delivery.

  4. Choose the order of email delivery: High priority is sent first, followed by Normal, then Low.

  5. Write a body content to provide additional information to include along with the attached report.

    Email - Configuration

    Email - Configuration

In Data-driven subscription, map the columns and their values with the report parameters. Follow these instructions:

  1. To connect values from your database table, choose Get Value from dataset as the source of value.

  2. Choose a Value/field from the dropdown.

  3. You can leave the values of the Reply-To and Comment fields as is.

Data Driven - Email

Data Driven - Email

# Recommended Reading

The following configuration is needed for the SSRS Subscription to work.

   Using Reporting Services Console Application

   Reporting

  Open iT Reporting Services

  Email Settings

We value your feedback!

Please take a few minutes to complete our survey and share your thoughts on your recent experience with our documentation.

Take survey

Close