SQL Server Reporting Services (SSRS) is Microsoft's primary technology for delivering standard reports. As mentioned in the previous chapter, these types of reports typically contain a well-defined output that is known and developed by a member of the IT department ahead of time and delivered on a recurring basis to facilitate the regular daily, weekly, and even monthly decision-making needs of the business.
One of the great capabilities of SSRS is the fine-grained control it allows users over visualizations, layout, parameterization, and extensibility, providing for a level of customization that simply can't be matched by any other reporting tool in the Microsoft stack. In fact, it is this ability of control and customization that also makes SSRS well suited for the development and delivery of complex dashboards and scorecards, even though there are arguably more suitable tools (for example, PerformancePoint) for this purpose when requirements are more basic.
At the same time, such a great level of control comes with the cost of a higher-than-necessary development effort and, therefore, can often be viewed as an impediment to the delivery of information to the business. SSRS, despite having been marketed in the past as a self-service reporting tool via Report Builder, requires an IT skill set to develop all but the most basic reports. It is for this very reason that we have seen the rise of self-service reporting tools over the last few years.
In this chapter, we'll cover the main features and functionalities offered in SQL Server Reporting Services 2012, including a breakdown of report components, development experience, extensibility, and security. By the end of the chapter, readers should have an understanding of the capabilities offered in SQL Server Reporting Services.
Before diving into all the cool features and functionalities offered by SSRS, it is important to have a basic understanding of the primary components that make up an SSRS report as well as a mental model of how these components work together to deliver information to the business; which is, after all, the entire purpose of standard reporting.
The primary components of an SSRS report are as follows:
The following diagram shows how data flows from the source system through these components and out to the end user:
Flow of data through primary report components
A data source is typically the first object created while building an SSRS report and contains all the information necessary to make a connection to a source system. The source system could be a SQL Server database, Analysis Services cube, a SharePoint list, or some other type of business application database such as Oracle.
While most reports typically pull data from a single data source, it is possible to pull data from multiple sources. In fact, a single report can be based on multiple data sources and multiple datasets. The following screenshot shows the full list of data sources that can be used with SSRS 2012 right out of the box:
Full list of available data sources
Note
Data sources use credentials to authenticate with the source system. Credentials can be stored with the data source or determined at runtime. Some Reporting Services features, such as subscriptions and snapshots, require a data source with stored credentials. We will cover credentials in more detail toward the end of this chapter in the section on security.
There are two types of data sources: shared and embedded. A shared data source exists as a standalone object outside of the SSRS report and can be reused and referenced by multiple SSRS reports. An embedded data source is specific to a single SSRS report. If you open an SSRS report file in a text editor, you can find a section (shown in the following screenshot) that contains the details of all the data sources included in the report. Notice that the snippet for the shared data source (DataSource2) only includes the information needed to locate the shared data source object on the report server, while the snippet for the embedded data source includes a connection properties section with all the information needed to connect to the source system.
XML code of SSRS report showing references to embedded dataset (top) and shared dataset (bottom)
One of the main benefits of using a shared data source is that it makes managing changes easier. This is especially true for large organizations, where you may have hundreds of reports based on the same relational source system. Imagine the headache and effort required to update the connection details for all of those reports if the source system is migrated to a new server. However, there are certain scenarios where an embedded data source is the only option. One example where the data source must be embedded is when you want to use an expression to dynamically control the destination of a data source object. Expressions will be covered later in this chapter.
A dataset is best thought of as a two-dimensional table structure based on the results of a query that has been executed against a report data source such as that displayed in the following screenshot:
Report dataset
This is not meant to imply that the results of the query are materialized in a physical structure, although that is an option if caching is configured.
Note
Caching the result set of a query may seem wasteful. However, when viewed from a more holistic perspective, the benefits can be substantial. Given the nature of standard reporting—where the same reports are being run by large numbers of users—the ability to cache the data retrieved from the source system can provide considerable performance gains and greatly reduce the load placed on the source system. We'll cover this topic in more detail later in the chapter.
While creating a dataset, the developer first chooses the data source, which can be a shared data source referenced by the report or an embedded data source created and contained within the report. Once the data source has been selected, a query is created—the results of which will be made available as a two-dimensional structure—like the one in the preceding screenshot, which can then be consumed by the various report items.
It is important to understand that the use of the word "query" in the preceding paragraph is in the most general sense. The type of query is completely dependent on the data source. For example, if the data source is a relational database, the query could be a standard SQL select statement, or it could be the result set from a stored procedure. On the other hand, if the data source is an Analysis Services database, the query could be an MDX query (for a multidimensional cube), a DMX query (for a data mining model), or even a DAX query (for a tabular model that is new to SQL Server 2012).
Dataset queries can be entered manually as text or constructed using the graphical query designer shown in the following screenshot. The graphical query designer is helpful when the report author is not very familiar with the query language. However, report authors who are more proficient in the query language are likely to prefer the manual entry method or a combination of the two.
Note
Though not a requirement, when the data source is a relational database, it is considered good practice to use a stored procedure to retrieve data for the dataset instead of a query embedded in the report. This offers an additional layer of encapsulation that comes in handy if/when minor changes are required at a later point in time. There may also be potential performance benefits through query parameterization and planned cache reuse on the source system, although thorough testing is the only way to be sure.
The Query Designer window is as follows:
Graphical query designer used to help construct SQL queries for datasets
Just as with the data source component, a dataset component can be shared or embedded. A shared dataset exists as a standalone object and can be referenced by multiple reports, while an embedded dataset is specific to a single report. A shared dataset cannot be based on an embedded data source; it must be based on a shared data source.
Datasets can be filtered to limit the data displayed on a report. However, it is typically better to push the filter down into the query defined in the dataset. This will maximize performance by only retrieving data from the source that needs to be displayed in the report. When the filter is applied to the dataset itself, all the data is retrieved from the data source and pulled into the dataset object, after which the filter is applied before data is consumed by the various report items.
Report items refer to the visual items that are placed directly on the report (referred to as the report body during authoring) that display information to the user. A report item can be a table, chart, gauge, map, or any of a number of different items.
Just as a dataset is linked to a specific data source, a report item is linked to a specific dataset. Looking at it from the other direction, many report items can be linked to the same dataset just as many datasets can be linked to the same data source. The following diagram helps you to visualize the cascading one-to-many relationship between these primary report components:
Relationship between primary report components
Report items can also be shared just like datasets and data sources. Shared report items, referred to as report parts can be based on either a shared dataset or an embedded dataset. When based on an embedded dataset, the dataset is wrapped up with the shared report part as a single unit and made available to other report developers. By creating shared report parts and reusing existing ones, the speed of report authoring can be greatly increased.
Note
Report items can be published to Report Server as report parts. Report authors using Report Builder to create new reports can save time by leveraging the existing report parts instead of recreating them in each report.
The extended use and administration of shared report parts is a bit more complex than shared datasets and shared data sources, and outside the scope of this book. Please check out the following TechNet article for more information: http://technet.microsoft.com/en-us/library/ee633670.aspx
The following is a list of the report items available right out of the box with SSRS 2012 along with basic descriptions of each:
- Textbox: This is a basic freeform text object that can be placed anywhere on the report body as well as the header and footer areas. The text value displayed can be static or the result of an expression.
- Line: This is just a graphic for aesthetic purposes.
- Table: This is one of the primary report items most commonly used to display data from a dataset in the standard two-dimensional table format. Cells of the table typically contain text but can also contain a variety of other report items, such as data bars, sparklines, charts, and many others.
- Matrix: This report item is based on the same underlying structure as a table except that it comes with a row group and column group already defined. Use this component when you want to display data pivoted on columns as with crosstab reports.
Note
Table and matrix report items are based on the same underlying structure, which is known as the tablix . A table is a tablix without row/column groups, while a matrix is a tablix with row/column groups. See the following screenshot for an example of the differences while displaying data in a table (left) versus a matrix (right).
Example of the differences when displaying data in a table (left) versus a matrix (right)
- Rectangle: This report item is a container used for grouping sets of report components placed within them. The group of report components can then be published to the report server as a single shared report part that can be reused in other reports. Rectangles can also be placed inside other report components such as the cells of a table.
- List: This report item, like the table and the matrix, is based on the underlying tablix data structure. The difference is that a list is used to create repeating areas in which additional report components can be placed in order to create what is known as a free-form report.
- Image: This report item is used to display a graphic, such as a company logo or product picture on the report. The image can be embedded in the report, but it is more common to have the image stored as a separate item on the report server, in a database, or somewhere out on the Web and then referenced from the report and displayed at runtime.
- Subreport: This report item is used to display the contents of another report (from the same report server) within the main report. The primary purpose of this report component is to promote encapsulation of information and reuse of existing development. However, from a performance perspective, it is typically a better idea to use the Nested Data Region design pattern, which you can read about in the following TechNet article: http://technet.microsoft.com/en-us/library/dd207033.aspx.
- Chart: This report item is used to provide a visual representation of the data in a report. The various types of charts available out of the box are shown in the following screenshot:
Options for chart report item
- Gauge: This report item is typically used to visualize Key Performance Indicators (KPIs) on a report. As you can see in the following screenshot, there are quite a few options available:
Options for gauge report item
Note
A KPI is a type of metric consisting of three to four measures that together provide information on how well the business is progressing towards a goal. These are very common in Business Intelligence dashboards or summary reports.
The following is a list of the measures that make up a KPI metric:
- Value: This is the current value of the metric.
- Target: This is the goal value.
- Status: This is the value that provides context between the Value and Target measures. This is typically defined as a breakdown of ranges corresponding to bad (red), ok (yellow), and good (green).
- Trend: This is the current trajectory based on recent history (optional).
- Map: This report item provides the ability to visualize information in a spatial or geographical context. There are typically two types of data involved with the map report item: spatial and analytical. Spatial data is required and provides the coordinates for layout. Analytical data is optional and provides the business measures or metrics.
Example of map report item
- Data bar: This report item is typically used in conjunction with a table or matrix report item and is good for visualizing the relative strength/weakness of a metric or measure across a group of business entities such as sales territories.
- Sparkline: This report item is similar to the data bar in the way is used in conjunction with a table or matrix report item. However, the sparkline is more appropriate than the data bar to visualize trends or business measures over time.
- Indicator: This is like the data bar and sparkline; this report item is commonly used in conjunction with a table or matrix and is used to visualize the status (and trend) of a business metric or KPI.
Options for indicator report items
In addition to the objects listed earlier, there are custom report items that you can obtain from third-party sources if the visualization required is not offered out of the box.