Chapter 10: Integrating Reporting Services

This chapter explores the reporting services available to .NET enterprise developers for producing high-quality , interactive reports. This chapter begins with a description of Crystal Reports and its tight integration with the Visual Studio .NET environment. Next , it reviews the report-building process, covering all the steps from designing the report to integrating it in an application. Finally, this chapter puts everything together by creating a new report and integrating it into the IssueTracker application.

Delivering Enterprise Reporting

Enterprise data must be printed at some point, so reporting services are a vital part of any enterprise application. Printed reports need to appear professional with accurate data and clear formatting. Developers can always write code to enable a Windows or Web application to loop through database records and print the results. However, anything beyond basic formatting ”such as data consolidation, multiple-level totals, charting, and conditional formatting ”can become complicated.

The Visual Studio .NET environment comes with Crystal Reports, a sophisticated reporting tool developed by Crystal Decisions. You can use it to quickly create complex and professional-looking reports such as the one shown in Figure 10-1. Crystal Reports is tightly integrated with Visual Studio .NET and provides a visual designer to quickly create and format reports.

Figure 10-1: IssueTracker's Open Issues Summary Report

Benefiting from an Enterprise Reporting Toolkit

An enterprise reporting toolkit such as Crystal Reports offers enterprise developers a number of advantages for quickly building a reporting service. Applications benefit from the option of using a thin Web client or a rich Windows form client to display reports. In addition, Crystal Reports provides report viewers that operate within a Web browser or a desktop application. You can also create interactions between the form controls and the report viewer.

You can specify viewer properties at design time and interact with other controls at runtime. With runtime customization, users can view different reports or change the format, data selection, or export options of an existing report. Finally, developers benefit from the rich object model that Crystal Reports provides. The report object model exposed by the reporting engine allows developers to add code that enables the Windows form viewer control to interact with other user interface controls in the same page. For example, a Windows form viewer can interact with a button or combo box to provide users with export and formatting options.

Summarizing the Report-Building Process

The report-building process happens in four distinct steps: creating the report, layout out the report, implementing the viewer, and distributing the reporting components .

Creating the Report

The entire process starts with the creation of a blank report template. Once you have created the template, you can assign a data source for the report because Crystal Reports connects directly to databases through ADO.NET database drivers. Each driver handles a specific database type or database access technology. To provide the most flexible data access to developers, two different models of data access pull or push values, as illustrated in Figure 10-2.

Figure 10-2: Comparing data push and data pull models

In the data pull model, the database driver connects to the database and pulls the data on demand. With this data model, Crystal Reports handles the connection to the database and the SQL extract command that retrieves the data. This is the default data retrieval methodology.

Alternatively, the data push model requires the application to connect to the database, execute a SQL command to create a RecordSet or DataSet object that matches fields in the report, and pass that object to the report. This data model enables the developer to build connection pooling into the application and to filter the data before Crystal Reports receives it.

Laying Out the Report Template

The next step in building a new report is to lay out the report template. Each report includes database connections, different report sections, and report objects. Using the Crystal Reports designer, you lay out reports graphically in Visual Studio. The designer starts when you add a new blank report to a project.

You need to specify one or more report data sources ” specifically , database tables from which the report will extract data. In most cases, you need to join individual database tables so that you can associate rows in one table with rows in another. The design environment provides intelligent linking of database tables and presents a user interface to manually set table links.

You also need to define and implement report sections. The report designer is divided into several report sections. You implement reports by dragging objects from the Field Explorer into each report section. The report data associated with a report object will vary depending upon the section into which you place the report objects.

Finally, you need to add individual report objects to a report. Their section placement determines their behavior. First, drag a report object from the Field Explorer and drop it into the body of the report. Second, use either the Properties window or the pop-up menu to format the report object.

Report objects come in the following categories:

• Database fields: These are tables and columns that represent the insertion point for extracted data. The tables and columns listed are based on the specified data sources.

• Formula fields: These are user-defined data fields that apply data transformation formulas against other fields.

• Parameter fields: These are user-defined data fields that are set by parameters passed to the report at runtime.

• Group name fields: These are user-defined fields that group all extracted rows together.

• Running total fields: These are user-defined fields that conditionally total the number of extracted records independent of grouping.

• SQL expression fields: These are user-defined data fields that apply SQL statements for optimized data extraction performance.

• Special fields: These are built-in fields that are specific to reports, such as page count, print date, author, and report file path .

• Unbound fields: These are strongly typed placeholders that participate in display formulas.

• Charts: These are graphical representations of extracted data.

• Subreports: These are nested reports that are defined elsewhere.

• Text objects: These are formatted text labels that display static information.

• Line and box objects: These are objects that draw a simple line or box for adding visual effects to a report.

• Picture objects: These are objects that load a picture from a file for display within a report page.

Creating Application Viewers

Both Windows forms and Web forms can contain a report viewer that displays the generated report to the application user. The Windows form viewer supports a number of features, including data export and file format conversion. You can also customize the viewer to add or remove functionality exposed to the user through toolbar buttons . The Web form viewer displays reports in Hypertext Markup Language (HTML) 3.2 and 4.0 one page at a time. You can export a report into different file formats using the Crystal Reports reporting engine.

Application users can set printing properties from a viewer's toolbar or keep the default print settings. Users can also export a report into a number of different file formats. These formats support easy integration into other applications including the following:

• Adobe Acrobat (.pdf)

• Crystal Reports for Visual Studio .NET (.rpt)

• HTML 3.2 and 4.0 (.html)

• Microsoft Excel (.xls)

• Microsoft Rich Text (.rtf)

• Microsoft Word (.doc)

• Plain-text files (.txt)

• Microsoft Exchange public folders

Distributing Reporting Components

The last step of the report-building process is distributing the reporting components. The Crystal Reports engine is distributable as a merge module that can be combined with an application's normal setup program. For Web applications, all reporting elements reside on a single server.