First, create a base report definition:
Dataset 1:AdventureWorks
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.Title IN (@Title)
Dataset2: TitleList
SELECT DISTINCT Title FROM HumanResources.Employee
Data Source must be defined as "Credentials stored securely in report server".
Creating a data-driven subscription:
Defining the recipient list:
SELECT DISTINCT Title FROM HumanResources.Employee
Extensions:
Other extensions have the quite similar explanation.Last, deliver reports based on the Title parameter. If you select the static value, all reports are same but are automatically delivered to all recipients.
From \\\queen\aol folder, you will see the reports created:
===Deliver reports through E-Mail
First, Run "Reporting Service Configuration"
Email Settings must be turned GREEN.
And then, create a table or modify the existing table with Email Address data.
Last, create a data-driven subscription with email delivery selection.
==
Useful example
You create a report named SalesDeviation, which includes Sales Target and Sales Representative's actual sales. All sales representatives do not meet their goals must be automatically received a deviation report.
You can accomplish the task by creating a data-driven report.
Create a data-driven subscription to the SalesDeviation report. Then, for the subscription's recipient, specify a Transat-SQL query that returns the names of sales representatives who have fallen short of their goals.
