This was a new one to me! A user subscribed to an SSRS Report but only wanted to receive the email if the report contained data.
There seems to be loads of write-ups on the web about how to set this up. Here is how it went for me …
Firstly, I created the stored-procedure that would return rows from a live table that contained (business logic) errors (EG: “rep_exceptions”).
I created the report and subscribed to it (making sure to paste in my actual email address, not my login).
In the subscription form I specified both a start-date and an end-date that were in the past (ensuring the subscription would never actually fire, and ‘OK’ed the form.
Within SSMS “Job Activity Monitor” I located the job that had been created by this subscription (I looked for jobs that had never run, and then matched the start / end dates in the schedule with those I used in the subscription-form)
I copied the action statement from the job-step eg:
EXECUTE [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='5ec60080-b30c-4cfc-8571-19c4ba59aaac'
… into a new job. Then augmented it, to only run if there was data …
EXECUTE [ServerName].[dbo].[rep_exceptions] if @@ROWCOUNT > 0 EXECUTE [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='5ec60080-b30c-4cfc-8571-19c4ba59aaac'
I scheduled the new-job to run every 8am.
Once this was all tested and working – I changed the SSRS subscription email address to the user (and left me as CC temporarily).