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:

End user triggers report through SSRS built-in UI (the report viewer)
End user triggers report through a pre-built URL. This URL will trigger the SSRS to render report
SSRS scheduled job will render reports on schedule
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:

Scheduled reporting
SSRS needs SQL server job scheduler to do scheduled reporting, current express edition doesn't have this function.
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:

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

Screenshot - pdf_ssrscfg.jpg

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:

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.

Use the following code before the SSRS rendering:

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

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