title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | helpviewer_keywords | |||
---|---|---|---|---|---|---|---|---|---|---|---|---|
Create a data-driven subscription (SSRS Tutorial) |
Learn about data-driven subscriptions through an example that creates a data-driven subscription used to generate and save filtered report output to a file share. |
maggiesMSFT |
maggies |
06/21/2024 |
reporting-services |
reporting-services |
concept-article |
updatefrequency5 |
|
Learn about how you create a data-driven subscription by using the SQL Server Report Service (SSRS) tutorial. The tutorial walks you through a simple example that creates a data-driven subscription to generate and save filtered report output to a shared file.
[!INCLUDEssRSnoversion] data-driven subscriptions allow you to customize and automate the distribution of a report based on dynamic subscriber data. Use data-driven subscriptions in the following scenarios:
- Distribute reports to a large recipient pool whose membership might change from one distribution to the next. For example, email a monthly report to all current customers.
- Distribute reports to a specific group of recipients based on predefined criteria. For example, send a sales performance report to all sales managers in an organization.
- Automate the generation of reports in a wide variety of formats, for example
.xlsx
and.pdf
.
Report server administrators typically create and maintain data-driven subscriptions. The steps to create data-driven subscriptions require building queries, knowledge of data sources that contain subscriber data, and elevated permissions on a report server.
The tutorial uses the Sales order report created in the tutorial Create a basic table report (SSRS tutorial) and data from the sample database AdventureWorks2022.
Your computer must have the following installed to use this tutorial:
-
An edition of [!INCLUDEssNoVersion] that supports data-driven subscriptions. For more information, see Editions and supported features of SQL Server 2022.
-
Run the Report Server in native mode. The user interface described in the tutorial is based on a native mode report server. Subscriptions are supported on SharePoint mode report servers but the user interface is different than what is described in the tutorial.
-
SQL Server Agent service.
-
A [!INCLUDEssRSnoversion_md] role assignment that includes the Manage all subscriptions task on the sample report. This task defines a data-driven subscription. If you're an administrator on the computer, the default role assignment for local administrators provides the permissions necessary for creating data-driven subscriptions. For more information, see Grant permissions on a native mode report server.
-
Write permissions to a shared folder that’s accessible over a network connection.
Estimated time to complete the tutorial: 30 minutes. An extra 30 minutes if you need to complete the basic report tutorial.
The tutorial is divided into three lessons:
Lesson | Description |
---|---|
Lesson 1: Create a sample subscriber database | Create a table in the local SQL Server database that contains subscriber information. This information includes the order numbers to use for filtering and output file formats. |
Lesson 2: Configure report data source properties | Configure a report data source to run a report unattended and on a schedule. Modify the report dataset to include a parameter supplied by the subscriber data. |
Lesson 3: Define a data-driven subscription | Create a data-driven subscription by using the Data-Driven Subscription Wizard. |
The following diagram illustrates the basic workflow of the process:
Step | Description |
---|---|
(1) | The subscription configuration sets the source report, schedule, and the field mapping to the subscribers Database. |
(2) | The OrderInfo table contains four order numbers to use for filtering, 1 per file. The table also contains the file formats for the generated reports. |
(3) | Information from the [!INCLUDE sssampledbobject-md] database is filtered and returned in the report. |
(4) | Reports created in the file formats specified by the OrderInfo table. |
:::image type="content" source="../reporting-services/media/ssrs-tutorial-datadriven-flow.png" alt-text="Diagram that shows the basic workflow of the process to create a subscription.":::