SQL Server Reporting Services (SSRS) Architecture Overview

As you can see in above diagram when you install SSRS it creates Report Server Databases in your SQL Server Instance. These databases are ReportServerDB and ReportServerTempDB which are used to store report configurations and other things including Caching, Session, etc. that improvise the overall performance. You have an option of installing other components like Report Manager and Report Server on the same machine where SQL Server instance is running or you can install them on a different server (typical enterprise setup). An important thing to note here is if you opt for latter you would end up paying for 2 or more SQL Server licenses.

As it turns out there are 3 distinguish components of SQL Server Reporting Services:

1) Report Server: It’s an overloaded term. Largely used to indicate a set of components that allow interaction with Report Server database. SSRS provides Web Services (.asmx) which allows LOB applications to interact with Report Server database directly (http://computername/ReportServer/reportservicexxxx.asmx where xxxx is version of SSRS). SSRS 2005 created virtual directories for Report Server & Report Manager (discussed next), but SSRS 2008 leverages the OS level HTTP listener making SSRS independent of IIS. This allows bundling of Report Server & Report Manager within a windows Service ReportingServicesService.exe. The name of this Windows Service is ReportServer. ReportingServicesService.exe functionality also includes report processing, scheduling (auto generated reports), subscriptions (mailers), etc.

2) Report Manager: An ASP.NET web based application (http://computername/Reports) that in turn interacts with Report Server Web Services. As the name indicates Report Manager allows you to manage reports in terms configuring security access, organizing them into folders (non of these folders map to physical directories but are stored as details in Report Server Database), subscribing to them, etc. One can also create reports (see next point as to how) and deploy them to Report Server Database using Report Manager. This is handy for some restricted user / production scenarios, though most developers prefer to do the deploy reports from BI studio. As discussed earlier with SSRS 2008 this component is bundled with ReportServer windows service.

3) Report Designer: There would be few guys in your team whom you may want to designate as Report Designers. Report Designers can design reports using VS.NET Business Intelligence projects (Report Server Project). Report Designers create data sources (normally a shared data source (.rds) that’s used across a set of reports), create the dataset (using queries / stored procedures on top of data source), define relevant report parameters (mapped to datasets for value retrieval via Report data window and can be passed on from .NET applications too), field formats (using properties window with pervasive VB expressions – e.g. formatting a textbox to display currency decimals) and create layouts (e.g. Grouping). Once they are done with designing their reports (.rdl files – described later) they can test (preview) them and publish them via Report Server (this is done by providing the Report Server URL in project properties and SSRS there creates a specific folder for your project). Once published these reports are available for end user consumption. Advance scenarios like interacting with Excel may require a third party product like OfficeWriter.

There are few other important aspects of SSRS which one should be familiar with.

Report Builder is a another tool which is targeted at business users who want to generate custom reports on fly. Report Builder is a ClickOnce application, intuitive and easy to use but doesn’t support all the options available with VS.NET. It’s also possible to install ReportBuilder as a standalone application.

Report Model is the base for report creation with Report Builder. It’s a simplified view of relational database targeted at business users for ad hoc report creation. Report models are created using BI Development Studio (Report Model Project – .smdl files). A report model is built on top of a Data Source View (.dsv) that defines a logical model based on one or more data sources. Models generated mainly consist of entities (relational tables), fields (attributes of a relational table) and roles (entity relationships – 1-1, 1-*, *-1). Models also contain other attributes like aggregate values that would help ease the reporting for end users. Post creation report model has to be deployed in similar way as reports. You can also use Report Model as a data source for generating reports via Report Designer. While it’s easy to deploy Report model from BI development studio, to deploy report model manually e.g. in production requires you to merge the .smdl and .dsv files.

RDL – this is another term you would run into while talking about SSRS. RDL stands for Report Definition language. This is an XML file which stores query information, data source information, etc. which are required to generate report. There is another type of report definition – RDLC (Report Definition Language Client-side) which don’t store any of above configurations. RDLC is a client side component (VS.NET Application Projects) to which you can pass data (e.g. via DataSet) coming from any of data sources. RDLC can be useful for scenarios like implementing custom pagination (SSRS 2005 pagination by default is client side pagination).

SSRS Security is primarily windows based. When a user accesses the Report Manager Application or ASMX Web Services he has to authenticate with a valid domain username / password. On successful login SSRS determines the role of the user (custom or built-in ones like Browser / Content Manager, etc.) and displays only those reports / folders to which user has access. There are few variations in the security implementation I have come across that don’t rely on Windows Authentication. Some projects tend to control role like content manager for pushing reports (.rdl files) to production with help of rs.exe. All users have an implicit role of a Browser and application layer security determines which reports the user should have access to. In case you want go ahead and roll out your custom authentication that flows security all the way down, SSRS allows you that too. If you are generating reports by connecting to remote data sources for accessing images, etc. you might have to configureUnattended Execution Account.

Deploying Reports to Production – This is mainly done in three ways. In restricted production environments rs.exe can be used. In others we can deploy the reports directly from Visual Studio or use Report Manager (discussed earlier). This normally requires to change your Data Sources and Report Server URL in Project properties. There is overwrite property for Data Sources which is normally set to false. This property helps in ensuring that you accidentally don’t overwrite production data sources during your deployment. We can deploy individual reports too in case we have a specific modification.

Integrating SSRS into your applications – I will focus mainly on ASP.NET here. We need a ReportViewer Control found in the toolbox. Drag it and drop it to your ASPX page and in the background it would add reference to – Microsoft.ReportViewer.WebForms DLL. You may need to bundle this DLL with your application package, as mostly in production, Web Server and Report Server would be on different machines. Below is typical markup found in .ASPX page (I have hardcoded report server url for simplicity)

<rsweb:ReportViewerID=”ReportViewer1″ …>
<ServerReportReportPath=”/TargetReportFolder/OpenTicketReport”ReportServerUrl=”http://ReportServerURL/ReportServer”/>
</rsweb:ReportViewer>

One can also pass any necessary parameters as below in the codebehind file

this.ReportViewer1.ServerReport.SetParameters(Param);

In case your Web Server and Report Server are located on different machines, you need to ensure that the Worker Process running the application on web server has access to reports (you can configure the same using Report Manager Security)