November 21, 2012 at 2:28 pm
Hi,
I am trying to schedule a report to run every hour in SSRS 2008 R2 Enterprise edition. I want the report to be sent in email when the report has got data in it.
If the report has no data , it should n't send email. Can we do it in SSRS?
Please help.
Thanks.
November 21, 2012 at 2:58 pm
I don't think this is something you can do purely with SSRS.
There's no real way for SSRS to determine if the report is empty or not without running and there is no feature to check the report once it's run before sending.
I have actually looked into doing something similar and the best solution I could think of was to:
1. Create a timed subscription that is only run once. Get the SubscriptionID.
2. Create a SQL job that verifies if the report query will return data, if so, it will run EXECReportServer.dbo.AddEvent@EventType = 'TimedSubscription', @EventData = @SubscriptionID . This stored proc on the ReportServer database triggers a subscription event which will make the report run and deliver.
This method is also useful if you want to create a event driven report instead of following a set schedule.
November 28, 2012 at 4:04 am
For things like this I usually 'convince' 😉 the user that it would be much better if they received a HTML report just containing the base data with no totaling etc.
You can then easily produce that from SQL and check that there is data present
An Alternative would be to schedule the report but only schedule it to run once a year.
Then within sql you can set up a 2nd job that checks if there is data and uses sp_start_job to run your scheduled report.
November 28, 2012 at 11:30 am
Sorry!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply