serving the solutions day and night


Wednesday, February 8, 2012

SQL Server Reporting Service (SSRS)

SQL Server Reporting Service (SSRS)
  1. SSRS is a bigger software application. SSRS is part of SQL server since SQL SERVER 2005. 
  2. The SSRS installation doesn’t need to have SQL Database in one box, but SSRS still needs to connects to an existing SQL server database instance. 
  3. All the SSRS configurations and reports definitions are eventually saved in the database, the configuration database for SSRS by default named as “ReportServer” and “ReportServerTempDB”. 
  4. Good practice to keep SSRS Report engine / Report definitions & web pages are at web server, i.e., n-tier enterprise solution.
  5. In SQL Server 2005, SSRS does need IIS to be together in one box, because it needs IIS to host its web service / portal interface. 
  6. In SQL 2008, SSRS has its own embedded web server, and doesn’t need IIS anymore.
  7. 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.
SSRS web management portal is for admin works (such as setup data source, online folders, security, adjust reports parameters, schedules etc.).  More info SSRS Web Management Portal - User Roles and Permissions

SSRS web service is for rendering reports during the run time.
When you deploy RDL from VS 2008/2010 BI stuidio (i.e. Business Intelligence Projects Template), you setup the deploy folder to http://localhost/Reportserver/<your report folder>.
More info Deploying a SSRS report to the Server

Report Viewer Control is a smaller, it’s either part of  .NET 3.5 or 4.0 environment, or you can install it separately (put necessary DLLs to GAC  -> c:\windows\assembly).
Download Report Viewer Control 2010 Redistributable Package from Microsoft.

Report Definition Language (RDL) 
  1. RDL is a file extension, such as “Report1.rdl”.  It’s a pure text XML file. 
  2. RDL can be deployed to SSRS server. “Deploy” is the process to put RDL into SSRS configuration databases which are “ReportServer” and “ReportServerTempDB”.
  3. Deployment can be done via Visual Studio or SSRS web management portal
  4. RDL is the skeleton, layout and structure of the report, data (from query against the SQL DB) is the meat. 
  5. SSRS + RDL + Data will give you a rendered report either in HTML or other formats (PDF, WORD, Excel).
  6. RDL is created from VS Business Intelligence Projects Template.
Report Definition Language Client (RDLC) 
  1. RDLC is pretty much the same as RDL , more info from microsoft link.  
  2. RDLC usually is applied to Report Viewer Control, but still served the same purpose as RDL as a skeleton of the report. 
  3. Report Viewer Control + RDLC + Data will also render you a report. 
  4. Report Viewer Control can also link to RDL on SSRS.
  5. RDLC can be part of your ASP.NET web application or windows form application (either in file system or resource file).
  6. RDLC is much easier, you can directly create it from normal VS project (win / web application), i.e., reference to the Report Viewer Control library.