36 Chambers – The Legendary Journeys: Execution to the max!

June 3, 2011

Reporting Services 2008: Creating Reports Programmatically Without Using A ReportViewer

Filed under: Database Administration, Programming & Work — Kevin Feasel @ 6:36 pm

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

About these ads

8 Comments »

  1. [...] In yesterday’s post, I described how to use the SQL Server Reporting Services 2008 web service….  After doing that, I realized that I would want to choose a different reporting server depending upon my environment, so I needed to find a way to make it more configurable.  The problem is that the web service edits the applicationSettings section of your app.config instead of the appSettings.  ApplicationSettings was added in .NET 2.0 to improve on some of the limitations of appSettings, but in doing that, it changed the way you interact with configurations.  So I went a-searching for how to do this. [...]

    Pingback by Setting ApplicationSettings Values At Runtime « 36 Chambers – The Legendary Journeys: Execution to the max! — June 4, 2011 @ 7:19 pm

  2. In this example – and confirmed from my findings – this makes three separate web service calls to the RS server. In a high volume scenario, say month-end reporting – I would like to reduce the number of calls down to one – is this possible???

    Comment by swhatmore — October 11, 2011 @ 3:45 pm

    • Unfortunately, I don’t know how to do that or even if it is possible. Reporting Services, in my experience, is a very inefficient tool. When deploying or running reports, it makes a lot of calls, and I think that’s by design.

      Comment by Kevin Feasel — October 16, 2011 @ 2:06 pm

  3. Kevin, thanks for show us these possibilities. I implemented the option using webservices and its works well.

    Comment by Alexandre Fonseca — December 14, 2011 @ 12:45 pm

  4. Amazing . You made my day.

    Comment by Vishwanatha — September 11, 2012 @ 2:56 am

  5. it keep promp me soapException was unhandled error from this line
    –rs.LoadReport(reportPath, historyID)
    what to do with the historyID
    i need you help..

    Comment by egfehs — November 14, 2012 @ 3:12 am

  6. Thank you for this posting. It has provided me with a perfect solution where other possible ideas on the net seem overly complex.

    Comment by David Wakeford — February 14, 2013 @ 12:37 pm

  7. Hi Kevin,
    I am working on a similar requirement of executing SSRS report using Autosys Scheduler. Can you please let me know how did u configured the jil file for your project. I am looking for how to chk whether the report is executed sucessfully and desired output file is created so that the autosys job can be flaged sucess or failure.I am a little bit confused and stuck up here on how to chk the status of report execution?
    I was able to execute and export report from C# program ,only part remaining is the integration with Scheduler Application .

    Any help on this is much appreciated.

    Thanks in advance,
    Hemesh

    Comment by Hemesh Thankappan — September 4, 2013 @ 6:17 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 92 other followers

%d bloggers like this: