Chapter 1: Introduction to SQL Server Reporting Services (SSRS)


In This Chapter

  • What Is SSRS

  • SSRS for End Users

  • Overview of Features

  • SSRS in the Report Development Life Cycle

  • Editions of Reporting Services

  • How Is SSRS Licensed?

Note - This book abbreviates SQL Server 2008 Reporting Services as SSRS and SQL Server 2005 Reporting Services as SSRS2K5.

New features available in SSRS and not in SSRS2K5 are labeled with “New in 2008.”

In today’s ultracompetitive business environment, having good information is essential. Companies are awash in information, and with the advent of technologies such as radio frequency identification (RFID), more and more information is coming. Technology has made the job of gathering information trivial, but making sense of it all still remains elusive. This makes good reporting and business intelligence tools essential.

This first chapter is strictly nontechnical. This chapter focuses on the following:

  • Capabilities of SSRS

  • How it fits into the Microsoft Business Intelligence platform

  • Report development life cycle as it relates to SSRS

  • Editions of SSRS

  • Licensing SSRS

What Is SSRS?

SSRS is Microsoft’s answer to business reporting. It provides a unified, server-based, extensible, and scalable platform from which to deliver and present information. Its scope extends from traditional paper reports to web-based delivery and interactive content. SSRS can also be configured to deliver reports to peoples’ inboxes, file shares, and so on. SSRS is capable of generating reports in various formats, such as the web-oriented Hypertext Markup Language (HTML) and desktop application (Microsoft Excel and CSV) formats, thus allowing users to manipulate their data in whatever format is required. In addition, SharePoint can be used as a front end for SSRS, allowing reports to be presented directly in corporate portals.

SSRS is just one of the components in the Microsoft Business Intelligence (BI) platform. Combined, those components provide an excellent platform for enterprise data analysis. The Microsoft BI platform includes the following:

  • SQL Server: The traditional database engine, which also stores SSRS catalog data.

  • SQL Server Analysis Services (SSAS): A component for online analytical processing (OLAP) and data mining. OLAP performs data aggregation and allows users to traverse from aggregations to details looking through the dimensions (such as geography or time) of data. Data mining helps users to discover patterns in data.

  • SQL Server Integration Services (SSIS): A component for extracting, transforming, and loading (ETL) data.

SSRS for End Users

SSRS is unique in the Microsoft BI suite because it covers a variety of information users. Microsoft divides users into three groups: information consumers, information explorers, and analysts.

Table 1.1 briefly summarizes the percentages of users in each group, the level of technical experience, and the expectations from an enterprise reporting tool. All of these factors will vary from company to company, but generally the breakdown holds true.

TABLE 1.1 Breakdown of Information Workers

Type of User


Technical Expertise





Analysts can develop reports, work with ad hoc reports, and perform sophisticated calculations (such as linear regressions and trend analysis). Analysts often publish reports to explorers and consumers.




Information explorers want to interact with reports to some degree, such as applying filters or performing drill down through.




Information consumers use static, predefined, and preformatted reports.

To address the varying needs of these types of users, SSRS provides three main tools from the user perspective:

  • Report Viewer: The primary mechanism for viewing reports over the Web. Report Manager is the name of the website that SSRS sets up. It provides a very clean and neatly organized user interface for end users. Developers can also embed a Report Viewer control into both ASP.NET and Windows Forms applications.

  • Report Builder: The tool that provides users with a front end for ad hoc reporting against a SQL Server or Analysis Services database. Unlike most ad hoc reporting tools, users of Report Builder do not need to know Structured Query Language (SQL) or anything about joins or grouping to create reports.

  • Report Designer: The tool that takes on the job of building advanced reports. Although Report Builder does a good job as an ad hoc reporting tool, Report Designer was made to tackle really advanced reports.

Figure 1.1 summarizes the types of reporting users, and mentions some of the tools SSRS provides for them.

Figure 1.1

Reporting Services users and tools.

Overview of Features

SSRS has a number of features to address complex business reporting needs. Over the course of this book, these features are explored more closely. For now, here is a brief overview.

As far as creating reports, SSRS is a full-featured report engine. Reports can be created against any data source that has a managed code provider, OLE DB, or ODBC data source. This means you can easily retrieve data from SQL Server, Oracle, Analysis Services, Access, or Essbase, and many other databases. This data can be presented in a variety of ways. Microsoft took the feedback from SSRS2K5 and enhanced 2008 with new Chart and Gauge controls, and a new Tablix control, which is a mix of the Table and the Matrix controls from the earlier release. Combined, these new presentation formats give a whole new experience out of the box. Other enhancements include new output presentation formats, including Word and Excel, and direct integration with SharePoint.

Here is a concise list of SSRS features:

  • Retrieve data from managed providers, OLE DB, and ODBC connections

  • Display data in a variety of ways, including tabular, free form, and charts

  • Export to many formats, including HTML, PDF, XML, CSV, TIFF, Word reports (New in 2008), and Excel

  • Aggregate and summarize data

  • Add in report navigation

  • Create ad hoc reports and save them to the server

  • Create custom controls using a report-processing extension

  • Embed graphics and images and external content

  • Integrate with SharePoint

  • Provide a Simple Object Access Protocol (SOAP) application programming interface (API) and pluggable architecture

  • Provide subscription-based reports and on-demand reports

  • Allow users to store and manage their own custom reports built with SSRS’s Report Builder 2.0 and manage subscriptions to the reports (New in 2008).

  • URL-based report access

  • Gauge and Chart controls to display KPI data (New in 2008)

As you can see, SSRS provides a comprehensive set of features out of the box. Another nice feature of SSRS is its extensibility. Because there is no way that the developers of SSRS could have anticipated every need of an enterprise reporting solution, they made SSRS extensible. This extensibility enables developers to use SSRS in any number of ways, from embedded reports to customized reporting solutions.

Enterprise Report Examples

Each user is likely to have favorite reports to make timely and effective business decisions, and although it is not possible to cover a whole gamut of reports in this book, some common ideas can help you think through practical applications of SSRS.

Scorecard reports are frequently used in today’s businesses and provide information for each manager on how well his group is doing as compared to the goals set for the group. Usually, a scorecard implements a “traffic light” type of highlight or a “gauge” indicator. Values on the scorecard are highlighted in green when the group is meeting its goals, in yellow when the group is doing so-so, and in red when the group’s performance requires immediate attention. Scorecard reports can take advantage of the key performance indicators (KPIs) features of Analysis Services 2008. Gauges and charts have been significantly enhanced in SQL Reporting Services 2008 by the acquisition of the Dundas Gauge and Chart controls by Microsoft.

When users are looking to combine a comprehensive set of business health and “speed” gauges (scorecard) and related information in a small space, a dashboard is used to accomplish this goal. A dashboard provides a short, typically one-page, summary view of a business (much like a car’s dashboard summarizes a car’s status) and allows drill down through the items on the top page to retrieve detailed information. SharePoint is an excellent platform to host dashboards and greatly simplifies arranging reports in a meaningful fashion on a page.

Today, when everybody is so short on time, it might be easy to miss an information point that could prove fatal for a business. Exception reporting is what comes to the rescue of a time-constrained user. Unlike regularly scheduled reports or summaries provided by scorecards, exception reports are created and delivered to a user when an unusual event occurs. An exception report removes the “noise” created in periodic reports, focusing instead on mission-critical anomalies. An example of such an anomaly could be a sudden drop in daily sales for a particular region.

Other typical reports include various views of sales (geographic, demographic, product, promotion breakdowns), inventory, customer satisfaction, production, services, and financial information.

SSRS in the Report Development Life Cycle

To understand all the ways SSRS can be used and deployed, you can simply walk through the report development life cycle and see what features are useful in each stage.

A typical reporting application goes through three stages (see Figure 1.2): authoring, managing, and delivery. SSRS provides all the necessary tools to work with a reporting application in all three stages.

Figure 1.2

Reporting life cycle.

Authoring Stage

During this stage, the report author defines the report layout and sources of data. For authoring, SSRS maintains all the features of SSRS2K5 and adds some new features. SSRS still maintain Report Designer as its primary tool for developing reports in the 2008 release. Report Builder 1.0 is also available as a tool for developing reports against report models. Report models are metatdata models describing the physical structure and layout of the underlying SQL Server database. The biggest new enhancement for SSRS as far as tools for authoring go is the addition of Report Builder 2.0. Report Builder 2.0 fits nicely into the high-powered analyst space and gives them almost all the power of Business Intelligence Development Studio (BIDS), but with an Office 2007 look and feel.

Note - Report Builder 2.0 is not installed along with the SQL Server Reporting Services, but is available as a free download in the SQL Server 2008 feature pack.

Figure 1.3 shows the Report Designer interface.

Figure 1.3

Report Designer.

Report Designer is a full-featured report-authoring tool that targets developers and provides extensive design capabilities and can handle a variety of data sources. Report Designer can work with all reports generated for SSRS, including reports generated by Report Builder. Report Designer incorporates the following productivity features:

  • Import Access Reports, a feature that enables report designers to import Microsoft Access reports and create a report definition in turn. In complex cases, it might not be able to successfully or compleM tely import an Access report. The general rule of thumb is that SSRS will be able to convert approximately 80% of the existing Access reports.

  • IntelliSense for Expression Editing, which provides assistance with the syntax of a function used in expressions, names of class members, and indicates syntax errors in expressions by underlining them with squiggly red lines. You will see more details about this feature in Chapter 10, “Expressions.”

  • Multidimensional Expressions (MDX) and Data Mining Expressions (DMX) Query Designer, which provides a drag-and-drop interface for writing MDX and DMX queries. This feature is covered in more detail in Chapter 17, “Working with Multidimensional Data Sources.”

  • Relational Query Designer, which provides a drag-and-drop interface for writing SQL queries.

  • Report Wizard, which provides step-by-step instructions to create a report.

  • Preview mode, which enables a report author to preview the report design and layout before he publishes the report. This is a powerful feature of a Report Designer that does not require Reporting Services to be installed on a computer on which the preview is generated.

  • Publishing and deployment functionality, which allows a report designer to publish a report to a target server. For example, the developer might choose to publish to a development or to a test reporting server.

SSRS also allows end users to design reports with SSIS. Three tools make this possible: Report Builder and Model Builder, and the new Report Builder 2.0. For those familiar with SSRS 2K5, Report Builder and Model Builder are carryover with little to no changes. Report Builder 2.0, however, is a brand new addition, which is a break from the previous edition. It is a smart client application that enables users to design reports with the full capability of SSRS. It also works directly against the client database.

1 2 3 Page 1
Page 1 of 3
IT Salary Survey: The results are in