May 18, 2009 at 8:11 am
I currently have a report setup and a subscription that email the report to some users. Is It possible that if the report returns no records to not have the report sent?
Thanks
May 18, 2009 at 8:59 am
You can use data-driven subscription or SSIS
For the SSIS method:
create a subscription to save the report to file
create an SSIS package which can test if the report is populated then email the saved file if conditions are met.
May 18, 2009 at 9:05 am
Thanks. Do you know if this is implemented in SSRS 2008?
June 1, 2009 at 9:12 am
Data-driven subs have been in SSRS for a while. The tricky part is that they aren't available in Standard edition, just Enterprise or Developer. As it turns out many people find this a useful technology, so the natural MS response was to leave it out of Standard to encourage upgrades. (I apologise for my cynicism, but it's Monday. I hope to be better by mid-week.)
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
June 2, 2009 at 5:15 am
I've come across this too, and guess which version I have!!! Standard!!! Grrr....
I did manage to get a bit of a cheat for this though. It will require you to find out the name of the subscription that appears in SQL Server agent jobs (subscriptions translate to sql agent jobs with unique yet unfriendly names). The method of doing this can be found in this very useful link (http://www.sqldisco.com/?p=51).
Once you've got this name, you can set up another agent job that you can enter a step using the IF EXISTS sql command (see the above link) to see if it will return any rows and, if so execute the subscription name using a command like this
EXEC Report$Server.dbo.AddEvent @EventType='TimedSubscription',
@EventData='49E98A70-2130-4900-9F82-A5CEBE658808'
June 2, 2009 at 7:56 am
That is very cool.
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
June 2, 2009 at 9:46 am
I have also used a method similar to Griffster, but instead of adding the AddEvent I just use sp_startjob to run the Report Subscription job. I do not have a schedule for the Report Subscription, I just use the schedule on the job that tests for data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2009 at 6:57 am
You can also abort a report (and therefore not send the email) by calling RAISERROR in one of your "Report DataSets". Your error text will appear in the SRSS log.
if (@shares<=10000)
begin
RAISERROR ('Aborting report because not enough shares were found.', 16, 1)
end
June 3, 2009 at 7:04 am
Paul,
I really like this idea of handling it with an "error" in the dataset. It's much simpler than manipulating the subscriptions jobs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2009 at 7:45 am
You might be able to use this with some modifications for SSRS...
August 10, 2009 at 3:43 pm
Modifying the SQL Agent job to check for rows would be a challenge since my datasource for my reports is not the same server as my SSRS database. It would make it very difficult to manage and I have a lot of reports I'd like to do this with. 🙁
I kind of like the error raising idea which would more easily solve the emailing issue but it makes for a nasty implementation when manually running the report.
Any other ideas?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply