Emailing SSRS reports on a scheduled basis, without using subscriptions (or attachments)

In the Microsft BI world, SSRS is a very much integrated product. We can find SSRS reports in many companies. A question that we encounter often as BI consultants is to render some of these SSRS reports on a scheduled basis and save them in a shared location or email them as a pdf file. Subscriptions can do this for you. However, with subscriptions, we are missing some flexibility, like dynamic report names or sending the rendered report only when certain conditions are met.

In this blog we want to get the most out of it and email a HTML version of the report when the report contains records. This also means we will not be leaning on attachments and that can be more practical in some cases (i.e. reading your email on a mobile device).

Approach

As we do not want to use subscriptions to automate report rendering, we will use PowerShell to launch `rs.exe` and feed it the needed parameters. The `rs.exe` executable will use a rss file (a script file written in VB.Net) that allows us to script various SSRS taks, like rendering a report. Then we will use PowerShell to create the email and use the generated HTML as the content for the email. In one of the last steps we will use regular expressions to clean up some of the generated HTML.

PowerShell

Let’s dive into the first part of the PowerShell script:

• The first variable ($rsexe) holds the location of rs.exe. This executable will render the report. You will find it on the report server.
• The second variable ($rss) points to the location of the rss file. This is a script with VB.Net code (no other choice here). The rss file contains the code to render the report and export it as a HTML file. It must be saved with unicode or UTF-8 encoding.
• The variable $srv is the http location of the report server. Most of the time this ends with reportserver_ or just reportserver if you use the default instance.
• $outputfile is the location where we save the output of the rendered HTML. We will use it afterwards as the source for our email. Variables defined afther the `-v` are optional variables that can be used in the rss script.
• The last line is the command to start processing/rendering the report. The last parameter `-e Exec2005` is making it possible to process and render the reports (Mgmt2005 is the default value, so you need to override it like this).

RSS Code

We’ll be adding some code to the PowerShell script afterwards but will now focus on the VB.Net code, the rss file, in order to continue in a chronological order:

The render type is set to HTML4.0, as we want to render it as html. This is going hand in hand with the MIME type, that needs to be set to text/html.

Before we can render the report it needs to be loaded. This is done using the LoadReport method. The first parameter of the LoadReport method holds the path to the report. In this example we have a SSRS folder named AutoRender that contains the report named HR_Employees. Use your own path.

The rendering returns a rendered object, it is not automatically saved to an output file. Therefore a FileStream is used that will eventually give us the html file.

Sending the email

As I assume this will most likely be used within a company, I also assume that an open SMTP is within the possibilities. If not, you will need to extend this code with your credentials or automate the Outlook client (this is very well possible with PowerShell, but it would lead us too far from our course).

Like this, you will have send an HTML email with your report as its content.

Cleaning up your HTML

Now that we know how to capture the HTML and send it in an email, we should pay attention to some fine-tuning (read: cleansing). One example is pictures that are used in the report. For example, when you enable interactive sorting, a pictures is added in the header cell. In this particular case we will remove that picture as it has no function in the email and will appear as a red cross (telling you that the image was not found). Your pattern might look different, it all depends on the specific need.

Some other cleansing:

Automating the report

Using the Task Scheduler, we can render the report automatically. In your Action settings, it is important that you use the full path of the PowerShell executable and the full path of the ps1 file (your PowerShell script file will probably have that extension).

• The full path to the Powershell executable will look like this: %WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe
• Full path to the PowerShell script file, starting with an ampersand (&) and a space, followed by the path within single quotes: & ‘C:\YourPath\render.ps1’

Some further remarks

• In our test case we also experimented by only sending an email when the report contains records. You can achieve this with a little trick. Just add a textbox to the report header or footer. Make it display a very specific text when your dataset contains no records. You can achieve this with a simple expression like the following: To make it invisible, you give the text the same colour as the background of the page. In the PowerShell script you then check if that specific text appears in the rendered html, and only when it does you send the email.
• The rendered html does contain some flaws. For example, when the report contains page splits, we noticed multiple html closing tags. Other issues are not known at the moment, but if they exist, some PowerShell find and replace commands might come in handy.
• When there are many pictures in the report, it might be better to render the report as a pdf file.