Crystal Reports and Reporting Services


Introduction

This article is intended to illustrate and discuss the development of reports using Crystal Reports (CR) and Reporting Services (RS). Although CR and RS are two different technologies, the produce the same results: a report. I will cover a side-by-side comparison and development of CR and RS reports.

It's remarkable to say that while a Crystal Report is the most used general-purpose reporting tool, Reporting Services is much easier to use because you can have a chart as well as a table in the same report section.

Developing reports in Crystal Reports and Reporting Services

We're going to develop a product profitability report which displays the information about products (cost, price and profit) stored in the table Production.Product in the AdventureWorks database. The underlying SQL query is show in Listing 1.

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

Listing 1

Please, open Visual Studio.NET and create a Windows application project named. Because I follow the Model-View-Controller (MVC) paradigm in my solutions architecture, we're going to add a class library where the data-access layer components will reside. Go to the File | New | Project from the main menu and then add new Class Library project to our solution (see Figure 1).


 
Figure 1

Let's move on and add a strongly typed dataset to this Class Library project representing the reporting query resultsets (see Figure 2).


 
Figure 2

Now let's add a table adapter and the underlying data table according to the query in Listing 1 using the TableAdapter Configuration Wizard (see Figure 3).


 
Figure 3

Now that we have developed the data-access layer component, then we move to the presentation layer. We're going to add two Windows Forms: one to display the CR report and the other to display the RS report. Then add the ReportViewer control to the RS Form by dragging Data|Report Viewer from the the toolbox and dropping it onto the design surface and finally dock this control to the form (see Figure 4). Similarly, add a CrystalReportViewer control to the CR Form by dragging Crystal Reports|CrystalReportViewer from the toolbox and dropping it onto the design surface and finally dock this control to the form (see Figure 5).


 
Figure 4


 
Figure 5

Now it's time to design the report layout. The report will show the name of report and company in the report header section and will display the list of products item (in the detail section).

Let's start by adding the RS report by right-clickling on Windows project in the Solution Explorer and selecting Add | New Item, and finally select Report from the Add New Item dialog box (see Figure 6).


 
Figure 6

In RS, you don't see the header and footer by default. Thus select Report|Page Header from the main menu in order to make visible the Page Header of the RS report.Add two Textbox control to the page header and a Table control to the header section from the Toolbox. Configure the Table to have six columns, each column of the table for each column of data. Then to add data field to the Table, we need to reference the definition of ProductProfitability dataset class. Go to the Data Sources windows and add a new object data source which references ProductProfitability dataset class (see Figure 7).


 
Figure 7

Now we can drag-and-drop data field from the definition of ProductProfitability dataset class in the Data Sources window into the report (see Figure 8).


 
Figure 8

Now add the fields to the Table Details and format the Table Header as bold and with a bottom line (see Figure 9).


 
Figure 9

Now let's add the CR report. In the Solution Explorer, select the Windows project and right-click on it and select Add|New Item, and select from Add New Item dialog box (see Figure 10).


 
Figure 10

When you add the CR report, then Crystal Reports Gallery dialog box will appear. Then select As a Blank Report option. You can see a new Toolbox named Field Explorer which displays the information available to design the CR report.

When you look around the two reports, you discover that the RS reports are free form in nature, so you can add the sections you need. In CR, you have by default headers and footers, even if you don't need them. You must notice that the CR has two sets of headers and footers: one for the page and other for the report as whole. We don't need to add a header or footer to the report, then drag-and-drop two Textbox items from the Toolbox onto the design surface of the header report.

Let's work on the detail (body) section of CR report now. In CR, we need to reference the data sources specifically the type data set. This step is very easy, just right-click inside any section, and select Database | Database Expert. Then the Database Expert dialog box will appear, and you must expand the node Create a New Connection | ADO.NET and a new ADO.NET dialog box will appear. You must browse to the place where the definition of you strongly dataset resides (inside the Class Library project, please see Figure 11).


 
Figure 11

Then click on the Finish button on the ADO.NET dialog box (see Figure 12).


 
Figure 12

Then on the Database Expert dialog box, you need to select the newly referenced dataset (see Figure 13).


 
Figure 13

Now mapping the newly referenced fields onto the detail section is very easy too. Drag and drop the fields from the Field Explorer window onto the details section of the report. You'll notice that as soon as you drop the field, the respective column header automatically appears in the page header section (see Figure 14).


 
Figure 14

Now let's configure the Report Viewers to bind the underlying data source to report definitions.

Let's begin with the RS reports. In this case, it's very simple. Open the RS Form and click on the smart tag of the ReportViewer control in order to select a RS report and bind the report data source with your application data source. When you select the RS report definition, then VS.NET also adds a binding source object to the Windows Form design surface. Now add an instance of ProductProfitabilityDS strongly typed dataset and an instance of ProductProfitabilityTableAdapter table adapter from the Toolbox onto the design surface (see Figure 15). 


 
Figure 15

Finally reference the former dataset from the binding source object (see Figure 16).


 
Figure 16

The last step is to fill the instance of ProductProfitabilityDS strongly type dataset with data.

Now let's configure the CrystalReportViewer control. Open the CR Form, and click on the smart tag of the CrystalReportViewer control and select Choose a Crystal Report option (see Figure 17).


 
Figure 17

After this, a new ReportDocument object is added to the design surface. Now add an instance of ProductProfitabilityDS strongly typed dataset and an instance of ProductProfitabilityTableAdapter table adapter from the Toolbox onto the design surface (see Figure 18).


 
Figure 18

The last step is to fill the instance of ProductProfitabilityDS strongly type dataset with data and bind this data source to the ReportDocument instance (see Listing 2).

this.productProfitabilityTableAdapter1.Fill(this.productProfitabilityDS1.ProductProfitability);
this.ProductProfitability1.SetDataSource(this.productProfitabilityDS1.ProductProfitability as DataTable);

Listing 2

Conclusion

In this article, I discussed the two most important reporting technologies in the .NET world. I developed a simple report and explained the main step to achieve this goal using Crystal Reports and Reporting Services.