Crystal Reports.NET

Introduction

This article is intended to illustrate how to build reports using Crystal Report.NET. Reports are important components of enterprise applications and are mainly used to provide a descriptive and summary view of your data in order to discover information in huge volume of data and make correct decisions. This is the principles of Business Intelligence in enterprise solutions.

First of all, let's analyze a methodology to develop reports. End-user interviews are the key of targeting the report's content. The end-user tells you how the report should be as a final product and what decisions are made based on the report's information. Then, you have a pretty good idea of what reports are required and how they will be used. You can document these requirements using tools such as user requirement statements and use cases.

Then you have to determine the underlying data sources. Most of the time, the data sources are will be relational database systems, but some times the data could reside on spreadsheets, flat files and OLAP data structures. Once you have found the data sources, you must determine the tables and views to be consumed. Sometimes, you have to develop additional views and stored procedures to consolidate the data prior to develop the report.

Next step is to design the report and set the required format for fields as well as the totals such sub-totals and grand-totals. It should closely match the report's final layout. You must check with the end-users and business analysts.

Finally, the report design must be bind with the underlying data source in order to generate the final report output. This step is realized by the report engines such as Crystal Reports and Reporting Services.

Getting started with Crystal Reports.

Let's create a Windows Forms application to display summary information about product profitability. The data source is the AdventureWorks database shipped with SQL Server 2005 (see Figure 1).

Figure 1

First step is to add a strongly typed data set to store the result set from the following query (see Listing 1).

select ProductID, [Name], ProductNumber, StandardCost as Cost, ListPrice as Price, ListPrice-StandardCost as Profit
from Production.Product;

Listing 1

Let's add a Crystal Report to our application. Select Project | Add New Item and choose Crystal Report item from the list of available templates. Enter ProductProfitability as the report's name. The Crystal Reports Gallery will appear (see Figure 2).

 
Figure 2

There are three options. Using the Report Wizard option enables to walk step by step through the creation of the project. As a Blank Report option is for experienced developers who are familiar with the Report Designer. And From an Existing Report option enables to select a report's template as the basis. We're going to select the As a Blank Report option.

Now let's add the data source. Right-click on the report and select Database | Database Expert. The Database Expert windows will appear (see Figure 3). There are different types of data sources.

Figure 3

Then the created strongly typed data set dtProductProfitability from Available Data Sources pane into Selected Tables pane (see Figure 4).

Figure 4

Let's go to the design of the report. By default, Crystal Reports .NET will have a number of properties preset such as font, formatting for fields, page size, margin and layout.

A Crystal Report comprises a number of sections. Each section has a set of properties and behaviors. If you want to see the Section Properties, right-click on the report and select Section Expert (see Figure 5).

Figure 5

We can find several sections. Report Header appears at the top of every first page of the report and usually suppressed by default. The Report Footer appears at the bottom of every last page of the report. Page Header appears at the top of each page and it's mainly used as for column headings, the report title, page count, etc. Page Footer appears at the bottom of each page and it's mainly used to display page numbers, print dates, times, grand totals and so on. Group Header appears at the head of each group. Group Footer appears at the end of each group and it's mainly used to display the subtotal and summaries associated to the group. Details Section, there's one for each record of the report and they're mainly used to display record information.

Report formatting is the most time consuming task of report design. You can change the format of each object in the report in order to control the color, font as well as data type formatting such as number, currency and date.

Let's add a Name Report and page counter to the report header. Right-click on the report and select Insert | Text Object and Insert | Special Object | Page N of M.

Now we're going to add the fields to the report. To do so, open the Field Explorer and drag and drop the fields onto the Details Section in the report design surface.

Let's do some fields formatting. Right-click on the ProductID field on the Details Section and select Format Object and then select the number format (see Figure 6).

Figure 6

Do the same step for the Cost, Price and Profit fields, but this time we format the number with decimal fractions and thousand separators (see Figure 7).

Figure 7

Finally, we're going to add grand totals to our report. Right-click on the report and select Insert | Summary, then the Insert Summary windows will appear. Then select the field to be summarized, in our case we select Product.Profit, the type of calculation and the location, in our case the scope of the summary is the report, but you can limit the scope to groups.

Now the report is created. In order to show the report, we need to add a CrystalReportViewer from the Toolbox on the main form design surface. Now tie the previously created report to the report viewer (see Figure 8).

Figure 8

Now, finally add the code to fill the dataset and bind this dataset to the Crystal Report document (see Listing 2).

this.m_taProduct.Fill(this.m_dsProductProfitability.Product);
            this.m_rpProductProfitability.SetDataSource(this.m_dsProductProfitability.Product as DataTable);

Listing 2

Conclusion

In this article, I covered the principles of CrystalReports technology walking step by step through an example. Now you can adapt this solution to your own business scenarios.