Integrating Reporting Services into Your Application

Reporting Services is a comprehensive platform for creating, managing, and delivering traditional, paper-oriented reports and also interactive, Web-based reports.

Reporting Services was designed from the start to be extensible, with open interface and programmatic access to support a wide range of environments and applications. Application developers can access this functionality in a number of ways, including accessing existing reports, generating custom reports and report controls, embedding reports locally within applications, and executing reports remotely.

By taking advantage of the functionality and open interfaces of Reporting Services, you can easily provide robust reporting capabilities while focusing on your application’s unique functionality.

This paper discusses the integration points that are available with SQL Server 2005 Reporting Services.

Creating Reports

You can create reports for your application by using a variety of tools. These include the Report Designer tool integrated with Visual Studio 2005, or the new Report Builder ad hoc tool, and then deploy them as part of your application installation. Your users can also use these tools to extend the set of reports that you provide with your application.

Embedding Reports

Perhaps the most common application integration requirement is the ability to embed reports or the ability to access reports from within an application. The first version of Reporting Services provided two methods for accessing reports that resided on a Report Server. These were URL access and an XML Web service, the Reporting Services SOAP API. SQL Server 2005 Reporting Services expands the options for embedding reports. These include a set of Report Viewer controls for Windows Forms and ASP.NET applications, and also Web parts that make it simple to navigate, select, and view reports in Microsoft SharePoint portal pages.

Managing Reports

Reporting Services offers a full-featured management interface using the Reporting Services Management Web service (a SOAP API). The Management Web service can display reports and manage rendering, subscriptions, and other aspects of the report server, programmatically from an application.

Generating Reports Programmatically

While many applications provide predefined reports, it is also possible for an application to generate report definitions automatically by writing to the published XML schema for Reporting Services reports. After a program has created the report definitions, the Reporting Services Execution Web service provides a programmatic interface to the report execution and rendering capabilities of a Report Server.

Integrating Ad Hoc Report Generation

The Report Builder tool lets end users and business analysts create and design reports by accessing a data model that presents the underlying data sources in a business perspective. Third-party applications can access the Report Builder. Reporting Services provides a model design tool for creating the Report Builder data models.

Extending Reporting Services

The modular architecture of Reporting Services is designed for extensibility. A managed code API is available so that you can easily develop extensions consumed by many Reporting Services components. By using the Microsoft .NET Framework, you can create custom assemblies, custom report items, and also new Reporting Services security, delivery, rendering, and data processing functionality to meet your evolving business needs.

The remainder of this paper describes these methods in detail.

Creating Reports

Report design is available within a Report Server project that is included in SQL Server 2005 and also integrated with Visual Studio 2005 language projects. The report design process is a graphical surface that is modeled after the Windows Forms editor.

Reports may be tabular, matrix, or freeform, and may contain rich charts. You simply drag and drop a field from the Data Sources window onto the design surface and then set the desired style properties. The Report Designer, shown in Figure 1, lets you access the full capabilities of Reporting Services reports, including the grouping, sorting, filtering, and conditional formatting features.

Figure 1. Visual Studio Report Designer

When you create reports, you can choose to either publish them on a Report Server or embed or access them locally from the application. Both of these options are described in the following subsections.

Hosted Reports

SQL Server 2005 includes a Report Server Project in the SQL Server 2005 Business Intelligence Development Studio for designing reports that will be hosted on a Report Server. Some of the benefits of deploying hosted reports include security, caching, scheduling, and delivery.

Hosted reports use the standard report definition format (RDL). This format contains information about how to connect to the data source and extract appropriate data.

Embedded Reports

Visual Studio 2005 includes the ability to design reports and embed them directly in any language project, including Visual Basic, Visual C#, Visual J#, or Managed C++.

The embedded Report Designer has the same functionality as the Report Server Designer included in SQL Server 2005, but uses the Visual Studio data source functionality to access data for the report. Reports can access traditional data sources or object collections.

Embedded reports use a report definition version (RDLC) that includes metadata about the data sources, but does not contain connection or query information. This is described in detail in the next section.

For more information about designing reports, see SQL Server Reporting Services Books Online.

Embedding Reports with the Report Viewer Controls

Visual Studio 2005 comes with freely redistributable Report Viewer controls for embedding Reporting Services functionality into custom applications. These controls require that Microsoft .NET Framework 2.0 is installed on the target machine.

There are two versions of the Report Viewer control: one for Windows clients and one for Web-based (ASP.NET) applications. The Visual Studio toolbox automatically provides the right control, based on the type of project you are creating.

The Report Viewer controls access reports on a Report Server or process and renders reports locally in the following ways:

In local mode, the application provides the report definition and datasets and triggers report processing. No SQL Server license is required and the necessary processing functionality is included within the Report Viewer control.
In Report Server mode, the Report Viewer control accesses a report hosted on the Report Server. The control is used to navigate and display the report. Report Server mode requires a SQL Server 2005 Report Server.

These distinctions are explained in detail in the following subsections.

Local Mode vs. Report Server Mode

The first important decision you have to make is whether to use the local or Report Server mode for the Report Viewer control. Your decision will probably depend on how your application will be deployed.

In local mode, as shown in Figure 2, the local application processes the reports from the report definitions that are either embedded in the application or loaded from disk. The application makes no connections to a Report Server. In fact, this approach does not require a SQL Server license or Report Server.

Figure 2. Local Mode Report Processing

In Report Server mode, shown in Figure 3, the application accesses a report published on a Report Server. The Report Server performs all data retrieval, processing, and rendering, and the control displays the results.

Figure 3. Report Server Mode

The Report Server offers a managed reporting environment that includes security, subscriptions, snapshot management, and report history. These services may be essential for enterprise-scale reporting environments.

There are other, more subtle distinctions you should be aware of when deciding which mode to use. These are summarized in Table 1.

Table 1

Category	Local Mode	Report Server Mode
Data Sources	Visual Studio data sources, including ADO.NET DataTables or application objects. The application must connect to the source for data.	Any data sources accessible from the Report Server. This includes a wide range of supported sources.
Report Definition	Embedded locally or loaded from disk or stream.	Published to Report Server.
Parameters	The application must implement the user interface for specifying parameters or queries.	Report Viewer control can prompt for query or report parameters.
Security	The application must manage security. Code embedded in a report cannot access the file system or network without explicit permission.	Report Viewer control prompts for credentials.
Export Formats	Microsoft Excel and PDF only.	All rendering formats supported by Reporting Services, including Excel, PDF, and MHTML.
SQL Server Licenses	None required.	One required for Report Server.

The report processing should be identical between the Report Server and local modes, because the Report Viewer control uses the same reporting engine as the Report Server. Both reports support interactivity, such as expanding and collapsing sections, drill-through, and interactive sorting, and a wide range of data layouts such as tables, lists, and charts.

Local mode does not support Custom Report items.

Changing from Local to Report Server Mode

The decision to choose local mode over Report Server mode is not irreversible. You can migrate reports from the local RDLC format to the hosted RDL format.

If the report uses a data source type directly supported by the Report Server, such as SQL Server, you can supply the missing information and then publish the report on the Report Server. If the report retrieves data from data sources not directly supported by the Report Server, you may have to provide data processing extensions in order for the Report Server to retrieve the data.

After the report is published to the Report Server, you only have to update the Report Viewer control with the report path and Report Server information needed to access the report.

Generally, if your report uses application data that is not stored in a database or is not accessible by using a Web service or other remote API, migrating from local to server mode will not be possible without building a data processing extension. This is described later in this paper.

Integrating Reports in a Windows Forms Application

To integrate a report by using the Report Viewer control in local mode (creating and generating the report locally), you can use the following steps.

To create the data sources

Launch the Data Source Configuration Wizard to create data sources from databases, Web services, objects, or local files.
The wizard creates a DataSet that contains the DataTables you have specified. Alternatively, you can use the TableAdapter Configuration Wizard. This allows you to use a query to create a DataSet.

To design the report

Use the Report Designer that is integrated with Visual Studio to define the report.
You can drag and drop fields from the Data Sources window onto the report items. Report Designer automatically puts the appropriate data source information into the report definition (RDLC) file for the local report.

To add the Report Viewer control to the Windows Forms application

Drag the Report Viewer icon from the toolbox to the Windows Forms design service. The SmartTags panel is automatically displayed.
Select the report to bind to the Report Viewer control.

Note that the order of these steps is flexible. For example, you can add the Report Viewer control to the application and then launch Report Designer to design the report.

To add a remotely hosted report to a Windows Forms application, you simply add the Report Viewer control to the application and, from the SmartTags panel, select the Report Server URL and the path for the report.

Integrating Reports in an ASP.NET Application

The process for integrating a report into an ASP.NET application is similar to that for a Windows Forms application. Following is the general process for embedding a local report.

To create the shared data sources

The data sources can be created as either DataComponents, by using the TableAdapter Configuration Wizards, or by using custom classes.

To design the report

Report Designer displays the shared data sources in the Data source window. Drag and drop data and report components to design the report.

To add the Report Viewer control to the Web page

Drag and drop the Report Viewer icon onto the WebForm design surface. The SmartTags panel for the control is automatically displayed.
Select the report you have designed and then select the data sources for the report.

If you are embedding access to a report hosted on a Report Server, all you have to do is add the Report Viewer control and, from the SmartTags panel, enter the URL and path for the report on the Report Server.

Embedding Reports in SharePoint

Reporting Services provides two Web components for embedding reports in Windows SharePoint Portal Server 2003 or Windows SharePoint Services environments. These include Report Explorer and Report Viewer:

Report Explorer navigates the Report Server to find available reports and manage report subscriptions.
Report Viewer lets users view and work with reports.

Separately or together, these two Web components let you easily leverage the capabilities of Reporting Services. For example, you can use these to do the following:

Automatically display a specific report on a portal page.
Give the portal user access to browse, select, and display available reports on the Report Server.
Give the portal user the ability to subscribe to a report on the Report Server.

Report Explorer Web Component

The Report Explorer Web component is essentially a scaled-down version of the Report Manager that is included with Reporting Services. The Report Explorer Web component provides the capability to do the following:

Browse available reports on the Report Server
Select a report to view
Subscribe to reports

The Explorer Web component can be used in conjunction with the Report Viewer Web component. When run in connected mode, the two Web components pass data to each other. In stand-alone mode, they do not.

If you run Explorer and Viewer in connected mode and select a report to view, this will automatically display the report in the Viewer Web component. If you run Explorer in stand-alone mode and select a report, this opens a new browser window to display the report. You will have to decide which approach is best for your application.

Report Viewer Web Component

The Report Viewer Web component displays the report. Users can view and navigate multi-page reports, print the report, or export the report to supported formats. The administrator can decide whether the report viewer toolbar is displayed with a minimal set of controls or none at all.

Managing Reports with the SOAP API

SQL Server 2005 Reporting Services includes a Reporting Services Management Web service for programmatically managing the Report Server. The Web service offers a single-entry point to the full report management functionality of the Report Server and can be used to perform a wide variety of tasks. This includes the following:

Browsing server contents
Publishing and removing reports
Managing snapshots and report history
Managing subscriptions

Reporting Services also offers a Windows Management Instrumentation (WMI) provider. This is a programmatic interface that you can use to build custom Report Server administration tools.

Distributing Reports with an Application

As part of your application installation or configuration, you may want to deploy a set of standard reports that your users can use. You can use the management Web service to deploy these to the Report Server.

In addition to deploying the reports, you will want to change the data sources for the report so that it points to the local installation. The simplest way to do this is to define the report by using shared data sources. Your deployment utility that publishes the reports to the server can also set the appropriate connection strings for the data sources.

Generating Reports Programmatically

Reporting Services offers several methods for generating reports interactively. It also supports programmatic report creation and execution, with a documented, extensible XML-based report format (RDL) and with SOAP interfaces for report execution.

Writing Report Definition Files with RDL

Reporting Services uses a published, extensible XML schema called Report Definition Language (RDL). The RDL format covers all aspects of the reports, including data retrieval, expressions, and layout.

You can use the expression capabilities of RDL to support dynamic content within reports, designing conditional formatting and drill-down links. However, there are a few applications that have to generate an entire report dynamically by writing the RDL. There are specific ways to generate RDL programmatically.

One way to generate RDL from an application is to use the Microsoft .NET Framework classes of the System.Xml namespace. The XmlTextWriter class can write RDL according to the specification. However, you can generate a complete report definition in any Microsoft .NET application.

Because RDL is an open schema, you can extend RDL with additional attributes and elements. You can even include custom report controls and elements that are not included with RDL and embed code inside the report definition.

For example, you can create and use maps, barcodes, and media clips within reports, and add the custom report controls to the Microsoft Visual Studio Toolbox. Custom report controls have their own properties and dialog boxes and use the expression evaluation, grouping, sorting, and filtering features of the Report Processor.

For information about the Report Definition Language Specification, see the Reporting Services Web site [ http://www.microsoft.com/sql/reporting/ ] .

Dynamic Report Execution

Typically, to execute a report from the Report Server, you publish the report to the server and have the server execute and render the report. The report then resides in the Report Server report catalog.

SQL Server 2005 Reporting Services offers a Reporting Services Execution Web Service for programmatic control over report processing and rendering on the Report Server. By using the classes and methods of this Web service, you can direct the Report Server to do the following:

Process and render a report from a report definition file
Render a report from a history snapshot
Execute server-based reports

The classes and methods are encapsulated in the ReportExecutionService class.

Integrating Ad Hoc Report Authoring

SQL Server 2005 Reporting Services includes a new ad hoc, report-authoring tool called Report Builder. In using Report Builder, business users and analysts can create reports by dragging and dropping data items onto a report layout. Because Report Builder uses predefined Report Models to access data sources, you will want to provide your end users with already built Report Models to enable ad hoc authoring.

Building Report Models

To provide ad hoc report design for application users, you have to define and publish the report models used by Report Builder. These models provide a business-level description of the underlying database. As a result, Report Builder users do not have to understand the source data structure in order to create meaningful reports.

Microsoft provides a Report Model Designer tool to define, edit, and publish report models for Report Builder. You can use this tool to design the model interactively by using the data source. Models can also be generated automatically for Microsoft SQL Server or Analysis Services databases.

You can override the default experience when users drill from one entity to another by using drill-through reports. These provide a customized experience and generally make your out-of-the-box applications function better.

For more information about creating report models, see SQL Server 2005 Books Online [ http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx ] .

Launching the Report Builder Client

Report Builder is a ClickOnce Windows Forms application that is accessible from the Report Server. You can access or launch Report Builder through a URL to provide integrated, ad hoc reporting.

Extending the Report Server

The uniquely extensible architecture of Reporting Services enables developers to extend specific features of the product and its components.

Extension Types

The types of extensions that are supported in SQL Server 2005 Reporting Services are shown in Table 2.

Table 2

Extension	Report Server Mode
Data Processing Extension	Data processing extensions enable developers to build additional data source types into Reporting Services. These data processing extensions add functionality to both the Report Server and Report Designer.
Delivery Extension	Delivery extensions allow the use of a wide variety of mechanisms when sending report notifications to users. You can extend the Report Server to provide custom delivery to users and you can extend the subscription management pages of Report Manager to enable subscriptions that use custom delivery extensions.
Rendering Extension	Rendering extensions transform report data and layout information into a device-specific format. You can create additional rendering extensions to generate reports in other formats that are not supported.
Security Extension	Security extensions enable the authentication and authorization of users in Reporting Services. By default, Reporting Services uses a Windows-based security extension to authenticate the identities of users on the system. You may have to replace the default security to accommodate custom security in your enterprise.
Custom Report Items	Custom report items allow developers to define additional item types that can be used within their reports. Custom report items include an interface that allows a design-time control to be hosted in the Visual Studio report design tool. Custom report items also include a run-time interface that the custom report item uses to convert report data and properties into an image to display in the rendered output.
Custom Code Assemblies	Custom code assemblies are referenced from within your report definition files and contain specialized functions that you can use in the expressions in your reports. The server calls the functions in your custom assemblies when a report is run.

More information about extensions, including their programmatic interfaces, is included in SQL Server 2005 Books Online.

Conclusion

SQL Server 2005 Reporting Services offers a wide range of integration points and makes it easy for developers to take advantage of the product’s comprehensive report generation, processing, rendering, and distribution capabilities.

For detailed information about these programmatic interfaces to SQL Server 2005 Reporting Services, see the SQL Server 2005 Books Online [ http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx ] .

For more information

http://www.microsoft.com/sql/ [ http://www.microsoft.com/sql/default.mspx ]

Contents

Introduction

About This Document

Prerequisites