Overview of SQL Server Reporting Services 2012 Architecture, Features, and Tools

For a newcomer to SQL Reporting Services 2012, things can seem a bit daunting, but this book will ease your path to becoming an expert. Clear instructions and screenshots will have you generating reports in no time.

(For more resources related to this topic, see here.)

Structural design of SQL servers and SharePoint environment

Depending on the business and the resources available, the various servers may be located in distributed locations and the Web applications may also be run from Web servers in a farm and the same can be true for SharePoint servers. In this article, by the word architecture we mean the way by which the preceding elements are put together to work on a single computer. However, it is important to know that this is just one topology (an arrangement of constituent elements) and in general it can be lot more complicated spanning networks and reaching across boundaries.

The Report Server is the centerpiece of the Reporting Services installation. This installation can be deployed in two modes, namely, Native mode or SharePoint Integrated mode. Each mode has a separate engine and an extensible architecture. It consists of a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Once deployed in one mode it cannot be changed to the other. It is possible to have two servers each installed in a different mode.

We have installed all the necessary elements to explore the RS 2012 features, including Power View and Data Alerts. The next diagram briefly shows the structural design of the environment used in working with the article:

Primarily, SQL Server 2012 Enterprise Edition is used, for both Native mode as well as SharePoint Integrated mode. As we see in the previous diagram, Report Server Native mode is on a named instance HI (in some places another named instance Kailua is also used). This server has the Reporting Services databases ReportServer$HI and ReportServer$HITempDB. The associated Report Server handles Jobs, Security, and Shared Schedules. The Native mode architecture described in the next section is taken from the Microsoft documentation. The tools (SSDT, Report Builder, Report Server Configuration, and so on) connect to the Report Server. The associated SQL Server Agent takes care of the jobs such as subscriptions related to Native mode.

The SharePoint Server 2010 is a required element with which the Reporting Services add-in helps to create a Reporting Services Service. With the creation of the RS Service in SharePoint, three SQL Server 2012 databases (shown alongside in the diagram) are created in an instance with its Reporting Services installed in SharePoint Integrated mode. The SQL Server 2012 instance NJ is installed in this fashion. These databases are repositories for report content including those related to Power Views and Data Alerts.

The data sources(extension .rsds) used in creating Power View reports (extension.rdlx) are stored in the ReportingService_b67933dba1f14282bdf434479cbc8f8f database and the alerting related information is stored in the ReportingService_b67933dba1f14282bdf434479cbc8f8f_Alerting database. Not shown is an Express database that is used by the SharePoint Server for its content, administration, and so on. RS_ADD-IN allows you to create the service. You will use the Power Shell tool to create and manage the service.

In order to create Power View reports, the new feature in SSRS 2012, you start off creating a data source in SharePoint library. Because of the RS Service, you can enable Reporting Services features such as Report Builder; and associate BISM file extensions to support connecting to tabular models created in SSDT deployed to Analysis Services Server. When Reporting Services is installed in SharePoint Integrated mode, SharePoint Web parts will be available to users that allow them to connect to RS Native mode servers to work with reports on the servers from within SharePoint Site.

Native mode

The following schematic taken from Microsoft documentation (http://msdn.microsoft.com/en-us/library/ms157231.aspx) shows the major components of a Native mode installation:

The image shows clearly the several processors that are called into play before a report is displayed. The following are the elements of this processing:

  • Processing extensions(data, rendering, report processing, and authentication)
  • Designing tools(Report Builder, Report Designer)
  • Display devices(browsers)
  • Windows components that do the scheduling and delivery through extensions(Report Server databases, a SQL Server 2012 database, which store everything connected with reports)

For the Reporting Services 2012 enabled in Native mode for this article, the following image shows the ReportServer databases and the Reporting Services Server. A similar server HI was also installed after a malware attack. The Report Server is implemented as a Microsoft Windows service called Report Server Service.

SharePoint Integrated mode

In SharePoint mode, a Report Server must run within a SharePoint Server (even in a standalone implementation). The Report Server processing, rendering, and management are all from SharePoint application server running the Reporting Services SharePoint shared service. For this to happen, at SQL Server installation time, the SharePoint Integrated mode has to be chosen. The access to reports and related operations in this case are from a SharePoint frontend.

The following elements are required for SharePoint mode:

  • SharePoint Foundation 2010 or SharePoint Server 2010
  • An appropriate version of the Reporting Services add-in for SharePoint products
  • A SharePoint application server with a Reporting Services shared service instance and at least one Reporting Services service application

The following diagram taken from Microsoft documentation illustrates the various parts of a SharePoint Integrated environment of Reporting Services. Note that the alerting Web service and Power View need SharePoint Integration.

The numbered items and their description shown next are also from the same Microsoft document. Follow the link at the beginning of this section.

The architectural details presented previously were taken from Microsoft documentation.

Item number in the diagram






Web servers or Web Frontends (WFE). The Reporting Services add-in must be installed on each Web server from which you want to utilize the Web application feature such as viewing reports or a Reporting Services management page for tasks such as managing data sources and subscriptions.




The add-in installs URL and SOAP endpoints for clients to communicate with application servers through the Reporting Services Proxy.




Application servers running a shared service. Scale-out of report processing is managed as part of the SharePoint farm and by adding the service to additional application servers.



You can create more than one Reporting Services service application with different configurations, including permissions, e-mail, proxy, and subscriptions.




Reports, data sources, and other items are stored in SharePoint content databases.




Reporting Services service applications create three databases for the Report Server, temp, and data alerting features. Configuration settings that apply to all SSRS service applications are stored in RSReportserver.config file.


When you install Reporting Services in SharePoint Integrated mode, several features that you are used to in Native mode will not be available. Some of them are summarized here from the MSDN site:

  • URL access will work but you will have to access SharePoint URL and not Native mode URL. The Native mode folder hierarchy will not work.
  • Custom Security extensions can be used but you need to use the special purpose security extension meant to be used for SharePoint Integration.
  • You cannot use the Reporting Services Configuration Manager (of the Native mode installation).You should use the SharePoint Central Administration shown in this section (for Reporting Services 2008 and 2008 R2).
  • Report Manager is not the frontend; in this case, you should use SharePoint Application pages.
  • You cannot use Linked Reports, My Reports, and My Subscriptions in SharePoint mode.
  • In SharePoint Integrated mode, you can work with Data Alerts and this is not possible in a Native mode installation.
  • Power View is another thing you can do with SharePoint that is not available for Native mode. To access Power View the browser needs Silverlight installed.
  • While reports with RDL extension are supported in both modes, reports with RDLX are only supported in SharePoint mode.
  • SharePoint user token credentials, AAM Zones for internet facing deployments, SharePoint back and recovery, and ULS log support are only available for SharePoint mode.

For the purposes of discussion and exercises in this article, a standalone server deployment is used as shown in the next diagram. It must be remembered that there are various other topologies of deployment possible using more than one computer. For a detailed description please follow the link http://msdn.microsoft.com/en-us/library/bb510781(v=sql.105).aspx.

The standalone deployment is the simplest, in that all the components are installed on a single computer representative of the installation used for this article. The following diagram taken from the preceding link illustrates the elements of the standalone deployment:

Reporting Services configuration

For both modes of installation, information for Reporting Services components is stored in configuration files and the registry. During setup the configuration files are copied to the following locations:

  • Native mode

    C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER

  • SharePoint Integrated mode

    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

Follow the link http://msdn.microsoft.com/en-us/library/ms155866.aspx for details.

Native mode

The Report Server Windows Service is an orchestrated set of applications that run in a single process using a single account with access to a single Report Server database with a set of configuration files listed here:

Stored in








Stores configuration settings for feature areas of the Report Server Service: Report Manager, the Report Server Web Service, and background processing.


<Installation directory> \ Reporting Services \ReportServer




Stores the code access security policies for the server extensions.


<Installation directory> \ Reporting Services \ReportServer




Stores the code access security policies for Report Manager.


<Installation directory> \ Reporting Services \ReportManager


Web.config for the Report Server Web Service


Includes only those settings that are required for ASP.NET.


<Installation directory> \ Reporting Services \ReportServer


Web.config for Report Manager


Includes only those settings that are required for ASP.NET.


<Installation directory> \ Reporting Services \ReportManager


ReportingServicesService. exe.config


Stores configuration settings that specify the trace levels and logging options for the Report Server Service.


<Installation directory> \ Reporting Services \ReportServer \Bin

Registry settings


Stores configuration state and other settings used to uninstall Reporting Services. If you are troubleshooting an installation or configuration problem, you can view these settings to get information about how the Report Server is configured.


Do not modify these settings directly as this can invalidate your installation.


HKEY_LOCAL_MACHINE \SOFTWARE \ Microsoft \ Microsoft SQL Server \<InstanceID> \ Setup and HKEY_ LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Microsoft SQL Server\Services\ ReportServer




Stores configuration settings for Report Designer. For more information follow the link http://msdn.microsoft.com/en-us/library/ms160346.aspx


<drive>:\Program Files \Microsoft Visual Studio 10 \Common7 \IDE \ PrivateAssemblies




Stores the code access security policies for the server extensions used during report preview.


C:\Program Files\ Microsoft Visual Studio 10.0\ Common7\IDE\ PrivateAssembliesr


First is the RSReportServer configuration file which can be found in the installation directory under Reporting Services. The entries in this file control the feature areas of the three components in the previous image, namely, Report Server Web Service, Report Server Service, Report Manager, and background processing.

The ReportServer Configuration file has several sections with which you can modify the following features:

  • General configuration settings
  • URL reservations
  • Authentication
  • Service
  • UI
  • Extensions
  • MapTileServerConfiguration (Microsoft Bing Maps SOAP Services that provides a tile background for map report items in the report)
  • Default configuration file for a Native mode Report Server
  • Default configuration file for a SharePoint mode Report Server

The three areas previously mentioned (Report Server Web Service, Report Server Service, and Report Manager) all run in separate application domains and you can turn on/off elements that you may or may not need so as to improve security by reducing the surface area for attacks. Some functionality works for all the three components such as memory management and process health.

For example, in the reporting server Kailua in this article, the service name is ReportServer$KAILUA. This service has no other dependencies. In fact, you can access the help file for this service when you look at Windows Services in the Control Panels shown. In three of the tabbed pages of this window you can access contextual help.

SharePoint Integrated mode

The following table taken from Microsoft documentation describes the configuration files used in the SharePoint mode Report Server. Configuration settings are stored in SharePoint Service application databases.

Stored in






RSReportServer. config


Stores configuration settings for feature areas of the Report Server Service: Report Manager, the Report Server Web Service, and background processing.


<Installation directory> \Reporting Services \ ReportServer




Stores the code access security policies for the server extensions.


<Installation directory> \Reporting Services \ ReportServer


Web.config for the Report Server Web Service

Registry settings


Stores configuration state and other settings used to uninstall Reporting Services. Also stores information about each Reporting Services service application.


Do not modify these settings directly as this can invalidate your installation.


HKEY_LOCAL_MACHINE \ SOFTWARE \Microsoft \ Microsoft SQL Server \<InstanceID> \Setup


For example instance ID: MSSQL11.MSSQLSERVER and HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\ Microsoft SQL Server\ Reporting Services\ Service Applications


RSReportDesigner. config


Stores configuration settings for Report Designer.


<drive>:\Program Files \ Microsoft Visual Studio 10 \Common7 \IDE \ PrivateAssemblies


Hands-on exercise 3.1 – modifying the configuration file in Native mode

We can make changes to the rsreportserver.config file if changes are required or some tuning has to be done. For example, you may need to change, to accommodate a different e-mail, change authentication, and so on. This is an XML file that can be edited in Notepad.exe (you can also use an XML Editor or Visual Studio). You need to start Notepad with administrator privileges.

Turn on/off the Report Server Web Service

In this exercise, we will modify the configuration file to turn on/off the Report Server Web Service. Perform the following steps:

  1. Start Notepad using Run as Administrator.
  2. Open the file at this location (you may use Start Search| for rsreportserver.config) which is located at C:\Program Files\Microsoft SQL Server\MSRS11.KAILUA\Reporting Services\ReportServer\rsreportserver.config.
  3. In Edit Find| type in IsWebServiceEnabled. There are two values True/False. If you want to turn off, change TRUE to FALSE. The default is TRUE.

    Here is a section of the file reproduced:


  4. Save the file to apply changes.

Turn on/off the scheduled events and delivery

This changes the report processing and delivery. Make changes in the rsreportserver.config file in the following section of <Service/>:


The default value for all of the three is TRUE. You can make it FALSE and save the file to apply changes. This can be carried out modifying FACET in SQL Server Management Studio (SSMS), but presently this is not available.

Turn on/off the Report Manager

Report Manager can be turned off or on by making changes to the configuration file. Make a change to the following section in the <Service/>:


Again, this change can be made using the Reporting Services Server in its FACET. To change this make sure you launch SQL Server Management Studio as Administrator. In the following sections use of SSMS via Facets is described.

Hands-on exercise 3.2 – turn the Reporting Service on/off in SSMS

The following are the steps to turn the Reporting Service on/off in SSMS:

  1. Connect to Reporting Services_KAILUA in SQL Server Management Studio as the Administrator. Choose HODENTEKWIN7\KAILUA under Reporting Services. Click on OK.

  2. Right-click on HODENTEKWIN7\KAILUA (Report Server 11.0.22180 –HodentekWin7\mysorian).

  3. Click on Facets to open the following properties page

  4. Click on the handle and set it to True or False and click on OK. The default is True. It should be possible to turn Windows Integrated security on or off by using SQL Server Management Studio. However, the Reporting Services Server properties are disabled.

Salient features of Reporting Services 2012

In the previous sections we described the environment created for working through the examples in this article and the details of the engines that work with the two modes of installation and the configuration files (or Registry settings) that are associated with them.

In this section we will look at the features of Reporting Services in the following sequence:

  • Report definition
  • Report processing stages
  • Creating reports
  • Report parts and reusability
  • Customizing reports
  • Saving and deploying reports
  • Report validation
  • Viewing reports
  • Managing reports
  • New features in RS2012 SharePoint Integrated
  • Implementing security – authentication and authorization
  • URL access
  • Reporting Services extensions
  • Reporting Services tools

The RS reports follow XML based report definitions that include report data and report layout elements. On the file system they have the extension RDL. A report item is published to a Report Server or SharePoint Site. It is accessed from a Report Manager, SharePoint Site, or even on an IIS server after some modification. As mentioned previously, reports on a Report Server installed in Native mode can be accessed using Web parts on a SharePoint server.

Report definition

Report definition is XML based and complete in all details. This means it is portable and can be recreated easily. However, one may have to implement the details in the report definition especially those related to data and the layout details in the XML file.

XML-based report definition (the .rdl file)

Here is the XML code of a typical report data's shared data source connection information in XML:

<?xml version="1.0" encoding="utf-8"?>
xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="DataSource1">
<ConnectString>Data Source=HODENTEKWIN7\KAILUA;Initial

This defines a DataSourceID for obtaining data in the Northwind database tables on the SQL Server 2012. The access method is via Windows Authentication. The DataSource name is DataSource1. The <IntegratedSecurity/> tag with value true is for Windows Authentication.

With this as the connection, a dataset is derived from one of the tables as shown in the next XML document for the dataset:

<?xml version="1.0" encoding="utf-8"?>
reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/
<DataSet Name="">
<CommandText>SELECT LastName, FirstName
FROM Employees</CommandText>
<Field Name="LastName">
<Field Name="FirstName">

Here the following query is used to just extract FirstName and LastName from the Employees table in the Northwind database. This is a shared dataset that can be used in more than one report, if needed. Have a look at the following query:

SELECT LastName, FirstName
FROM Employees

This dataset populates the layout elements that you choose from the Toolbox completing the report design. The report with RDL extension has all the XML elements of data and the layout information.

The layout information even for a single field takes up a couple of pages on Notepad and it is not reproduced here. Readers are urged to take a look at the XML of a RDL report. If you are authoring a report in SQL Server Data Tools (SSDT), right-click on a report and click on View Code to review the XML of the report.

Power View report contents

Power View reports have the extension .rdlx. These files are ZIP files containing the report definition file, a reportState file (XML), and a folder.

Using the same query as the one used for Native mode, a report with .rdlx extension was created. The report definition file is very large and cannot be shown here but the download of the report file is provided for the readers so that they can open and take a look at how the report is structured.

After a report is authored it goes through a number of processing steps shown here before it is displayed:

  • Compilation : Report definition evaluation, store compiled intermediate format for server
  • Processing : Run queries and combine with layout
  • Rendering : Format page using a rendering extension

The rendered report can be exported if required through another processing step, Export. Reports are authored using authoring tools such as Report Builder and SSDT and they are processed by the Report Servers. We have already seen how reports are authored using SSDT where the compiled files are processed and displayed.


In this article, we looked at accessing Report Servers from three different kinds of applications, namely, SSIS, WPF Application, and SharePoint Site. SQL Server Integration Services can be used to look at Report Servers using the Web Service Task but still some of the parts are hard to configure. The Windows Presentation Foundation class project as well as the Windows Forms project can make use of a Web Browser control to access Report Servers useful for developers desiring to integrate reports with desktop/Web applications. Finally, reports on a Native mode Report Server can be brought into folder, traversed and viewed by using SharePoint Web parts, the Report Explorer, and the Report Viewer.

While SQL Server 2012 marked the beginning of Reporting Services 2012 integration with SharePoint 2010, there are still many folks using the SQL Server 2008 R2. The reporting services integration for those servers works differently from that of SQL Server 2012. With the release of SQL Server 2012 SP1 and the SharePoint 2013, reporting services gets turbocharged especially for Power View.

In conclusion, the article provides a complete reference to SSRS 2012 covering both modes of implementation, Native and SharePoint Integrated mode.

Resources for Article :

Further resources on this subject:

Books to Consider

comments powered by Disqus