Thursday, March 24, 2011

Render SQL Server Reporting Services report as a PDF file

We have various reports developed using SQL Server Reporting Services. In some cases we want to provide our users with PDF versions of the reports without them having to navigate the Report Server website. For these purposes, it is convenient to produce a PDF file and send it by email or save it to a share.

I thought it would be easy, but it wasn't so. It took many hours to get a program working that would access our report server and save a report to disk as a PDF file. The following program worked:

Module Module1



    Sub Main()

        Dim format As String = "PDF"

        Dim fileName As String = "C:\temp\test.pdf"

        Dim reportPath As String = "/Project1/Report1"



        ' Prepare Render arguments



        Dim myCredentials As New NetworkCredential(userName:="USER", password:="password")



        Dim rs As ReportExecutionService = New ReportExecutionService()

        rs.Credentials = System.Net.CredentialCache.DefaultCredentials



        Dim historyID As String = Nothing

        Dim deviceInfo As String = Nothing

        Dim extension As String = Nothing

        Dim encoding As String = Nothing

        Dim mimeType As String = Nothing

        Dim warnings() As Warning = Nothing

        Dim streamIDs() As String = Nothing

        Dim results() As Byte





        rs.LoadReport(reportPath, historyID)



        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



End Module


I compiled this program using Visual Studio 2010 Express edition. The report server is part of SQL Server 2008 Enterprise Edition.

Within Visual Studio I had to create a reference to a web resource. This is a bit tricky... I don't have VS in front of me now, but I opened the project settings, selected resources (or something like that) and drilled down through advanced settings until, way at the bottom of one of the pop-up screens, there was an "Add Web Resource" button (or something like that). There I entered the name of our report server. Something like:

http://server/reportserver/reportexecution2005.asmx?wsdl
I don't know if the "?wsdl" is necessary. Some posts said it was and this is the URL I was using when I finally got it working. More to test, if I wasn't already many hours behind schedule.

This (reportexecution2005.asmx) works despite the server being SQL Server 2008 and Visual Studio being 2010. All the sample VB code I could find used something called "rs" without declaring or initializing it. None of those posts said anything about where it came from or what versions of tools the code worked with. I stumbled upon ReportExecutionService() quite by accident, and cobbled together working declaration and initialization by trial and error and by following a few hints here and there on the Internet.

There is lots left to learn and understand, but in the mean time, the above is working for me.

2 comments:

Merpy said...

Do you know if it's possible to have additional reports appended to the same PDF?

Ian said...

I don't know if that is possible.

We often create a "single report" (from SSRS perspective) to contain multiple reports (from the user's perspective) by using a list as the basic element of the report, with one list row per customer-perspective report. This is easy because the cells in the list can contain not just single fields but fairly arbitrary content.

If you have an existing report, you might try using it as a subreport, but I haven't done this myself.

Labels