How to integrate your reports into Windows and Web applications
SQL Server 2005 Reporting Services (SSRS) lets you create a central repository for sharing reports across an enterprise. However, there are times when you might need more functionality. For example, you might need reports that are tightly integrated with an application (e.g., reports that are distributed as part of an application, reports that appear in an application's window).There are also times when you might need reports that are less centralized, perhaps for a stand-alone, custom application or an environment in which connectivity to a report server isn't available. The ReportViewer control in Microsoft Visual Studio 2005 is the key to all this functionality.
The ReportViewer control is a flexible tool that you can use in Windows and Web forms to display two types of reports. The first type is a server report, which requires an SSRS report server. The report server provides the values for the report parameters, creates the data sets, and renders the report. The ReportViewer control then displays the rendered report within an application window. When you create server reports, you're using the remote processing mode.
The second type is a local report. Local reports are distributed within an application and don't require an SSRS server. However, freedom from the tether to a reporting server comes at a price.The application must provide the values for the report parameters and create all the data sets. The ReportViewer control renders the report. When you create local reports, you're using the local processing mode.
Creating a Server Report
Using the ReportViewer control in the remote processing mode is by far the simpler of the two modes of operation. You just need to create an instance of the control in an application form and point it at a report residing on an SSRS report server. Here's the step-by-step procedure for creating a server report:
- Place a ReportViewer control in the Windows or Web form.
- Open the ReportViewer Tasks dialog box by clicking the small black triangle in the upper right corner of the Report Viewer control. In this dialog box, select <Server Report> from the Choose Report drop-down list. This selection changes the options in the ReportViewer Tasks dialog box to those that Figure 1 shows.
- In the ReportViewer Tasks dialog box, enter the URL to the report server's Web service in the Report Server Url text box. In the Report Path text box, enter the pathname to the target report. The pathname must start with a forward slash (/) to represent the home folder on the report server. This path is also used when rendering a report through the report server's Web service or through URL access to the report server.
That's all there is to it. When the Report-Viewer control loads, it requests the report from the report server's Web service. The report is rendered in a format known as Remote Graphics Device Interface (RGDI), which is the same format used for ad hoc reports displayed by SQL Server 2005's Report Builder. The RGDI format is for internal use by ReportViewer and Report Builder. You won't see it in the Export dropdown list in SSRS's Report Manager because its visibility is set to false in the report server's configuration file.
When you run the application, the report appears in the viewer just as it would appear in a browser if you were using Report Manager. Figure 2 shows a sample server report. If the report has any parameters, you'll be prompted for their values. If the report supports any navigation features (e.g., drill-down), you can use those navigation features.
Creating a Local Report
To use the ReportViewer control in the local processing mode, you must create a local report, along with its data set, in your Windows or Web form. Here's the step-by-step procedure:
- Place a ReportViewer control in the Windows or Web form.
- Open the ReportViewer Tasks dialog box. Click the Design a new report link, which creates a report named Report1.rdlc. Open this file in the report-designer window.
- Click the Add New Data Source link in the left pane to bring up the Data Source Configuration Wizard. The wizard asks you where you want the report to get its data from. You can use a database, a Web service, or a Microsoft .NET object as the data source for your report. For this example, leave the default Database option selected and click Next.
- Define the data connection that the data source will use. Select an existing data connection if one has been defined previously, or click the New Connection option to create a new data connection. If you're defining a new connection, complete this process. Click Next when you're done.
- Select whether you want to store the connection string information in the application's configuration file or whether you want to leave the information embedded in the code. Saving the connection string in the configuration file provides more flexibility because the database environment can change. After you specify the connection string's location, click Next.
- Select the items from the data source you want to include in the data set. When you use a database as a data source, the items can include tables, views, stored procedures, and functions. Not only can you select the database objects but also specific fields from those objects, as Figure 3 shows. After you select the database items, click Finish. The wizard then creates a typed data set, which shows up as an XML Schema Definition (XSD) item in the Solution Explorer window. This typed data set serves as the data source for the report.
- With the data set available in the Data Sources window, you can create the report just as you would create any other SSRS report. The only difference is that the report designer doesn't include a Data tab or Preview tab. The data comes from the typed data set, so there's no need for a Data tab. Because the report is previewed by executing the application, there's no need for a Preview tab. (Note that the example here doesn't support report parameters or subreports. I'll show you how to use them in Part 2.)
- After the report is complete, return to the form that contains the Report-Viewer control. To assign the new local report to ReportViewer, select the report from the Choose Report dropdown list in the ReportViewer Tasks dialog box. You'll see that the wizard has added DataSet, BindingSource, and TableAdapter objects to the form-design window. These objects provide the data for the local report.
When you run the application, the local report appears in a ReportViewer window, as Figure 4 shows.This report is added to the project as a Report Definition Language Client (.rdlc) file because it's a client-side report. The .rdlc file is almost identical to the standard Report Definition Language (.rdl) file that you can create and deploy to an SSRS report server. The only difference is the rd:DataSetInfo section in the .rdlc file. Figure 5 shows this section, which Visual Studio 2005 uses to add objects and generate code inside the form hosting Report Viewer. The rd:DataSetInfo section provides the information needed to create and fill the DataSet, BindingSource, and TableAdapter objects and link them to the local report.
By default, local reports are compiled right into the application's .exe or .dll files. This feature makes the reports self-contained and easy to distribute. However, there are two possible drawbacks. First, you need to create a ReportViewer control for each local report that you want to make available to end users. Second, revising an existing report or adding a new report to an application requires that you recompile and redistribute that application. Thus, if you need to create numerous reports or if you have a dynamic reporting environment, using the local processing mode might not be desirable.
The ReportViewer control is a powerful tool for integrating reports into Windows and Web form applications. This control lets you easily display reports from an SSRS report server. It also provides an easy, wizard-driven method for creating local reports that are compiled and distributed with your application. You can do even more if you're willing to write a few lines of Visual Basic .NET or Visual C# code. As you'll see in Part 2, you can, for example, store local report definitions outside the application so that you can add new reports or update existing reports without having to recompile and redistribute the application. You can also support report parameters and subreports.