SQL Server Reporting Services (SSRS) is a great reporting tool, especially its ability to schedule reports to be sent out via e-mail. But one thing nobody wants is useless e-mails flooding their inbox. Some of the reports I’ve built using SSRS list problem data that needs to be cleaned up. I don’t want those reports getting sent out every day, even when they’re empty. I’m going to show you the method I used to get SSRS to only send a report under the correct circumstances.
Note: This method is not officially supported by Microsoft and involves directly modifying SQL Server Agent jobs that are created by SSRS. Also, if your version of SSRS supports data-driven subscriptions there’s a better way to do this, which you can read about here.
1. Schedule the report
First, create and schedule the report. SSRS creates a SQL Server Agent job to run the scheduled report, and you are going to modify that job.
2. Find the job id
SSRS uses a GUID as the name of the SQL Server Agent job it creates for each scheduled report. You need to identify which job is the one you need.
To do this, connect to the server hosting the SSRS database in SQL Management Studio. The default database name for SSRS is ReportServer. The following query will return a list of all scheduled reports by name.