This content is part of the Essential Guide: Guide to SQL business intelligence, analytics and data visualization

Reporting Services architecture: Creating high-quality reports

In this book excerpt, author and Microsoft SQL Server Reporting Services expert Brian Larson introduces features of Reporting Services.

This is an excerpt from Chapter 1, "Let's Start at the Very Beginning," from the book Microsoft SQL Server 2012 Reporting Services by Brian Larson. Larson is CTO and partner at Superior Consulting Services. In this chapter, he discusses the importance of sharing business intelligence from the perspective of different roles within a business. He then explains how Reporting Services help achieve useful information sharing. This excerpt focuses on the features of the Reporting Services architecture and on structuring reports.

Report authoring architecture

Copyright info

This excerpt is from the book Microsoft SQL Server 2012 Reporting Services 4/E by Brian Larson. Published by McGraw-Hill Professional, New York. ISBN 9780071760478. Copyright 2012.

As mentioned previously, Reporting Services reports are created using either the Report Builder or the Report Designer. The Report Builder supports the construction of full-featured Reporting Services reports. It features a user interface similar to that of Microsoft Word 2010 or Microsoft Excel 2010, so it should be familiar for users comfortable with those products. The Report Designer, found in SQL Server Data Tools and Visual Studio, also supports all of the features of Reporting Services. In addition, it provides tools for project organization and source-code management for those reporting projects that have a lifecycle similar to that of a software development project (version control, check-in/check-out, etc.)

This book can help you get the most from the incredibly rich report-authoring features available in Report Builder and Report Designer. These environments contain everything necessary to create a wide variety of reports for Reporting Services. Everything you need to select information from data sources, create a report layout and test your creation is right at your fingertips.

Report Structure

Figure 1-1

Figure 1-1

Each Reporting Services report contains two distinct sets of instructions that determine what the report will contain. The first is the data definition. The data definitioncontrols where the data for the report will come from and what information will be selected from that data. The second is the report layout. The report layout controls how the information will be presented on the screen or on paper. Both of these sets of instructions are stored using the Report Definition Language (RDL).

Figure 1-1 shows this report structure in a little more detail.

Data definition

The data definition contains two parts: the data source and the data set. The data source is the database server or data file that provides the information for your report. Of course, the data source itself is not included in the report. What is included is the set of instructions the report needs to gain access to that data source. These instructions include the following:

  • The type of source you will be using for your data (for example, Microsoft SQL Server 2012, Oracle, DB2, Informix or Microsoft Access). Reporting Services will use this information to determine how to communicate with the data source.
  • The name of the database server or the path to the data file.
  • The name of the database.
  • The login for connecting to this data source, if a login is required.

When the report is executing, it uses the data source instructions contained in the report 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 data set.

More on SQL Server 2012

Examining SQL Server 2012 data warehouse appliances

The ins and outs of the SQL Server 2012 contained database

SQL Server Data Tools 2012 makes developing DB projects easier

The content of the data set is defined using a tool called the Query Designer. The Query Designer helps you build a database query. The database query may be in Transact-Structured Query Language, or T-SQL, for querying relational data, Multidimensional Expression language (MDX) for querying multidimensional data or Data Mining Expression language, or DMX, for querying data-mining data. The query provides instructions to the data source, telling it what data you want selected for your report. The query is stored in the report as part of the data definition.

The data selected by the query into the dataset consists of rows and columns. The rows correspond to the records the query selects from the data source. The columns correspond to the fields the query selects from the data source. (Queries in MDX are flattened into a table of rows and columns.) Information on the fields to be selected for the dataset is stored in the report as part of the data definition. Only the information on what the fields will be called and the type of data they will 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 when the report is run.

Report layout

The data that the report has extracted into a data set is not of much use to you unless you have some way of presenting it to the user. You need to specify which fields go in which locations on the screen or on paper. You also need to add such things as titles, headings and page numbers. All of this forms the report layout. In most cases, your report layout will include a special area that interacts with the data set. This special area is known as a data region. A data regiondisplays all the rows in the data set 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), which 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 data set, and the report layout. When you create a report in the Report Designer, it is saved in a file with an .rdl extension.

If you have not worked with XML, or are not even sure what it is, don't worry. Report Designer, Report Builder and Reporting Services will take care of all the RDL for you. For those of you who want to learn more about RDL, we'll take a quick peek under the hood in Chapter 8.

Report Designer

Figure 1-2 shows the Report Designer. This is one of the tools you can use for creating and editing reports throughout this book. We will look at some features of the Report Designer now and discuss them in more detail in Chapter 5 through Chapter 9.

Figure 1-2 The Report Designer

Figure 1-2 The Report Designer

Design surface

The design surface, in the center of Figure 1-2, is where you create your report layout. To do this, you use four of the other areas visible in Figure 1-2: the Report Data window, the Toolbox, the Properties window and the Grouping pane. You will learn how these work in the following sections. The design surface shares space with the Preview tab. The Preview tab will show you how the report layout and the data combine to create an honest-to-goodness report.

Report Data window

The Report Data window, shown in the upper-lefthand corner of Figure 1-2, provides a list of database and other types of fields you can use in your report. The Report Data window makes it easy to add database information to your report layout. Simply drag the desired field from the Report Data window and drop it in the appropriate location on your report layout. The Report Designer takes care of the rest.

For further discussion of this chapter, see this Q&A with author Brian Larson.

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)