Reporting Services – Data -Driven Subscription

 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
as
begin
    if (select COUNT(*) from ReportTriggers
        where TriggerDate is null) > 0
        begin
                select ‘yourname@yourdomain.com’ as Email
                update ReportTriggers set TriggerDate = getdate()
                where TriggerDate is null
        end
                select NULL as Email
end       
        

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