Data-Driven Subscriptions in SSRS

 Data-driven subscriptions in SQL Server Reporting Services (SSRS) allow for dynamically generating report subscriptions based on query results. This powerful feature enables reports to be personalized and sent to different recipients with varying parameters and delivery options.

Key Concepts

1. Data-Driven Subscription: A subscription that uses a query to retrieve subscription data such as recipient list, report parameters, and delivery options.
2. Delivery Extensions: Mechanisms for delivering reports (e.g., email, file share).

Prerequisites

• SQL Server Reporting Services configured.
• A report deployed to the SSRS server.
• Permissions to create and manage subscriptions.
• An SMTP server for email delivery or a file share for file delivery.

Steps to Create Data-Driven Subscriptions

1. Create the Report Subscription Query
2. Configure the Data-Driven Subscription
3. Set Up Delivery Options
4. Schedule the Subscription
5. Monitor and Manage Subscriptions

1. Create the Report Subscription Query

The query for a data-driven subscription retrieves the data needed for generating subscriptions, such as email addresses, file paths, and parameter values.

Example Query

Assume you have a report that shows sales data, and you want to send it to different sales managers based on their regions.

sql
SELECT 
    EmailAddress, 
    Region, 
    ReportFormat 
FROM 
    SalesManagers

2. Configure the Data-Driven Subscription

1. Open Report Manager:

   • Navigate to the SSRS web portal (e.g., http://<your-report-server>/Reports).

2. Navigate to the Report:

   • Find the report you want to subscribe to and click on it.

3. Create a New Subscription:

   • Click on "Manage" or "Subscribe" depending on your SSRS version.
   • Choose "New Subscription" and select "New Data-Driven Subscription".

4. Specify Data Source:

   • Choose a data source for the subscription query. This can be a shared data source or a specific one for the subscription.

5. Enter the Subscription Query:

   • Enter the query you created in Step 1.
   • Validate the query to ensure it runs correctly.

3. Set Up Delivery Options

1. Define the Delivery Method:

   • Choose the delivery method (e.g., Email, File Share).

2. Map Query Results to Delivery Options:

   • Map the columns from your query to the required delivery fields.
   • For Email:
     • To: Fields!EmailAddress.Value
     • Subject: "Sales Report for " & Fields!Region.Value
     • Render Format: Fields!ReportFormat.Value
   • For File Share:
     • Path: Fields!FilePath.Value
     • File Name: "SalesReport_" & Fields!Region.Value & ".pdf"

4. Schedule the Subscription

1. Set Schedule:

   • Define when and how often the subscription runs.
   • Options include daily, weekly, monthly, or on specific dates and times.

2. Parameters:

   • If your report requires parameters, map the query results to the report parameters.

5. Monitor and Manage Subscriptions

1. View Subscription Status:

   • Check the status of the subscription to ensure it’s running as expected.
   • Monitor for any errors or issues in the subscription history.

2. Manage Subscriptions:

   • Edit, pause, or delete subscriptions as needed.
   • Ensure the query and delivery options remain accurate and up-to-date.

Example Walkthrough

Scenario: Email Sales Reports to Regional Managers

1. Create Subscription Query:

   sql
   SELECT 
       EmailAddress, 
       Region, 
       'PDF' AS ReportFormat 
   FROM 
       SalesManagers
   
   

2. Configure Data-Driven Subscription:

   • Data Source: Use an appropriate data source.
   • Query: Enter the subscription query.
   • Validate: Ensure the query runs correctly.

3. Set Delivery Options (Email):

   • To: Fields!EmailAddress.Value
   • Subject: "Sales Report for " & Fields!Region.Value
   • Render Format: Fields!ReportFormat.Value
   • Include Report: Yes, attach the report.

4. Schedule:

   • Frequency: Weekly, on Monday at 8 AM.

5. Parameter Mapping:

   • Region Parameter: Fields!Region.Value

By following these steps, you can set up data-driven subscriptions in SSRS that dynamically generate and deliver reports based on data. This approach ensures that recipients receive personalized reports tailored to their specific needs and schedules.