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.
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.
4. In next step, select Data source type and enter Connection String. Also give Credentials if required. Then click on Next Button.
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.
6. In next step, provide subscription settings as shown in below screen shot :
7. In next step, provide report parameter values as shown below. Then click on Next button.
8. In next step, specify when the subscription is processed. select options as shown in below screen shot. Then click on Next Button.
9. In next step provide details of schedule as shown in below step. And then click on Finish Button.
10. So we have done with Data-driven Subscription in SSRS. You can also see the history of subscription as shown in below screen.
Congratulations! We successfully created Data-driven Subscriptions in SSRS.