October 1, 2007 at 12:56 pm
This may be obvious, but I'm inexperienced in SSRS, and I can't find an easy answer in the various documentation sources.
I'm working on porting an Access 97 application to SQL Server 2005 which will run as a series of
SQL server agent jobs.
One of the main tasks is to pick up files from a third party, import them into the database, and if appropriate, email the new "rows" in a report to various people (different reports based on different
queries)
This runs hourly. The old application ran the report and emailed it via a macro.
I'd like to do a similar thing from a stored procedure. As I understand it, I can set up a scheduled job to run from Reporting services, and it can run queries, so I can have it update the table to indicate that those rows have been reported (only new rows get emailed out). Sometimes there is no data, and so there should be no email. Is this possible in this scenario?
I'd like the simplest possible solution, but I am an experienced developer, so If I have to use CLR to access the reporting server web services, I can. It seems overkill to programmatically generate the report and create a subscription. Can I create a static "Quiescent" report that I can activate dynamically each time I need to?
Other approaches?
Thanks,
Kevin
October 1, 2007 at 2:11 pm
If you are using Enterprise Edition, you can create a data-driven subscription. This allows you to use a select statement to determine your recipient list. If you use a select statement that will not return a recipient list unless there is data, it will only run and email the report if there is data.
If you are not using Enterprise Edition, you can create and schedule your report. Then, go into the Job Agent and find the report you just scheduled. Add a step before the report step that checks if records exist and skips the step that runs the report if there are no records.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply