If Reporting Services were simply a report authoring environment, it would be a good tool. However, Reporting Services has much more to offer beyond just report creation. In fact, Reporting Services is a complete, enterprise-wide report management and distribution service. Reporting Services enables us to securely distribute reports throughout an organization using an existing intranet infrastructure, and it provides for the integration of reports with both desktop and web-based applications.
A report project can contain a number of reports. Each report contains two distinct sets of instructions that determine what the report will contain. The first is the data definition, which controls where the data for the report comes from and what information is to be selected from that data. The second set of instructions is the report layout, which controls how the information is presented on the screen or on paper. Both of these sets of instructions are stored using the Report Definition Language (RDL).
The data definition contains two parts: the data source and the dataset. The data source is the same as we have seen used in OLAP projects: It is the set of instructions the report needs to gain access to a data source that provides data for the report. The data source definition can be created right inside the report definition, or it can be created externally and shared by a number of reports. In most cases, report management is easier when we use external shared data sources. When the report is executing, it uses the data source instructions to gain access to the data source. It then extracts information from the data source into a new format that can be used by the report. This new format is called a dataset. The content of the dataset is defined using a tool called the Query Designer. The Query Designer helps us build a database query. The database query may be in T-SQL for querying relational data, MDX for querying multidimensional data, or DMX for querying data-mining data. The query provides instructions to the data source, telling it what data we want selected for our report. The query is stored in the report as part of the data definition.
[info](Reporting Services does not support hierarchical result sets, so MDX and DMX query results are flattened into a single table of rows and columns.[/info]
Information on the fields to be selected into the dataset is stored in the report as part of the data definition. Only the information on what the fields are to be called and the type of data they are to hold is stored in the report definition. The actual data is not stored in the report definition, but instead, is selected from the data source each time the report is run.
The data the report has extracted into a dataset is not of much use to us unless we have some way of presenting it to the user. We need to specify which fields go in which locations on the screen or on paper. We also need to add things such as titles, headings, and page numbers. All of this forms the report layout. In most cases, our report layout will include a special area that interacts with the dataset. This special area is known as a data region. A data region displays all the rows in the dataset by repeating a section of the report layout for each row.
Report Definition Language
The information in the data definition and the report layout is stored using the Report Definition Language (RDL). RDL is an Extensible Markup Language (XML) standard designed by Microsoft specifically for storing report definitions. This includes the data source instructions, the query information that defines the dataset, and the report layout. When we create a report in the Report Designer, it is saved in a file with a .rdl extension.
Report Service Architecture
After a report has been developed, it is time to share that report with our users. This is when our report get matured. This is known as deploying the report. Let me assure you, reports pass through deployment much easier than you and I passed through adolescence!
The report server is the piece of the puzzle that makes Reporting Services the product it is. This is the software environment that enables us to share our report with the masses, at least those masses who have rights to the server. The following figure shows the functional structure of Report Server.
When a report is deployed to a report server, a copy of the report’s RDL definition is put in that server’s Report Catalog. The Report Catalog is a set of databases used to store the definitions for all the reports available on a particular report server. It also stores the configuration, security, and caching information necessary for the operation of that report server.
When a report needs to be executed, the report processor component of the report server directs the show. The report processor retrieves the RDL for the report from the Report Catalog. It then reads through this RDL to determine what is needed for the report. The report processor orchestrates the operation of the other components of the report server as the report is produced. It takes the output from each of the other components and combines them to create the completed report.
As the report processor encounters dataset definitions in the report RDL, it retrieves the data to populate that dataset. It does this by first following the instructions in the report’s data source for connecting to the database server or file that contains the data. The report processor selects a data provider that knows how to retrieve information from this type of data source. The data provider then connects to the source of the data and selects the information required for the report. The data provider returns this information to the report processor, where it is turned into a dataset for use by the report.
Once all the data for the report has been collected, the report processor is ready to begin processing the report’s layout. To do this, the report processor looks at the format requested. This might be HTML, Portable Document Format (PDF), Tagged Image File Format (TIFF), or one of several other possible formats. The report processor then uses the renderer that knows how to produce that format. The renderer works with the report processor to read through the report layout. The report layout is combined with the dataset, and any repeating sections of the report are duplicated for each row in the dataset. This expanded report layout is then translated into the requested output format. The result is a report ready to be sent to the user.
The request handler is responsible for receiving requests for reports and passing those requests on to the report processor. Once the report processor has created the requested report, the report handler is also responsible for delivering the completed report.
Reporting Services Components
Reporting Services is not a single program that runs on a computer to produce reports. Instead, it is a series of services, web applications, and databases that work together to create a report management environment. As you plan your Reporting Services installation, it is important that you understand a little bit about each piece of the puzzle and how all these pieces work together to create a complete system. As the following figure shows all the parts that make up a complete Reporting Services installation. Each part has a specific role to play in the development, management, and delivery of reports or in the management of the Reporting Services environment itself. Let’s take a look at each part and see how it fits into the whole.
Report Server Service
The Report Server service is the heart of Reporting Services. This service is responsible for the two main interfaces with the report server. First, it contains the application that implements the Report Manager website. Second, it provides a web service interface for programmatic interaction with the report server. In past versions of Reporting Services, these two interfaces were hosted by Internet Information Services (IIS). In SQL Server 2008, the Report Server Service uses the Hypertext Transfer Protocol (HTTP) library in Windows to serve as its own HTTP request handler, so IIS is not needed.
The Reporting Services Windows service needs to have a valid user name and password that it can use to access the Report Catalog. This login information, along with other information that determines how the report server operates, is stored in the RSReportServer.config file. Most of the information in the RSReportServer.config file is stored as plain text and can be modified using Notepad or a similar text editor. The login information, however, is encrypted when it is stored in this file. It cannot be changed except through the administration utilities.
Report Server and Report Server TempDB Databases
Reporting Services uses two databases within SQL Server: the Report Server and the Report Server Temp DB. The Report Server database is used to store the Report Catalog. The Report Server database also holds information about the Report Manager website. This includes such things as the folder structure of the website and the security settings for each folder and report. As the name implies, the Report Server Temp DB database is used as temporary storage for Reporting Services operations. Information can be stored here to track the current users on the Report Manager website. Short-term copies of some of the most recently executed reports are also stored here in what is known as the execution cache.