Some of us has a daily or weekly process that puts some data in the tables and has some reports that report on top of these data, part of the daily process in any business it schedule report subscriptions ...
Sometime you get empty reports if the data is missing or no data for that day and you need to filter and not to send these reports....
There's are many ways to do it...but the most easiest one is to schedule your report normally, SSRS will create a new SQL agent job with a GUID numbers and later, just edit this job, go to the steps and add your protection code.
The simplest code will check if therefore any rows or not, you can also get notified using DBMail if no data, instead of sending empty reports to your boss!!
You can alter this step, or add a step before... Up to you.
Well I'm lazy, i'll alter this one and add a simple code to check if the table has rows or not !!
You can also use your own query based on a date and time column that you have !! to check for new data that you'll report on.
Just be creative...
You can also use sp_send_dbmail and send yourself an e-mail if it's empty.
Hope that helps..
Sometime you get empty reports if the data is missing or no data for that day and you need to filter and not to send these reports....
There's are many ways to do it...but the most easiest one is to schedule your report normally, SSRS will create a new SQL agent job with a GUID numbers and later, just edit this job, go to the steps and add your protection code.
The simplest code will check if therefore any rows or not, you can also get notified using DBMail if no data, instead of sending empty reports to your boss!!
Well I'm lazy, i'll alter this one and add a simple code to check if the table has rows or not !!
You can also use your own query based on a date and time column that you have !! to check for new data that you'll report on.
If exists (select * from tableA where Date_Column = getdate() )
Begin
Exec ......
End
Else
Send yourself an E-mail....
Just be creative...
You can also use sp_send_dbmail and send yourself an e-mail if it's empty.
Hope that helps..
worked for me.
ReplyDeletewas the simplest way of achieving it, from all the solutions i found.
thanks for sharing
ps. instead of exist i used if( select count())>0
I’m sorry but why should I use this approach? To be honest I consider this a novice query. “ IF EXISTS( ) ” is the best approach in my opinion.
DeleteI’m sorry but why should I use this approach? To be honest I consider this a novice query. “ IF EXISTS( ) ” is the best approach in my opinion.
ReplyDeleteWorked for me too - thanks for posting!!
ReplyDelete8 years later and this helped me. Thanks!
ReplyDelete