SSRS Data Driven Subscriptions - Part 1

SQL Server Reporting Services provides data driven subscription to use dynamics subscription data to distribute reports. Dynamics subscription data is retrieved based on predefined criteria from external data source at run time.

For example, there is an requirement to email a daily product listing report to product managers with product respective to their department. This requirement would be difficult to achieve through standard report subscription. However with data driven subscription, it could be easily done. Let's see how it works.

Create database and tables. There are 5 products. 3 products from department 1, and 2 products from department 2. Manager A and manager B are the managers on its respective department.

IF DB_ID('TEST') IS NOT NULL
DROP DATABASE TEST;
GO

CREATE DATABASE TEST;
GO

USE TEST;
GO
CREATE TABLE Products
(
 ProductID int IDENTITY(1,1),
 ProductName varchar(20),
 ProductDesc varchar(50),
 DepartmentID int
);

INSERT INTO Products (ProductName, ProductDesc, DepartmentID)
VALUES 
 ('Product 1', 'Product 1 Desc', 1), 
 ('Product 2', 'Product 2 Desc', 1),
 ('Product 3', 'Product 3 Desc', 1),
 ('Product 4', 'Product 4 Desc', 2),
 ('Product 5', 'Product 5 Desc', 2);

CREATE TABLE EmailRecipient
(
 RecipientID int IDENTITY(1,1),
 Name varchar(50),
 Email varchar(50),
 DepartmentID int
);

INSERT INTO EmailRecipient (Name, Email, DepartmentID)
VALUES 
 ('Manager A', 'ManagerA@Test.com', 1), 
 ('Manager B', 'ManagerB@Test.com', 2);
GO

Create report with SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). In this example, I am using Visual Studio 2012 equipped with Business Intelligence for Visual Studio 2012 (SSDT BI) templates (download here).



Add a report parameter (DepartmentID) to filter the product list with to this parameter.



Then, add and configure a new data source.



In the data source credential properties, select the use this user name and password, and enter the credential.



This step is important, the credential is required to stored in the report. If not, you will receive this error when adding a data drive subscription on the report.



The next step is to create a dataset for the report. Select use a dataset embedded in my report. Enter the query as shown in the picture below,



In the parameters, enter the @DepartmentID as parameter name as specified in the query, and select the DepartmentID report parameter (created in the previous step) in drop down list



Once the dataset is created, design the report like this. A table with Product Name and Product Description from the dataset.



Save the report and deploy the reporting service project to the Reporting Server configured in project property (TargetServerURL).

So far we have created the tables, design the report and deploy to report server. In the next post, we will configure data driven subscription.