In Reporting Services data driven subscriptions are used to run a query on a scheduled basis which returns a list of recipients to receive a report. What I have tried to do here is to show a simple example which will use this functionality to monitor an application and email out a report when any alerts that have been triggered.
You need the enterprise version of reporting services to use data driven subscriptions. In addition SQLAgent needs to be running and the data source must use stored credentials.
The example will use a simple table to hold details any events that need to be reported on. The application will insert rows into this table for each event that needs reporting on
create table ReportTriggers(
TriggerID int IDENTITY(1,1) NOT NULL,
TriggerDate smalldatetime NULL,
TriggerDescription varchar(50) NOT NULL
The stored procedure prGetReportTriggers will be queried and it will only populate the email recipient list if new records have been added to the ReportTriggers table that have not already been reported based on the fact that the triggerdate is null
Create procedure prGetReportTriggers
if (select COUNT(*) from ReportTriggers
where TriggerDate is null) > 0
select ‘firstname.lastname@example.org’ as Email
update ReportTriggers set TriggerDate = getdate()
where TriggerDate is null
select NULL as Email
The report that will be called by the subscription should use the following SQL to get those records that have just been updated by the prGetReportTriggers stored procedure.
select * from ReportTriggers
where TriggerDate = (select max(TriggerDate) from ReportTriggers)
Follow the following basic steps to create the data driven subscription
Call the stored procedure
Populate the To value with the returned email list
Set up the subscrption schedule to run as frequently as you require