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:

  1. Show how many users registered to the site and did not receive a welcome email.
  2. 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.

Report 1

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

FROM NewClients

WHERE Region =
@Region

@Region is a parameter which can be dynamically populated in a drop down list and will get the value based on your selection.

Report1

 

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).

Report 2

Report2

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)

 

Report 3

 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.

 

Report3

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

two + 8 =

Time limit is exhausted. Please reload the CAPTCHA.

Subscribe to our newsletter and stay up to date with:

  • Latest posts
  • News and Articles
  • Products, Promotions and Giveaways

Get our FREE step by step guide to Building SSRS reports 101

Hide me
Show me
Build an optin email list in WordPress [Free Software]