Crystal Reports is officially a member of the Visual Studio .NET product.

It is included in all major editions and ships in all languages available with Visual Studio .NET. Crystal Reports .NET provides developers with the fastest, most productive way to create and integrate presentation-quality, interactive reports that scale to meet the demands of end users. This article introduces you to Crystal Reports .NET and shows you how to create reports and view them in either a Windows form or a Web form. I will also show you how to publish a report as a Web service and consume the service in a Web form.

There are a wide variety of available sources for feeding data into your report. You can use an OLE DB provider, ODBC data source, ADO.NET dataset, and even an XML Web service.

Crystal Reports .NET is a very powerful report writer. It offers a very well defined object model that provides flexibility when integrating reports into Windows and Web applications. It also comes with a highly customizable viewer component that gives developers control on how their reports are presented to users. This article examines some of the new features of Crystal Reports .NET and shows you how to integrate a report into your application. After creating this report, you will learn how to publish its content to the Web as a Web service.

New Features in Crystal Reports .NET

Integrated into .NET IDE

Crystal Reports .NET has been completely re-written from the ground-up in C#. It is tightly integrated into the Visual Studio .NET IDE. Developers no longer have to switch to a different application to create and edit reports.

Crystal Reports Viewer

Crystal Reports .NET provides developers with two different report viewing components. The first can be used for displaying reports in Windows forms. The other is a component that can be used to display reports in a Web environment. The beauty of these viewers is in their simplicity. Viewing a report is as simple as setting the ReportSource property of the respective viewer.

Allowing users to interact with the report

Developers can give users the ability to interact with their reports object via a programmable API. Developers can change report properties by calling methods or setting properties of the viewer object.

ADO.NET Integration

ADO.NET is now the lingua-franca of data interchange in the .NET world. Crystal Reports .NET provides the ability to use ADO.NET datasets as the data source for reports.

Expose Reports as Web Services

Reports can be exposed as Web services. This adds additional power to Web service applications. Exposing reports as Web services enables developers to offer real-time information (in the form of a report) as opposed to real-time data!

Designing and Viewing a Report in Crystal Reports .NET

The major focus of this article is to show you how to create a complex report using Crystal Reports .NET and SQL Server 2000. The report will use data from the SQL Server database called Northwind. The specification for this report is:

1. 1. Show a list of all Products grouped by Category.
2. 2. Display the Quantity on Hand, Unit Price, Re-order Level and value of each product.
3. 3. Show a count and total value of all products in stock by category and an overall count and total value.
4. 4. Highlight all products where the quantity on hand has fallen below the re-order level.
5. 5. Limit the report products that have not been discontinued.

As you can see, this set of requirements will demonstrate the majority of features necessary to create production quality reports.

Creating a Report

The first step to creating a new report is to create a Visual Studio .NET application for your report. Create a new Windows project by selecting Windows Application from the template dialog. You can choose to create a project using C# or Visual Basic .NET.

Once you have created your new project, right-click on the project in the Solution Explorer and choose Add, New Item. In the Templates pane of the Add New Item dialog, scroll down until you see Crystal Report. Select it and enter a filename for your report.

In the Crystal Reports Gallery dialog (see Figure 1), select As a Blank Report and click OK. Crystal Reports does have a report wizard that can be used to create your report. However, I find that I spend more time fixing up the report once the wizard has created it, than I would have by creating the report from scratch. Once you have created your new item you can use the Crystal Reports .NET designer (Figure 2),

Select data source

There are several different choices for connecting your data source. This example uses the SQL Server OLE DB provider.

There are two versions of the Crystal Reports Viewer component: one for viewing reports on a Windows form and one for viewing reports on an ASP.NET Web form.

To select the data source, right-click on the report design surface and select the Database, Add/Remove Database options. The Database Expert dialog displays. Click the OLE DB (ADO) node in the Available Data Sources pane of the dialog. Now a dialog displays allowing you to select the OLE DB Provider. I am going to use the Microsoft SQL Server Provider for this sample.

After selecting the Microsoft SQL Server Provider and clicking Next, a form displays, allowing you to specify your connection information (Figure 3) for attaching to your database server.

After specifying your connection options, you will see the Crystal Reports Database Expert. This is where you specify the tables or views to include in your report. For this report you need the Products and Categories tables. Figure 4 shows the selected tables. After selecting the tables, click the Links tab to specify how the tables should be linked. Crystal Reports attempts to automatically link your tables. If it does not link them correctly, you can use this dialog to correct any linking issues. In this example, they are linked correctly. Once the tables have been selected and linked, you can begin placing fields on your report.

Place fields

Once tables have been added to a report, they are displayed in the Field Explorer, shown in Figure 5. Each table can be expanded to show a list of fields in the table. To add fields to the report, all you need to do is to drag and drop the appropriate field from the Field Explorer onto the design surface.

When a field is placed in the detail band on the report, a label is automatically added to the Page Header band. By default, the name of the field is used as its label. A label can be changed by simply double clicking on it and changing its contents.

According to the requirements, our report needs to show the total value for each product. You do this by adding a calculated field to the report. This calculated field will multiply the Quantity On Hand with the Unit Price.

To insert a formula field, right-click on the Formula Field node in the Field Explorer tree view and select New. Enter a name for your Formula Field (ProductValue). Then, the Formula Editor displays. The syntax for the formula is {Products.UnitPrice}*{Products.UnitsInStock}. Figure 9 shows the Formula Editor with the formula entered. Once the formula is saved and the Formula Editor is closed, the calculated field can be placed on your report.

Figure 6 shows these fields placed on the design surface.

Add Grouping and Subtotals

The report specification calls for the products to be grouped by category. To add the group to the report, right-click on the design surface and choose Insert, Group from the pop-up menu. This displays the Insert Group dialog, shown in Figure 7.

In the first combo box, select the field you want group by, CategoryName in this case. A good habit for your reports is to display the group header on each page (in the event a page breaks in the middle of a group). To do this, select the Repeat Group Header On Each Page option.

After closing the Insert Group dialog, a new band is added to the report and a special field is added to print the field specified for the group (Figure 8). Notice that the new band is placed between the Detail Band and the Report Header Band. Since our field labels are in the Report Header Band, I prefer to have the column labels right above the fields.

To move the labels down, you can create a second Group Header Band and move the labels down to that band. To insert a new Group Header Band, right-click on the design surface and select Insert, Section. This displays the Section Expert dialog. Click on the Group Header Band in the Sections list and then click Insert.

After closing this dialog, you will see a new empty band below the existing Group Header Band. The column headings can be selected and moved down to the new band.

To insert counts and subtotals, right-click the field you want to summarize and select either Insert Subtotal or Insert Summary. The difference between these two options is that inserting a subtotal automatically sums the field. Inserting a summary enables you to choose from several summary operations. These include count, average, maximum, minimum and others. For our example, we need to sum the UnitsInStock field and the ProductValue calculated field. There is also an option to insert Grand Totals, as well.

Add a Filter

The report specification says that only products that have not been discontinued will be displayed. To do this, right-click on the design surface and select Report, Edit Selection Formula, Records. This displays the Record Selection Formula Editor dialog. The Products.Discontinued field does not show up in the list of report fields because it is not included on the report. To drill down to that field, you can expand the Server node and drill down to the Products table and select the Discontinued field.

Double clicking the field places the field in the bottom section of the editor where the actual formula is being built. Now click in the editor and finish the formula so it reads:

{Products.Discontinued} = False

Even though the Discontinued field is a bit field in the table, Crystal Reports translates it into True or False. We must use these key words instead of 1 or 0 when comparing these types of fields. Figure 9 shows this dialog.

Add Conditional Formatting

Crystal Reports enables you to add conditional formatting to fields or to an entire band. This allows you to do things like change the background and or foreground color when a logical expression evaluates to True. We will use this conditional formatting to identify all products where the UnitsInStock have fallen below the ReorderLevel. We will change the background color or the detail band to yellow if this is the case.

To do this, right-click on the design surface and select Format Section. In the Section Expert dialog, select the Detail band. Then click on the Color tab in the tabbed dialogs on the right of the form. Now click the icon to the right of the Background Color option and enter the following code in the edit window:

if {Products.UnitsInStock} <
   {Products.ReorderLevel} then
  Yellow
else
  White

Adding Text Fields and Special Fields

You can add text fields to the report by right-clicking on the design surface and selecting Insert, Text Object. Then drag and drop the object to the desired location. To add text, simply double-click on the object and type the text.

To add special fields you can either expand the Special Fields node on the Field Explorer to drag-and-drop the desired fields onto the report or right-click the design surface and select Insert, Special Field to select the desired field. I have placed the special field Page x of y in the center of the report footer. Figure 10 shows the final report layout.

There certainly is additional formatting that can be done, such as drawing lines between the detail lines and the subtotals and adding additional labels. However, this will suffice for now. I want to continue to show how to view your report.

Viewing a Report in a Windows Form

To view a report in a Windows form, add a form to your project. Open the form for editing and drag the CrystalReportViewer component from the toolbox onto the form. You may want to anchor the viewer control to all four corners of the form so it will stretch when the form is resized. Now add the following line of code to the Form_Load method:

Me.CrystalReportViewer1.ReportSource = _
  "C:\Documents and Settings\djurden\" & _
  "My Documents\Visual Studio Projects\" & _
  "CR CoDe\Products.rpt"

Note: Obviously, you will need to change the path to the appropriate path for your project.

This is all you need to do. When you run the form, the report displays as shown in Figure 11. This method of setting the ReportSource property is a lot of typing as well as not very flexible, especially if the path of the report changes. A better way is to create an instance of the report object and assign that to the ReportSource property. The code looks like this:

Dim loRpt As New Products()
Me.CrystalReportViewer1.ReportSource = loRpt

Substituting this code for the above code works the same and gives much more flexibility. There are several other properties of the report object that can be set now that we have a reference to it. For example, I can limit the report to only products for a selected category by setting the RecordSelectionFormula property. Like this:

Dim loRpt As New Products()
loRpt.RecordSelectionFormula = _
  "{Categories.CategoryName} = 'Beverages'"
Me.CrystalReportViewer1.ReportSource = loRpt

This code overrides the existing RecordSelectionFormula in the report, if one exists. To add to the existing formula, we could use:

loRpt.RecordSelectionFormula = _
  loRpt.RecordSelectionFormula &_
  " and {Categories.CategoryName} = 'Beverages'"

There are several properties that can be set on the CrystalReportsViewer control, as well, such as a series of properties that control which toolbar buttons to display on the control. For example, if you did not want the users to be able to export or print the report, you could hide those buttons with the following code:

With Me.CrystalReportViewer1
   .ShowExportButton = False
   .ShowPrintButton = False
End With

Using the Report and Viewer objects gives us a lot of flexibility and control over the reporting features in our applications.

Viewing a Report in a Web Form

Adding a form for viewing your report in a Web form is very similar to the Windows form. In your ASP.NET application, add the report file to the project. Then add a new Web form and drop the CrystalReportViewer control on the form. Go to the code window and enter the same code described above for setting the ReportSource property of the CrystalReportViewer in the Page_Load event. When you run the form, you should see something similar to Figure 12.

The CrystalReportViewer component for Web forms has a different set of properties than the one for Windows forms. Notice that there is no Print or Export button. Also, instead of having a ShowPageNavigationButtons property, it has a HaspageNavigationButtons property.

Using an ADO.NET Dataset as the Data Source

To use an ADO.NET Dataset in your report, you need to create a typed dataset in your project. A typed dataset is stored in an XSD file and is an XML document describing the structure of the dataset, including all tables, fields (and their types), relations, and so forth. The typed data set does not contain actual data; you will have to write code to fill the dataset with data before the report is viewed or printed.

To create a typed dataset, add a new item to your project. In the templates pane of the Add New Item dialog, select DataSet. This adds the component to your project and displays the DataSet design surface. Now you can use the Server Explorer to drag and drop your tables and create relationships. Figure 13 shows the design surface for the Products and Categories tables used in our sample report. Once the typed dataset is created and saved, it can be used as the data source for your report. In the Database Expert, select the Project Data and ADO.NET DataSets notes. Your typed dataset and tables should be available for selecting. After selecting the data set, the report can be designed as usual.

If you already have the report designed using a different data source, you can replace that data source with a different one, as long as the two data sources have the same table names, field names and types, relations, and so on. To do this, open Crystal Report's Field Explorer and right-click the Database Fields node and select Set Location. The Set Location dialog displays, which enables you to select a new data source for the report. Be sure to click Replace once the new data source is identified.

Now that the report is designed to use an ADO.NET dataset for the source, you have to provide the actual data to the report before viewing or printing. The code in Listing 1 demonstrates adding data to a dataset.

Most of this code is creating the dataset object, loDS. This code could be placed in a business object or the dataset object could be retrieved from a Web service. Either way, the important lines for Crystal Reports are the last two: setting the report's DataSource by calling the SetDataSource method with your dataset object as the argument and then assigning the report object to the viewer's ReportSource property.

Publishing a Report as a Web Service

To publish a report as a Web service, create a new ASP.NET Web service application. Either create a report or add an existing report to the project. Once the report is in the project, publishing the report as a Web service is very simple. All you need to do is right-click the report in the Solution Explorer and select Publish as Web service. The name of the service defaults to the name of the report with the word "Service" appended. I added our Products.RPT file to an ASP.NET Web service project and published it as a service. The service name is ProductService.

Consuming a Report Web Service

Consuming the report Web service is just as easy. All we have to do is to add a reference to the Web service to our project. Then create an instance of the Web service and set it as the ReportSource for the viewer object. The code to do this is:

Private Sub Page_Load(ByVal sender As_
    System.Object,ByVal e As System.EventArgs)_
    Handles MyBase.Load
 
  Dim loProductsWS = New _
    localhost.ProductsService()

  Me.CrystalReportViewer1.ReportSource = _
    loProductsWS
End Sub

Summary

This article has provided an overview of some of the capabilities of Crystal Reports .NET. I demonstrated how to create a new report with several features of the Crystal Reports designer. Also, some techniques for displaying your reports in Windows forms as well as Web forms were shown. This article just scratches the surface of what can be done with Crystal Reports .NET.

Future articles will explore how you can enable users to interact with your report, use ADO.NET datasets and Web services to retrieve data for a report, expose Reports as a Web service, and pass custom parameters into your report.