Reporting Services 2008: Creating Reports Programmatically Without Using A ReportViewer

Job problem:  I need regularly to generate a series of PDFs of invoices and then do some processing of these PDFs.

Prospective solution:  use SQL Server Reporting Services 2008 to generate the PDFs.  Then, use a console application to tell SSRS which invoices to create and do the subsequent processing.  This console application would run via the Task Scheduler.  All of these are technologies which our IT staff know, so maintenance by somebody other than me wouldn’t be a big hassle.

There are a few ways to run Reporting Services to generate reports without needing to connect directly to the report server through your web browser.

The ReportViewer Control

The easiest method is definitely the ReportViewer control.  It works well with WinForms and WebForms applications, but unfortunately, I wanted to use a scheduled console application and have no human intervention.  That left this one out in the cold, although we do use it in web applications.

RS.exe

The second method is to run RS.exe.  This is a utility which allows you to run VB files (with an RSS extension) against a Reporting Services installation.  This can then be called from PowerShell or  a console application.  Roman Rehak has a great post on this and you should read that.  The command is:

rs.exe -i Test.rss -s http://[server]/reportserver -e Exec2005

Note that the “-e Exec2005” part is necessary for a Reporting Services 2008 installation.  Your RSS file would be pretty simple.  The one I used for testing was:

Public Sub Main()

Dim format as string = "PDF"
Dim fileName as String = "C:\RS\Output.pdf"
Dim reportPath as String = "/[Report Folder]/Invoice"

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim extension as string = Nothing
Dim encoding as string
Dim mimeType as string = "application/pdf"
Dim warnings() AS Warning = Nothing
Dim streamIDs() as string = Nothing
Dim results() as Byte

rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.LoadReport(reportPath, historyID)

Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "InvoiceNumber"
parameters(0).Value = "15"

rs.SetExecutionParameters(parameters, "en-us")

results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub

That file works.  What I’m doing is passing in a single parameter—InvoiceNumber—and a value associated with that parameter.  My report was already set up to generate a report based off of that one parameter.  The only other place that would need to be changed is the report location, where you would need to go to the correct folder and give the right name.  The name of my report was “Invoice.”

The downside to using RS.exe is that you need to script out files for each one, and then call this executable for each script.  It works, and I could have a console application call it so many times, but honestly, I was looking for something a bit more elegant.

Using The Web Service

The method I ended up going with was writing code to connect to the Reporting Services web service directly.  To do this in Visual Studio 2010, the first step is to add a Web Reference:  Right-Click References –> Add Service Reference –> Click Advanced Button –> Click Add Web Reference Button.

Once you get to the web reference page, you’ll be prompted for a WSDL address.  Type in http://%5BYour Report Server]/reportserver/reportexecution2005.asmx?wsdl.  Note that you want to go to reportexecution2005 and NOT reportservice2005 for this.  The reportservice2005.asmx service is for administrative functionality and the reportexecution2005.asmx service is for running reports.  This changed a bit from SSRS 2005, where both sets of services used the same address, so some stuff floating around on the internet doesn’t really apply to SSRS 2008.  Anyhow, after selecting the report execution service, you should give it a name.  I called mine ReportingServicesReference.  After you add the reference, Visual Studio will update your app.config to add some new code to connect to the server.

What you want to do next is figure out how to render your report.  MSDN has some code and Will Beattie has some code.  In addition, you can read the RSS file above and get a general idea of how Reporting Services methods operate.  There are some minor changes between the RS.exe and web service approaches—the biggest one is that the Render method does not include a parameter list in the web service option, whereas it does in RS.exe.

Here is sample code which you can use to render the same reports:

using [your namespace].ReportingServicesReference;

private static void CreateInvoicePDF(int InvoiceId, string fileName)
        {
            string FullFilePath = ConfigurationData.PDFLocation + fileName;

            ReportExecutionService rs = new ReportExecutionService();

            string format = "PDF";
            string reportPath = "/[Your Report Folder]/Invoice";
            string mimeType = "application/pdf";

            ParameterValue[] parameters = new ParameterValue[1];
            parameters[0] = new ParameterValue();
            parameters[0].Name = "InvoiceNumber";
            parameters[0].Value = InvoiceId.ToString();

            byte[] results = RenderReport(rs, format, mimeType, reportPath, parameters);
            WriteFile(results, FullFilePath);
        }

private static byte[] RenderReport(ReportExecutionService rs, string format, string mimeType, string reportPath, ParameterValue[] parameters)
        {
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

            //Prepare Render arguments
            string historyID = null;
            string deviceInfo = String.Empty;
            string extension = String.Empty;
            string encoding = String.Empty;
            Warning[] warnings = null;
            string[] streamIDs = null;
            byte[] results = null;

            rs.LoadReport(reportPath, historyID);
            rs.SetExecutionParameters(parameters, "en-us");
            results = rs.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

            return results;
        }

private static void WriteFile(byte[] input, string FileName)
        {
            //Open a file stream and write out the report
            FileStream stream = File.OpenWrite(FileName);
            stream.Write(input, 0, input.Length);
            stream.Close();
        }

ConfigurationData up above is a helper class that I created to give me static typing for appConfig settings. Otherwise, this should be pretty straightforward code, whose end result is a PDF created in the directory and location of your choosing.