Reporting Services Crib Sheet
For things you need to know rather than the things you want to know
Contents
- Introduction
- The design of SSRS
- The components of SSRS
- SSRS DataSources and Datasets
- Conclusions
- Further Reading…
Introduction
SQL Server Reporting Services (SSRS) aims to provide a more intuitive way of viewing data. It allows business users to create, adapt and share reports based on an abstraction or ‘model’, of the actual data , so that they can create reports without having to understand the underlying data structures. This data can ultimately come from a variety of different sources, which need not be based on SQL Server, or even relational in nature. It allows also developers a wide range of approaches to delivering reports from almost any source of data as part of an application.
The reports are interactive. The word ‘reporting’, in SSRS, does not refer just to static reports but to dynamic, configurable, reports that can display hierarchical data with drill-down, filters, sorting, computed columns, and all the other features that analysts have come to expect from Excel. Users can specify the data they are particularly interested in by selecting parameters from lists. The reports can be based on any combination of table, matrix or graph, or can use a customized layout. Reports can be printed out, or exported as files in various standard formats.
SSRS provides a swift, cheap way of delivering to the users all the basic reports that are required from a business application, and can provide the basis for customized reports of a more advanced type.
The design of SSRS
The surprising thing about Reporting Services is its open, extensible, architecture. With SSRS, Microsoft has taken pains over a product that has an obvious long-term importance for data handling in .NET.
From a programmer’s perspective, the ‘Big Idea’ behind Reporting Services is to have a standard way of specifying reports. In a way, it is an attempt to do for reports what HTML did for rendering pages. Report Definition Language (RDL) is an XML-based open standard grammar. It was designed to provide a standard way to define reports, to specify how they should appear, their layout and content. It specifies the data source to use and how the user-interaction should work.
In theory, there could be a number of applications to design business reports; several ways of managing them, and a choice of alternative ways of rendering them. All these would work together because of the common RDL format.
SQL Server Reporting Services is the first product to adopt the architecture. It is a combination of report authoring, report management and report delivery. It is not limited to SQL Server data. It can take data from any ODBC source. Reporting Services can use a SQL Server Integration Services package as a data source, thereby benefiting from Analysis Service’s multidimensional analysis, hierarchical viewing and data mining. It can just as easily report from OLAP data as relational data. It can also render reports to a number of media including the browser, application window, PDF file, XML, Excel, CSV or TIFF.
The API of SSRS is well-enough documented to allow the use of custom data, custom ways of displaying data or special ways of delivering it. Because Microsoft has carefully documented the RDL files, and the APIs of the ReportingServices namespace, it is reasonably easy to extend the application for special data or security requirements, different data sources, or even the way the reports are rendered. One can, of course, replace a component such as the report authoring tool with one designed specially for a particular application.
When SSRS is installed, it is set to deliver reports via a ‘Report Server’ which is installed as an extension to the IIS service on the same server as that on which SQL Server is installed. The actual portal, with its hierarchical menu, report models and security, can be configured either via a browser or from Visual Studio. The browser-based tools are designed more for end-users, whereas the Visual Studio ‘Business Intelligence Development Studio’ tools are intended for the developer and IT administrator.
The ‘Report Server’ is by no means the only possible way of delivering reports using Reporting Services, but it is enough to get you started.
So let’s look in more detail at the three basic processes that combine to form SQL Server Reporting Services (SSRS): Report Authoring, Report Management and Report Rendering
The components of SSRS
Report Authoring
The Report Authoring tools produce, as their end-product, RDL files that specify the way that the report will work.
Any application capable of producing an XML file can produce an RDL file, since RDL is merely an XML standard. There is nothing to stop an application from producing an RDL and then using Microsoft’s ReportViewer component to render the report.
Hopefully, third-party ‘Report Designer’ packages will one day appear to take advantage of the applications that are capable of rendering RDL files.
The report designers of SSRS are of two types: ‘Report Builder’ designed for end users and ‘Report Designer’ designed for developers.
Report Builder
Report Builder is an ‘ad-hoc reporting tool’, and designed for IT-savvy users to allow them to specify, modify and share the reports they need. It can be run directly from the report server on any PC with the .NET 2 framework installed. It allows the creation of reports derived from ‘report models’ that provide a business-oriented model of the data. These reports can then be managed just like any others. The Report Builder allows the users to specify the way data is filtered and sorted, and allows them to change the formulas of calculated columns or to insert new columns. These reports have drill-down features built into them.
Report Designer
Visual studio has a ‘Report Designer’ application hosted within Business Intelligence Development Studio. It allows you to define, preview and publish reports to the Report Server you specify, or to embed them into applications. It is a different angle on the task of designing reports to ‘Report Builder’, intended for the more sophisticated user who understands more of the data and technology. It has a Query Builder, and expression editor and various wizards. The main designer has tabs for the data, layout and preview.
With the embedded Query Designer , you can explore the underlying data and interactively design, and run, a query that specifies the data you want from the data source. The result set from the query is represented by a collection of fields for the dataset. You can also define additional calculated fields. You can create as many datasets as you need to for representing report data. The embedded Layout Designer allows the insertion or alteration of extra computed columns. With the Layout Designer, you can drag fields onto the report layout, and arrange the report data on the report page. It also provides expression builders to allow data to be aggregated even though it has come from several different data locations. It can then be previewed and deployed.
Model Designer
The Model designer in Visual Studio allows you to define, edit and publish ‘report models’ for Report Builder that are abstractions of the real data. This makes the building of ad-hoc reports easier. These models can be selected and used by Report Builder so that users of the system can construct new reports or change existing reports, working with data that is as close as possible to the business ‘objects’ that they understand. The model designer allows the programmer to specify the tables or views that can be exposed to the users who can then use the models to design their reports. One can also use it to determine which roles are allowed access to them.
Report Management
There are configuration, monitoring and management tools in SSRS which are provided within the Business Intelligence Development Studio.
Report Manager
Report Manager is a web-based tool designed to ease the management task of connections, schedules, metadata, history and subscriptions. It allows the administrator to categorize reports and control user access. The data models that are subsequently used by the ad-hoc Report Builder tool to translate the data into business entities can be edited in this tool. The report portal , which provides the ‘homepage’ for the Report Server, can be edited to create or modify the directory hierarchy into which the individual reports are placed. The RDF files can be uploaded to the report server using this tool and placed in their logical position within the hierarchical menu.
One can create or assign the roles of users that are allowed access the various levels of access to this report. These roles correspond to previously defined groups in the Active Directory. One can specify whether and how often a report should be generated and email the recipients when the report is ready.
SSRS uses role-based security to ensure that appropriate access to reports is properly enforced. It controls access to folders, resources and the reports themselves. With SQL Server Standard and Enterprise editions, one can add new roles, based on Active Directory groups. There are APIs for integrating other security models as well.
Management Studio
The SQL Server Management Studio (SSMS) tool mirrors most of the capabilities of the Report manager with the addition of instance configuration and scripting. Management Studio itself uses RDL files in order to implement the performance Dashboard so as to get reports on the performance of the server itself, and this is easily extended to provide additional reports.
Report Rendering
Viewing Reports on an intranet
When SSRS is installed, it sets up a virtual directory on the local IIS. From there, users with the correct permissions can gain access to whatever reports you choose to deploy. The idea of allowing users to interact with reports and to drill-down into the detail is fundamental to the system, so it is possible to allow users to design their own reports or to use pre-existing ones and to hyperlink between reports or drill down into data to get more detailed breakdowns. SSRS now provides ‘floating headers’ for tables that remain at the top of the scrolled list so one can easily tell what is in each column
Report parameters are important in SSRS. If, for example, the users can choose a sales region for a sales report then all possible sales regions for which data exists are displayed for selection in a drop-down list. This information is derived from the data model that forms the basis for the report.
Reports can be viewed via a browser from the report server, from any ASP.NET website and from a Sharepoint portal.
Reports in applications
One is not restricted to browser-based access of SSRS reports. Any .NET application can display such reports easily. The latest version of SSMS, for example, uses reporting services in order to get performance reports.
There are alternatives. such as using the Web Browser control or the ReportViewer control.
To use the web browser control in an application, all one needs to do is to provide the URL of the report server. The report is then displayed. One can, of course launch the browser in a separate window to display the reports. The URL parameters provide precise control over what information is returned. Using the appropriate parameters, not only can you get the report itself for display, you can also access the contents of the Data Source as XML, the Folder-navigation page, the child items of the report, or resource contents for a report. You can also specify whether it should be rendered on the browser or as an image/XML/Excel file.
The report viewer control, ‘ReportViewer’, ships with Visual studio 2005 and can be used in any Windows Form or web form surface, just by dragging and dropping. After you assign a report url and path, the report will appear on the control. You can configure the ReportViewer in a local report-processing mode where the application is responsible for supplying the report data. In local-processing mode, the application can bind a local report to various collection-based objects, including ADO.NET regular or typed datasets.
One can use the Report Server Web Service to gain access to the report management functionality such as content, subscription and data source, on top of all the facilities provided by using a URL request s. This allows reporting via any development tool that implements the SOAP methods. This Web Service approach provides a great deal of control over the reporting process greatly facilitates the integration of Reporting Services into applications, even where the application is hosted in a different operating environment.
SSRS DataSources and Datasets
SSRS Data Sources
Data that is used to provide the Dataset that forms the basis for a report usually comes from SQL Server, or a source for which there is an OLEDB or ODBC provider. It is possible to create the dataset in another application, even a CLR, and bind it to a report. One can access other data sources, such as an ADO.NET dataset, by using a Custom Data Extension (CDE).
Report delivery can be from a Sharepoint site, using the SharePoint Web parts that are included in the SSRS package.
The information contained within a data source definition varies depending on the type of underlying data, but typically includes information such as a server name, a database name, and user credentials.
Data sources can include Microsoft SQL Server, Microsoft SQL Server Analysis Services, ODBC, and OLE DB, Report Server Model, XML, Oracle, SAP NetWeaver Business Intelligence or Hyperion Essbase
A data source can be contained within a report, or it can be shared by several. In the first case, the definition for a report-specific data source is stored within the report itself, whereas for a shared source, the definition is stored as a separate item on the report server. A report can contain one or more data sources, either report-specific or shared.
SSRS DataSets
A Reporting Services dataset, which is not the same as a .NET dataset, is the metadata that represents the underlying data on a specific data source. It contains a data source definition, a query or stored procedure of the data source and a resulting fields list, and the parameters if any, calculated fields, as well as the collation. A report can contain one or more datasets, each of which consists of a pointer to a data source, a query, and a collection of fields. These datasets can be used by different data regions on the report, or they can be used to provide dynamic lists of parameters.
The datasets used as the basis for reports can come from a wide variety of sources. The examples are mostly queries involving SQL Server base tables, and this has given the impression that this is all that can be used. Reports can, in fact, easily use Stored Procedures to provide the dataset for a report. However, the queries for datasets that fetch the items in the drop-down Parameter lists must be provided too.
Dataset Fields
Each dataset in a report contains a collection of fields. These fields generally refer to database fields and contain a pointer to the database field and a name property but this can be overwritten with a more meaningful name where necessary. These fields can, alternatively, be calculated fields, which contain a name and an expression .
Conclusion
When implementing an application, one ignores Reporting Services at one’s peril. The benefit to almost any application of implementing standard reports from SSRS is immediate and always impressive to end-users. The impact is far greater than the effort involved. One of us (Phil) suffered intense embarassment through believing the users of an application when they said that they would never require interactive reports and only wanted strictly defined and cross-checked standard reports in an application. When someone else implemented both Business Intelligence and SSRS, and gave the users the freedom to explore their own data, Phil was left in no doubt as to his foolishness in having neglected to do so.
There is always a point when developing an application that the standard fare that can be provided by SSRS is not quite enough for the more advanced reporting requirements. However, it is prudent to make sure that all other reporting up to that point is done via SSRS.
The worst mistake of all is dismissing SQL Server Reporting Services as being just an end-user tool for simple reports. Its architecture is such that it forms the basis of an extremely powerful tool for delivering information to users of an application.
Further Reading….
- SQL Server 2005 Reporting Services
- Technologies: Reporting Services
- SQL Server 2005 Books Online: SQL Server Reporting Services
- Configuring Reporting Services to Use SSIS Package Data
- Introducing Reporting Services Programming (SQL 2000)
- Report Definition Language Specification
- Report Controls in SQL Server 2005 Reporting Services
Load comments