Data-driven Subscriptions in SSRS

Data-driven Subscriptions in SSRS

Data-driven Subscriptions is a very powerful feature of Sql Server Reporting Services.

It provides a way to use dynamic subscription data that is retrieved from an external data source at run time. A data-driven subscription can also use static text and default values that you specify when the subscription is defined. You can use data-driven subscriptions to do the following:

  • Distribute a report to a fluctuating list of subscribers. For example, you can use data-driven subscriptions to distribute a report throughout a large organization where subscribers vary from one month to the next, or use other criteria that determines group membership from an existing set of users.
  • Filter the report output using report parameter values that are retrieved at run time.
  • Vary report output formats and delivery options for each report delivery.

 

In this article I will show you how to create Data-driven Subscriptions in SSRS.

1. For data driven subscription, you must require a separate data source. So we created one sample database named as Subscribers in our local system. Execute following script:

USE Subscribers
CREATE TABLE [dbo].[UserInfo] (
    [SubscriptionID] [int] NOT NULL PRIMARY KEY ,
    [EmployeeID] [int] ,
    [LastName] [nvarchar] (50) NOT NULL ,
    [FileType] [bit],
    [Format] [nvarchar] (20) NOT NULL ,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('1', '289', 'Bhushan', '1', 'IMAGE')
INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('2', '284', 'Jasmin', '1', 'MHTML')
INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('3', '275', 'Abhi', '1', 'PDF')
GO

SELECT * FROM [dbo].[UserInfo]

2. Now open Internet Explorer and go to Report Manager URL. Then click on your SSRS Project. Now click on down arrow on the report which you want to subscribe and select Manage. Then click on Subscriptions from the left pane. In that click on New Data-driven Subscription.

2-Data-Driven Subscriptions in SSRS

3. In that screen enter the description, Select Windows File Share for Specify how recipients are notified and select Specify for this subscriptions only. Then click on Next Button.

3-Data-Driven Subscriptions in SSRS

4. In next step, select Data source type and enter Connection String. Also give Credentials if required. Then click on Next Button.

4-Data-Driven Subscriptions in SSRS

5. In next step, specify a query which returns a list of recipients. You can also validate query by clicking on Validate button. Then click on Next Button.

5-Data-Driven Subscriptions in SSRS

6. In next step, provide subscription settings as shown in below screen shot :

6-Data-Driven Subscriptions in SSRS

7. In next step, provide report parameter values as shown below. Then click on Next button.

7-Data-Driven Subscriptions in SSRS

8. In next step, specify when the subscription is processed. select options as shown in below screen shot. Then click on Next Button.

8-Data-Driven Subscriptions in SSRS

9. In next step provide details of schedule as shown in below step. And then click on Finish Button.

9-Data-Driven Subscriptions in SSRS

10. So we have done with Data-driven Subscription in SSRS. You can also see the history of subscription as shown in below screen.

10-Data-Driven Subscriptions in SSRS

Congratulations! We successfully created Data-driven Subscriptions in SSRS.

5 thoughts on “Data-driven Subscriptions in SSRS

  1. Hi Bhushan,

    What I want to ask you is can we mail reports with any specified formats like invoices, bills, receipts or we can only mail tabular reports using Data Driven Subscription?

Leave a Reply to kmtaha Cancel reply

Your email address will not be published. Required fields are marked *