Using SSRS Data Driven Subscription as a Data Driven Schedule

0
votes

I'd like to have a data driven "schedule", meaning every report has it's unique time (stored in a table which is populated every minute) when it needs to go out.

For example: (not all fields included)

To                 Parameter     Format    NextRunDate         ReportStatus
bob[at]email.com   SalesRegion1  Email     1/1/2012 9:00:00   'Unprocessed'
joe[at]email.com   SalesRegion2  Email     1/1/2012 9:05:00   'Unprocessed'
sara[at]email.com  SalesRegion1  Email     1/1/2012 8:55:00   'Processed'

if the time is currently 1/1/2012 9:01:00 my Data Driven Subscription "where statement" would be something like... 

where NextRunDate < GETDATE() -- in the past
   and ReportStatus = 'Unprocessed' -- <--here is my problem

This query is run every minute. It should only pull the report for bob[at]email.com...

My problem is here: I need to add logic that toggles the Report Status and 'Processed' once the report is sent... otherwise it runs every report every time...

Thoughts? Other ways to get this to work?

Running SQL Server 2k8 Enterprise

Thanks in advance!

1
reporting-servicesssrs-2008

1 Answers

0
votes

If the reports are run on a similar cycle (every minute or every five minutes as in your code) you could use BETWEEN and DATEADD to subtract the number of minutes (whatever) to get to the last time you ran the report through to now.

{WHERE NextRunDate BETWEEN DATEADD(mi, -5, GETDATE()) AND GETDATE()}

I hope that helps.