Solution - scheduling and emailing a report only if data exists

I have found a solution that I would like to share. I needed the ability to send a report on a recurring basis to certain salespeople only if there was data to send. The best way to solve this issue was to first create a custom error within SQLServer using the sp_addmessage stored procedure. I used an error number of 60001.
Then I created a Query object using the 'if exists' command with a subquery of the data that you want to find in the database. If it does exist then select the data. If it does not then use the Raiserror command and raise the new 60001 error. So, you can schedule this query object to run every night and depending on whether there is data or not it will either complete successfully or it will fail.
Now there is one other thing you have to do. That is to go into Info Administrator and create a new event. When you schedule the query object you go to the Notify tab and click on the specify button under the 'On Success' side and click on the Event and Set. Do the same thing on the On Fail side but click on the Event and Reset. THen schedule the job. Then you can choose a report object and under the When tab choose Program/Event, then click on this same Event and click Add. This will ensure that the report will only run when the Event is set...

Regards,
Bessebo