If you only want to send an email or save a report in SQL Server Reporting Services (SSRS) if there are results for your data query, here’s how I did it.
Are there any results?
The majority of the work is actually done in the SQL for the data driven subscription
Since I had to run the base query to see if there were any results, I decided to run the base query in the data driven subscription, store the results of the base query in a table, and then query that table for the actual report.
When creating a data driven subscription, one is prompted, on step 3 of the process, to “Specify a command or query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient.” What we really need to do is return one row per recipient, because the list of recipients can be statically assigned in step 4.
Since I only wanted to send an alert if there was data, what I’m going to query for instead of a list of recipients, is the number of rows in my data, although an email address and other parameters could be returned also.
Before I can do that, I have to truncate and populate a table with the results of my base query.
I’m using Amazon Redshift, and the SQL query that I will use to do that is:
TRUNCATE my_table; INSERT INTO my_table SELECT [my base query];
After that, I’ll add my query that returns the number of rows in my data, but only return the number of rows if there are rows. While I’m at it, I’ll create a dynamic subject line indicating the number of rows. Basically you just need a subquery to dynamically return results based on the number of rows.
SELECT my_count || ' new alerts' AS my_subject FROM (SELECT COUNT(*) AS my_count FROM my_table) WHERE my_count > 0;
In my case, I wanted to send an email if there were greater than 0 rows in the results, but one could change the join specification (a.my_count > 0) to set a different threshold, for example, to only send an email if there were 10 rows or more (a.my_count >= 10).
On step 4 of the data driven subscription creation process, I specified a static “To” value, and used the my_subject value returned from the database query for the “Subject” value.
Generating the report
Now that I have the results in my_table, the report will be generated if there are more then one rows in the table. To generate the report, I simply needed to pull the results from the table using a dataset containing this SQL query in the report:
SELECT * FROM my_table;