Building a SSRS Report that sends emails
I’ve been tasked recently with building a SSRS report that displays the count of users that will receive a welcome email.
So this is what the report should do:
- Show how many users registered to the site and did not receive a welcome email.
- Have a button that will act as a trigger to send emails
Sounds simple. How hard can it be, right? Well, reports built with SQL Data tools have their restrictions and having a trigger on a textbox that actions a stored procedure is not possible.
The solution I came up with is building 3 reports instead of just one.
First report runs a query that brings back the user counts. Here’s an example that brings back the total email counts for a particular region
SELECT COUNT(EmailAddress) as TotalEmails
WHERE Region =
@Region is a parameter which can be dynamically populated in a drop down list and will get the value based on your selection.
The Send emails “button” is actually a text box with an action to GO TO REPORT.
(On click will send the user to another report)
You can specify to pass the parameter value (@Region) to the next report which must contain the same variable name (name is case sensitive).
This second report contains the same query as the first one. (same dataset)
This report acts as a safety measure and makes the user aware of the action he/she is about to take.
Continue to Send Emails is also a text box which will send you to the 3rd report. (pass the same parameter value)
Now comes the last report that will actually run a stored procedure that will insert email addresses in a table which is later on processed by a SQL Server scheduled Job.
The reason behind why I chose this approach is because when running a SSRS report it will automatically execute the queries in your datasets.
This means that the procedure that inserts the email addresses may be run accidentally and send out those emails.
(This can still happen if the 3rd report is ran directly without any security implemented in the SSRS report)
Finally, the Go back to the first screen text box does exactly what it says. It will redirect you to the first report without passing any parameter values to it.
If you have suggestions on how this could have been achieved differently, leave a comment below.