Introduction
This article explains how to create data driven PDF in two approaches: SSRS 2008 and Reporting Viewer Control. I also explain and compare the basic similarities and differences of the two.
Please note, this article is about how to use SSRS or Report Viewer Control as back-end rendering engine. This article is NOT about how to use SSRS or RVC as UI control to show the real report. If you are interested in that topic, there is a very useful MSDN link comparing both features for you, it's here.
Background
Back in 2007, I wrote a CodeProject article "Create data driven PDF on the fly by using SQL server reporting service". It was for VS 2005 and SQL 2005. I originally thought of just revising that article for VS 2010 and SQL 2008, but there was too much content to fit in a revision, so I decide to create brand new demos and an article to explain them.
Live Demos
SSRS installation and configuration are not trivial efforts, so before you dive into the code, check the live demo first:
- SSRS PDF approach (Note: The first click might spin for 15 secs to give you result, after that, it's going to be seconds.)
- Report Viewer Control PDF approach (Note: The first click might spin for 15 sec to give you result, after that, it's going to be seconds.)
Using the Code
ReportViewer2Pdf.zip is for Report Viewer Control approach. You need VS 2010 + .NET 4.0 to run it. In addition, you need Report Viewer Control 2010 installed locally on your development PC (when you deploy the solution to server, you need to have RVC installed on the server as well).
The Microsoft link to install RVC 2010 is here.
======================
Now let's talk about the SSRS approach:
Ssrs2Pdf.zip is a web application solution for SSRS approach. You need VS 2010 + .NET 4.0 to open it. Plus, you need to have SSRS 2008 installed and configured properly on your development PC or Server because the solution needs to have a SSRS web service reference. How to setup SSRS 2008 is not covered in this article, but you can find out details from MSDN, here.
Ssrs2Pdf_RDL.zip is the solution to create RDL (report skeleton for Ssrs2Pdf web application). You need VS 2008 + Business Intelligence Studio template to open it. Business Intelligence Studio Template is installed when you installed your SQL server 2008 locally (any edition will do). Note: You still need SSRS 2008 to host the deployment of this RDL.
I named the RDL I named it as "Rpt01.rdl", and I deployed to my local SSRS folder "ssrs2pdf".
From the web application part, I also hard coded it as below:
Rse2005.ExecutionInfo ei = rsExec.LoadReport("/ssrs2pdf/Rpt01", historyID);
You can change it as you wanted. Just don't forget to change your SSRS setup as well.
The web service link in my example is like this in web.config:
<add key="ReportExecution2005.ReportExecution2005"
value="<a href="http://localhost/ReportServer/ReportExecution2005.asmx"/">
http://localhost/ReportServer/ReportExecution2005.asmx"/</a>
You can change it to adapt your web service URL.
For SSRS debugging, some tricks you have to know:
If you are using Windows 7 or Vista, make sure "localhost" is in your IE's intranet or trusted site list (configure it from IE\Internet Options\security\Local Intranet). In addition, launch IE and Visual Studio in “run as administrator” mode before you debug. Otherwise, you are likely to get "The permissions granted to user are insufficient for performing this operation” error.
Again, SSRS configuration is tedious, I suggest you try the live demo to feel it first.
Points of Interest
As for back-end report rendering, you might wonder what are the Pros and Cons of the two approaches, or when to use which. I had the same doubt before. After digging and testing for a while, and applied for several projects, I came across some personal thoughts about the question.
To explain it better, I compiled a FAQ list below to explain some basic terms and concepts about the SSRS and Report Viewer Control.
My FAQ List for SSRS and Report Viewer Control
======================================================
Claim: The following FAQs are just my personal understanding about the SSRS and Report Viewer Control. I can’t guarantee its technical right, so please take it as is. But one thing I can guarantee is that these understandings serve my projects very well. In addition, these FAQs are
far from a real introduction of the two software, because the topic is just too big to cover in this article. If you are really interested to know further, go to MSDN to find out more.
======================================================
1Q: What are the installation and configuration differences between the two?
A: SSRS is a much bigger software application. SSRS is part of SQL server since SQL 2005, the installation files are hundreds of MBs and take around half hour or even hours to install and configure properly. Report Viewer Control is much smaller, it’s either already part of your .NET 3.5 or 4.0 environment, or you can install it separately. You can download Report Viewer Control 2010 Redistributable Package from Microsoft (here), only 4.5 MB. The installation will put necessary DLLs to GAC (usually c:\windows\assembly). You could install this RVC package on your local developer PC or production server in about 5 minutes.
2Q: Can SSRS be installed stand alone (without SQL database in one physical box)?
A: Yes. The SSRS installation doesn’t need to have SQL Database in one box, but SSRS still needs to “point” or “link” to an existing SQL server database instance. Because all the configurations and reports definitions are eventually saved in the database, the configuration
database for SSRS by default named as “ReportServer” and “ReportServerTempDB”.
This is very good for multitier enterprise solution (data is at the safest place, Report engine and/or report definitions & web pages are at web server).
3Q: Does SSRS need IIS?
A: In SQL 2005, it does need IIS to be together in one box because it needs IIS to host its web service interface and web portal interface (although you can hide both UIs from the end users). In SQL 2008, SSRS has its own embedded web server, and doesn’t need IIS anymore.
4Q: Does SSRS need Active Directory or Domain?
A: Not necessary. SSRS can be setup as totally independent server, no AD/Domain at all, although AD/Domain setup can bring it to normal enterprise security level.
5Q: License or cost of the two?
A: Mean and lean speaking, both solutions are free. well if you already bought SQL server standard and/or above licenses, SSRS is included already. If you are just using the free edition of SQL server, SQL server express edition, SSRS is licensed free. Report Viewer Control is always free to use and distribute.
6Q: What is RDL (Report Definition Language) or RDLC and how does it apply to two software?
A: RDL is a file extension, such as “rpt01.rdl”. It’s a pure text XML file. Basically, RDL is the skeleton, layout and structure of the report, data (from query against the SQL DB) is the meat. SSRS + RDL + Data will give you a rendered report either in HTML or other formats (PDF, WORD, Excel).
RDLC is pretty much the same as RDL (slight different, explained more at 8Q), “C” means “client” or something similar. RDLC usually is applied to Report Viewer Control, but still served the same purpose as RDL as a skeleton of the report. So Report Viewer Control + RDLC + Data will also render you a report. By the way, Report Viewer Control can also link to RDL on SSRS.
7Q: Where is RDL or RDLC physically sitting
A: RDL and RDLC are pure text XML files.
RDL can be deployed to SSRS server. “Deploy” is the process to put RDL into SSRS configuration databases which are “ReportServer” and “ReportServerTempDB” (see 2Q). Deployment can be done via Visual Studio or SSRS web management portal (see 9Q).
RDLC can be part of your ASP.NET web application or Windows Form application (either in file system or resource file).
8Q: What are the tools to create RDL or RDLC
A: RDL is created from Visual Studio Business Intelligence Project Template (sometimes, it is referred as Business Intelligence Studio or BI studio). As for PC development, you install Visual Studio first, then install SQL server (Any edition). The Business Intelligence template is in the SQL server installation package. This template is very sensitive to VS versions , which means, You can only use VS 2005 + SQL server 2005 BI studio to create RDL for SSRS 2005, and use VS 2008 + SQL server 2008 BI studio to create RDL for SSRS 2008. No VS 2010 template at the moment.
RDLC is much easier, because you can directly create it from normal Visual Studio project (either win form or web application).
9Q: What are the URLs for SSRS web management and SSRS web service?
A: SSRS web management portal is for admin works (such as setup data source, online folders, security, adjust reports parameters, schedules, etc.). Usually the local installation URL is like: http://localhost/Reports. You can open it and do most SSRS admin works.
SSRS web service is for rendering reports during the run time. SSRS web service URL is like: http://localhost/Reportserver/ReportExecution2005.asmx, even in SSRS 2008, you will still have to use ReportExecution2005.asmx as the report rendering web service.
When you deploy RDL from VS 2008 BI studio (i.e. Business Intelligence Project Template), you setup the deploy folder to http://localhost/Reportserver/<your report folder>.
10Q: How does SSRS or Report Viewer Control create PDF under the hood?
A: SSRS creates PDF or other format of the report through ReportExecution2005.asmx web service.
You can do the same in your code. By calling functions in ReportExecution2005.asmx with proper parameters, you trigger the SSRS process to render certain report for you. Actually SSRS process will run in a separate process than your main code, either in the same machine or other physical server wherever it’s installed.
Report Viewer Control on the other hand is in the same process as your main code, either it would be your ASP.NET process or your WinForm main process. Of course, you can make it fancy to spawn it into other thread, it’s all up to you.
11Q: What are the IDE tricks when using SSRS approach to develop report
A: Due to UAC and IIS 7.5’s new structure, in Windows 7 or Vista environment, you’d better start IE “run as administrator” to get it open your local PC SSRS web management and web service URL. Even further, you might have to make sure your “localhost” is in your IE’s
intranet sites list (IE\Internet Options\security\Local Intranet).
In addition, before you debug your web service calling code from Visual Studio, you’d better start VS in “run as administrator” mode. Otherwise, you will always get error like “The permissions granted to user are insufficient for performing this operation”.
Report viewer control doesn’t need this trick if it’s not connecting to SSRS.
12Q: When to use SSRS and when to use Report Viewer Control to render PDF
A: It depends (as usual).;-)
Reporting Viewer Control + RDLC is simple and easy to program with. Especially if you are a one-man-show for a reporting project, this is the way to go. Reporting Viewer Control can open either RDLC saved in the file system or RDL saved in SSRS database. However, you have to deal with databinding and user access control from your code.
On the other hand, SSRS is much more complicated: you need multitier configuration/setup, believe me, it’s not trivial work.
However, SSRS makes sense if your company needs to host many (tens or hundreds) RDLs as report templates for different departments, usually a DBA or even a dedicated team is assigned to maintain all the enterprise level RDLs. The DBA or team will take care of security access, data source connections and even delivery or scheduled rendering parts.
Programmer at this point just needs to call the SSRS web service (by passing the proper parameters and security tokens) to render the reports based on RDLs. This can be either midnight job rendering thousands data driven PDFs or individual user click the button rendering just one PDF for him on the fly.
Both solutions can render thousands PDFs on the fly no problem. So it really depends on your infrastructure and project needs to choose either one approach.
History
- 25th February, 2011: Initial post
