Create data driven PDF on the fly by using SQL server reporting service (SSRS)

• Download RDL Template - 5.9 KB
• Download Web Application - 20.7 KB

Introduction

The example is a web application, accepting the user input text and creating a data driven PDF on the fly. The web application programmatically calls the SQL server reporting service (SSRS)'s web service to do the trick.

Background

Basically there are 4 approaches for the SSRS data driven report rendering:

1. End user triggers report through SSRS built-in UI (the report viewer)
2. End user triggers report through a pre-built URL. This URL will trigger the SSRS to render report
3. SSRS scheduled job will render reports on schedule
4. Programmer calls the SSRS web service to render report programmatically

This article uses approach #4. By calling the web service, the programmer gains the most control out of SSRS and can do more with less restrictions (if used properly).

You can use the same idea to create your own Excel spreadsheet or CSV files on the fly.

If you don't have SSRS development environment, I suggest you try my live demo first. To install the whole environment locally from scratch is not a trivial task.

This article assumes the reader already knows:

• how to install and use SSRS
• how to author and publish a SSRS report template (RDL file)
• how to use C# to call a web service

Free, mean and lean

If you just want a pure report engine for not-too-heavy-duty usage, you don't have to buy SQL server standard edition (means thousands of dollars in license fee). SQL server express edition is totally free and can do most SSRS functions.

Some limitations you need to keep in mind when you use SSRS express edition:

1. Scheduled reporting
   SSRS needs SQL server job scheduler to do scheduled reporting, current express edition doesn't have this function.
2. Scale to multiple CPUs
   At present, SQL server express edition can only utilize one CPU.

The example code was tested on a XP Pro SP2 PC and Windows 2003 server SP1 with SQL server express edition 2005.

Using the code

The source code was tested on a PC:

• Windows XP Pro SP2
• .NET 2.0 framework
• Visual Studio 2005
• Business Intelligence Template (for SSRS authoring)
• SQL server express edition 2005 with advanced services SP2 (download from Microsoft)
• IIS 5.1 (comes with XP pro)

(If you just want see how it goes, you can try my live demo here)

Before you test the example code, you need to make sure that your SSRS and web service is configured properly.

The above screenshot is the SSRS configuration screen. "green" indicator means proper configuration of the entry.

Now you can compile and deploy the two source projects on your local PC:

1. PdfReport.zip is the RDL template to author the sample PDF report
   • Deploy the report (pdf01.rdl) to your local PC (http://localhost/reportserver)
   • To make things easier, pdf01.rdl doesn't have any data source
   • To demo data-driven, I put one report parameter (user input) and five "real-time" yahoo stock charts (To make things more interesting, I used MSFT, SUNW, IBM, GOOG, ORCL)
2. RsPdf.zip is the web application calling the SSRS's web service to create real PDF from pdf01.rdl template. (In the example solution file, I already added the web reference WSDL file which refers to http://localhost/ReportServer/ReportExecution2005.asmx)

Security tips

You can see the code below right before the SSRS rendering:

rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

Because you are using local PC to do the test, I also assume that you as a programmer always login local PC as a local admin, so the default credentials are powerful enough to call the local SSRS web service.

In case you are deploying the same function to a public server like my live demo, you might want to do the following steps before your code can run properly.

1. Create a local user (e.g. user1234) in your server or you can also create an active directory domain user
2. Make sure this user is in SQLServer2005ReportingServicesWebServiceUser local user group (This user group is created when you install and configure SSRS)
3. Login to http://YourPublicServerURL/reports
4. Click "edit" button of your report entry (pdf01 in this example)
5. Add the newly created local user (or AD user) to the security tab of this report template (pdf01 in this example)
6. Use the following code before the SSRS rendering:
   C#

   rsExec.Credentials = new NetworkCredential
                       ("user1234", "password", "domain");

If user1234 is an AD user, "domain" is the AD domain; If it is just a local user, leave it as blank "".