Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.68/5 (13 votes)
17 Jun 20074 min read 231.7K   3.8K   103   30
A free, mean and lean way to create data driven PDF on the fly by using SQL server reporting service (SSRS)

Screenshot - pdf_webapp.jpg

Screenshot - pdf_result1.jpg

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.

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:

  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:

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer
Canada Canada
Yet another jack-of-all-trades programmer?


Comments and Discussions

 
QuestionCan you please consider rewriting this for newer SQL Server versions & .Net 4.0+ ? Pin
Fresh Mexican Food Fan30-Dec-13 8:58
Fresh Mexican Food Fan30-Dec-13 8:58 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey16-Apr-12 19:19
professionalManoj Kumar Choubey16-Apr-12 19:19 
AnswerRe: Passing the report a parameter. Pin
simonp_ca5-Aug-11 9:23
simonp_ca5-Aug-11 9:23 
GeneralMy vote of 5 Pin
Twisted Highway12-Apr-11 5:52
Twisted Highway12-Apr-11 5:52 
GeneralPDF Report from SQL query - explain pagination Pin
Blogdar21-Aug-10 4:49
professionalBlogdar21-Aug-10 4:49 
GeneralError In PDF Pin
Member 434380930-Jun-10 0:00
Member 434380930-Jun-10 0:00 
GeneralFilter Data in Gridview Pin
S Banik24-Oct-09 2:42
S Banik24-Oct-09 2:42 
GeneralCapture PDF file to email Pin
Member 13834205-Oct-09 4:12
Member 13834205-Oct-09 4:12 
Is it possible to not just render the PDF file, but capture it somehow so it can be emailed?

I need to be able to allow a user to indicate they want to release a batch of invoices, and get the parameters from an SQL table, then email to proper supplier.

I tried using a shared schedule to do this, but i need the user to indicate when invoices are available to be sent, and the user is not admin, so they did not have access to shared schedule.
GeneralI am getting error for this Error is System.Web.Services.Protocols.SoapException: The item '/pdf01' cannot be found. Pin
ramashwin29-Sep-09 16:33
ramashwin29-Sep-09 16:33 
GeneralGreat Pin
ridha touir11-May-09 6:31
ridha touir11-May-09 6:31 
Generalredirect to another page after viewing pdf output Pin
tammi.harris23-Oct-07 8:17
tammi.harris23-Oct-07 8:17 
GeneralRe: redirect to another page after viewing pdf output Pin
simonp_ca28-Oct-07 8:41
simonp_ca28-Oct-07 8:41 
GeneralRe: redirect to another page after viewing pdf output Pin
tammi.harris29-Oct-07 3:38
tammi.harris29-Oct-07 3:38 
QuestionServer Setup Pin
RyanRyanRyanRyanRyanRyanRyan22-Aug-07 12:52
RyanRyanRyanRyanRyanRyanRyan22-Aug-07 12:52 
AnswerRe: Server Setup Pin
simonp_ca22-Aug-07 15:23
simonp_ca22-Aug-07 15:23 
GeneralRe: Server Setup Pin
R__y___a_n11-Sep-07 4:59
R__y___a_n11-Sep-07 4:59 
GeneralRe: Server Setup Pin
simonp_ca11-Sep-07 8:34
simonp_ca11-Sep-07 8:34 
QuestionError thrown in online demo - http://www5.rptea.com/rspdf/ Pin
wiegelman10-Jul-07 5:49
wiegelman10-Jul-07 5:49 
AnswerRe: Error thrown in online demo - http://www5.rptea.com/rspdf/ Pin
simonp_ca10-Jul-07 11:11
simonp_ca10-Jul-07 11:11 
QuestionInvalid Path Pin
Welshlady9-Jul-07 1:43
Welshlady9-Jul-07 1:43 
AnswerRe: Invalid Path Pin
simonp_ca9-Jul-07 2:44
simonp_ca9-Jul-07 2:44 
QuestionNice... Pin
Namshub28-Jun-07 22:29
Namshub28-Jun-07 22:29 
AnswerRe: Nice... [modified] Pin
simonp_ca30-Jun-07 4:26
simonp_ca30-Jun-07 4:26 
Generalerror occurs 'file is damaged' Pin
vikas chaudhary24-Jun-07 18:07
vikas chaudhary24-Jun-07 18:07 
AnswerRe: error occurs 'file is damaged' Pin
simonp_ca25-Jun-07 2:58
simonp_ca25-Jun-07 2:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.