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).
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.
Let’s dive into the first part of the PowerShell script:
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.
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.
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:
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).