Special Edition Using Visual Basic.NET

By Brian Siler, Jeff Spotts

Crystal Reports is the professional reporting tool that is bundled with Visual Studio .NET. This tool automatically handles low-level printing operations, such as paging and print preview, allowing the developer to concentrate on designing the content of the report. You design Crystal Reports graphically, by arranging report fields in the integrated Crystal Report Designer. The Crystal Reports engine merges your report layout with a data source to come up with the finished report. In this section, we demonstrate how to design a Crystal Report and display it in your program.

Setting Up a Data Source

Crystal Reports are data-driven. Even before you begin designing a report, you need to have a good idea of where the data is coming from. Some examples of data sources suitable for Crystal Reports are SQL Server databases or ADO.NET datasets. Reporting on an ADO.NET dataset (which we discuss later in this chapter) is a little more complicated, since it is disconnected from any data source. For the example in this section, we use data from a stored procedure in the Northwind database, which is included with Visual Studio .NET. If you installed the .NET Framework SDK samples, you already installed an instance of SQL Server called NetSDK with the Northwind database. If you have not installed these samples, please see instructions in the earlier section called "Planning a Report." Before continuing, please verify that you can connect to the database and execute the Employee Sales By Country stored procedure. The sample stored procedure accepts two parameters (starting and ending dates) and returns information about each order shipped within the date range. To run the stored procedure and look at the data, execute the following SQL statement:

exec [Employee Sales by Country] '1/1/1990','12/31/2000'

Note

As we mentioned in Chapter 20, "Database Basics," database object names with spaces have to be enclosed in brackets. (We also mentioned that is probably not a good idea to use spaces in database object names due to this extra complication, but Microsoft wrote this procedure, not us!)

To learn how to execute SQL statements and stored procedures, p. 535

As you will see in a moment, when you design a Crystal Report you specify the stored procedure name, and Crystal will retrieve information about that stored procedure's parameters and database fields. However, one peculiarity of Crystal Reports is that sometimes it is confused by the presence of date parameters in a SQL Server stored procedure. In other words, the Crystal Reports program might incorrectly think that the Employee Sales By Country stored procedure has no data fields because it has two date parameter fields. Fortunately, SQL Server is flexible enough to accept string parameters and automatically convert them to dates. To ensure your success with this example, make a minor modification to the stored procedure by performing the following steps:

1. Using the Server Explorer as described in Chapter 20, establish a connection to the Northwind database.

2. Navigate to the Stored Procedures list.

3. Right-click the Employee Sales By Country stored procedure and select Edit Stored Procedure.

4. Change the data type for the two parameters from DateTime to char(10), as follows:

   @Beginning_Date char(10), @Ending_Date char(10)

5. Click the Save button on the toolbar to update the stored procedure.

The authors are not sure if this is a bug in the Crystal Reports program, but at the time of this writing, the previous steps were necessary to get Crystal Reports to work successfully with date parameters.

Designing a New Report

Now that we have identified the data source and prepared the stored procedure, we can proceed with designing a report. If you are following along with the sales report example from the Web site, CrystalExample.zip, add a new Crystal Report to your project by performing the following steps:

1. Right-click the project name in the Solution Explorer and select Add New Item.

2. From the list of item templates, select Crystal Report.

3. Enter a name for the report. For our example, use the name SalesReport.rpt. (Crystal's proprietary file format uses the rpt extension.)

4. Click Open. You will see the Crystal Report Gallery dialog box appear, as shown in Figure 23.5.

   Figure 23.5. When creating a new Crystal Report, you can choose from several canned templates, create a blank report, or copy an existing report.

   If you choose one of the Report Expert options (Standard, Cross-Tab, and so forth), you will be asked a series of questions and Crystal will automatically create a report layout for you. However, in the interest of learning as much as possible about how the report design process works, let's create a report entirely from scratch.

5. Select As a Blank Report and click OK to close the Crystal Report Gallery dialog box.

Understanding Report Sections

When you add a new Crystal Report to you project, the designer window for the new report is opened. As shown in Figure 23.6, the report designer is divided into several areas, or sections.

As you design the report, you will use the Toolbox and Field Explorer to add items to the appropriate sections of your report. Each section takes on a different behavior when an end user views or prints the report, as summarized here:

• Report Header This section appears once, at the very beginning of the report. You might want to use it to add a company logo or separator page.

• Page Header Appears at the top of every page of the report. This section usually contains column headings for database fields in the Details section.

• Details The contents of the Details section are repeated for every record in the data source.

• Report Footer This section appears once at the very end of the report. One use for it would be a grand total or other summary information.

• Page Footer This section appears at the bottom of every page of the report. This is a good place to add fields for page number and print date.

As you will see in a moment, you can also add additional sections to group records according to your specific reporting needs.

Enabling Stored Procedures

To retrieve data from a database, you have to come up with a query, whether it is a SQL statement or stored procedure. The Crystal Reports designer provides a graphical interface by which you can pick and join multiple tables from a database, and it will automatically generate a SQL query. However, I picked a stored procedure as the data source for our sample report to demonstrate an extra step that is required if you want to work with stored procedures. For some reason, each version of Crystal Reports in recent memory has made it unnecessarily difficult to use stored procedures by hiding a menu option somewhere that must be activated first. To enable reporting on stored procedures, you must perform the following steps:

1. Right-click anywhere in the white area of the report to display the pop-up menu.

2. From the Database submenu, select Log On/Off Server. The Data Explorer dialog box will appear.

3. Click the Options button. You should see the Default Settings dialog box, shown in Figure 23.7.

   Figure 23.7. If you want to use a database stored procedure in a Crystal Report, don't forget to select the option to show them.

4. Select the check box labeled Stored Procedures and click OK.

5. Click Close to close the Data Explorer window.

Connecting to a Database

To make the database fields from your stored procedure available in the report, you need to connect the Crystal Report to the data source. To connect to the Northwind database, perform the following steps:

1. Right-click anywhere in the white area of the report to display the pop-up menu.

2. From the Database submenu, select Add/Remove Database. You will see the Database Expert dialog box, pictured in Figure 23.8.

   Figure 23.8. Crystal can connect to a wide variety of data sources, including databases and local project items.

3. In the Available Data Sources panel, expand the OLE DB (ADO) folder. A dialog box will appear that allows you to specify a new connection to an OLE DB Provider.

4. From the list of OLE DB providers, select the Microsoft OLE DB Provider for SQL Server and click Next.

5. Fill in the Server name, User ID, and password fields appropriately. From the Database drop-down list, select Northwind.

6. Click Finish. Your database should be added to the list of Available Data Sources in the OLE DB connections folder.

7. Expand the connection list to navigate to the list of stored procedures in the Northwind database.

8. From the list of stored procedures, select the stored procedure named Employee Sales By Country.

9. Click the > button to move the stored procedure to the Selected Tables list.

10. Click OK to close the Database Expert dialog box.

Note

When using tables or views in a report, the Database Expert dialog box also contains a Link tab used to specify table joins.

Now that we have connected the Crystal Report to a data source, we can begin positioning fields from the data source on the report.

Using the Field Explorer

To add fields to your report, you first need to make sure the Field Explorer window is visible. The Field Explorer window is pictured in Figure 23.9.

As you can see, the Field Explorer shows several different types of fields you can add to your report:

• Database Fields The list of database fields contains the fields provided by the data sources in the report.

• Formula Fields The value of a formula field is the result of a user-defined formula, which may or may not use other fields in the calculation.

• Parameter Fields Parameter fields are passed to the report by program code or the end user and may be used in formulas or displayed on the report.

• Group Name Fields Used to place section group names in your report; user-defined sections work similarly to the GROUP BY statement in SQL.

• Running Total Fields Allows you to place calculated total fields (sum, maximum, recordcounts, and so on) on the report.

• Special Fields Special report information generated by the Crystal Reports engine, such as the print date and time or current page number.

• Unbound Fields Special-purpose formula fields.

Note

The items in the Field Explorer involve some calculation or relationship to the data; the items in the Crystal section of the Toolbox are static items designed to enhance the appearance of your report.

Let's add some fields to the report.

1. If you expand the Database fields section, you'll see the fields returned by the stored procedure.

2. Using the mouse, drag each of these fields from the Field Explorer window to the Details section of your report.

3. Arrange them so your report looks similar to the one shown in Figure 23.10.

   Figure 23.10. When you add a database field to the Details section, a text object label for the field is automatically added to the page header section.

Note

You can modify the size of each report section by sliding the section bars up and down with the mouse. You might want to do this to accommodate extra fields in a section or add whitespace to your report.

Congratulations, you have designed a very plain but functional Crystal Report. In a moment, we will return to the report designer and add more fields to enhance the usefulness and appearance of the report. However, first, we will demonstrate how to display and print the report in your program.

Displaying the Report

Visual Studio .NET includes two viewer controls for Crystal Reports: a Windows viewer for use in Windows applications and a Web viewer for use in Web applications. In this section we demonstrate using the Windows viewer. To learn more about the Web viewer, see Chapter 19, "Web Controls."

Note

Users of previous versions of Crystal Reports will note the conspicuous absence of the lightning bolt icon, which was used to preview the report while in design mode. Fortunately, because Crystal is completely integrated into the Visual Studio environment, it is very easy to preview a report using a Windows form.

For more on using a Crystal Report in a Web application, p.501

Setting Up a CrystalReportViewer Control

The Crystal Report viewer for Windows applications is called the CrystalReportViewer control and is located in the Windows Forms section of the toolbox. To display your new report using this control, you need to add the control to a Windows form and then let the control know the location of the report file. You can do this at design time by performing the following steps:

1. If you are already working with the sample report in a Windows application project, display the startup form. Otherwise, start a new Windows application project.

2. Add a CrystalReportViewer control to your form. Set its name property to crViewer. Size it so that it takes up most of the form area.

3. Open the Properties window for the crViewer control, and find the ReportSource property.

4. Using the Browse option in the ReportSource property, browse to the report file you just created, SalesReport.rpt.

5. Run the Windows application. The viewer will display dialog boxes requesting the start and end date parameters for the stored procedure.

6. After entering the report parameters, your report will be displayed in the viewer, as pictured in Figure 23.11.

   Figure 23.11. The versatile CrystalReportVie wer control allows the end user to print, zoom, and export the displayed report.

When your report is loaded in the viewer, you can use the toolbar to navigate and print the report.

Dealing with Parameters

Although Crystal Reports will automatically provide a dialog box for the user to enter report parameters, you may not always want this to happen. For example, the parameters may be database keys or other numbers that the user is not required to know. Or, you may want to restrict the parameters the user can enter to a subset of values based on the user's identity. In either case, you can send parameters to a CrystalReportViewer control programmatically to avoid the appearance of parameter dialog boxes. To demonstrate with our sample report, return to the Properties window and set the ReportSource property to None. To set up the viewer at runtime, perform the following steps:

1. Expand your form so there is room for a Button control.

2. Add a Button control to your form. Set its Text property to Display Report. Set its Name property to btnDisplayReport.

3. Add the following line of code to the top of the code window for your form: Imports CrystalDecisions.Shared

4. Enter the code from Listing 23.8 in the Click event handler for btnDisplayReport.

Listing 23.8 CRYSTALEXAMPLE.ZIP Setting Report Parameters

'Clear previous report source crViewer.ReportSource = Nothing 'Create the parameter value objects Dim paramStartDate As New ParameterDiscreteValue() Dim paramEndDate As New ParameterDiscreteValue() paramStartDate.Value = "8/1/1996" paramEndDate.Value = "8/31/1996" 'Create the parameters collection Dim paramList As New ParameterFields() 'Create the parameter objects and add them to the collection Dim paramTemp As ParameterField paramTemp = New ParameterField() paramTemp.ParameterFieldName = "@Beginning_Date" paramTemp.CurrentValues.Add(paramStartDate) paramList.Add(paramTemp) paramTemp = New ParameterField() paramTemp.ParameterFieldName = "@Ending_Date" paramTemp.CurrentValues.Add(paramEndDate) paramList.Add(paramTemp) 'Assign parameters collection to the report viewer crViewer.ParameterFieldInfo = paramList 'Load the report crViewer.ReportSource = Application.StartupPath & "\..\SalesReport.rpt"

The code in Listing 23.8 limits the date range of the records displayed to fixed values.

Enhancing the Report

Now that you have successfully created and displayed a Crystal Report, you can refine and enhance the report layout as necessary by returning to the report designer. For example, the appearance of our sample report is rather mundane. Figure 23.12 shows how elements from the Toolbox can make it more visually appealing.

In addition to adding these static elements from the Toolbox, you can also format the dynamic elements, such as database fields, by using the Format Editor dialog box, shown in Figure 23.13.

The Format Editor allows you to control field alignment, numeric and date formatting, font size, borders, and other formatting-related tasks.

Creating a Formula Field

In addition to making a report more visually appealing, you can also make it more functional by adding a formula field. A formula field contains the result of a calculation, usually performed on one or more database fields. For example, the sales report has separate fields for last and first name, but we might want a complete name field on the report. Because the data source only returns the names separately, we can create a new formula field called CompleteName that combines them inside the report. To add this formula field, perform the following steps:

1. In the Field Explorer window, right-click the Formula Fields and select New.

2. For the formula name, type CompleteName and click OK. The Formula Editor should appear. The Formula Editor, shown in Figure 23.14, allows you to edit the formula that will be used to determine the formula field value.

   Figure 23.14. You can type your formula manually in the lower pane of the Formula Editor or double-click the objects in the upper pane to add text automatically.

3. At the bottom of the Formula Editor dialog box, type the following line of text:

   Trim ({spBriantest;1.LastName}) + ', ' + Trim ({spBriantest;1.FirstName})

4. Click the Save and Exit button to close the dialog box.

Now that you have created a new formula field, it will appear in the Field Explorer window. You can then drag it to the Details section just as you would any other database field.

Adding a New Report Section

Another useful feature of Crystal Reports is the ability to add report groupings, also known as breaks. When you add a grouping, a new header and footer section are added to the designer. Each time the field you are grouping by changes, the header and footer are repeated on the report and can be used to display summary information for the group. For example, our sales report includes the country of each sale. With just a few mouse clicks, you can group the records by the Country field and have Crystal Reports display subtotals for each country:

1. First, you need to add a new group to the report. To do this, simply right-click Group Name in the Field Explorer and select Insert Group.

2. A dialog box will appear asking you for the group field and sort order. Select Country and click OK. New sections for the group header and footer will be added to the Design window.

3. Once you have added a new report group, you can add summary fields by right-clicking the desired field in the Details section and selecting Insert Summary.

There are several summary types to choose from, including count, summary, maximum, and minimum. Figure 23.15 shows the sales report as seen by the end user after we added summary fields for Country and Sales amount, as well as some text objects to label them.

Reporting from an ADO.NET Dataset

As you have seen throughout this section, Crystal Reports is capable of retrieving information directly from a database server using an OLE DB provider. However, when working with disconnected data in a distributed application, it may not be desirable or even possible to have the Crystal Viewer make a direct connection to the database. Fortunately, you can use an ADO.NET Dataset object as the data source for a Crystal Report.

To learn more about datasets, p. 599

Because a dataset is not associated with any particular database, you have to go through the extra step of building a description of it, so that Crystal will understand the field definitions. Once you design a report based on a dataset,you can substitute any Dataset object with the same field definitions at runtime.

Building the Dataset Schema

A description of database tables, including their field names and data types, is known as a schema. Schemas in ADO.NET are typically represented using XML. The Visual Studio .NET help files have a nice walk-through that shows you how to build a schema interactively by selecting an OLEDB provider, and then picking field names. However, to keep things interesting, we will demonstrate a quicker way to create a schema, by using an existing dataset. By simply filling a dataset and calling its WriteXMLSchema method, you can create a schema that is usable as a Crystal Report data source. To make this process even more painless, we will build on earlier knowledge by reusing our ADO.NET function library from Chapter 22. Assuming you have the ExecSP and associated functions available, you can generate a schema for the data returned by the Employee Sales By Country stored procedure with just a few lines of code. Listing 23.9 shows how to generate an XML schema.

Listing 23.9 CRYSTALEXAMPLE.ZIP Reporting from a Dataset

Dim intRetVal As Integer Dim parmArray(1) As SqlParameter 'Set up parameters parmArray(0) = New SqlParameter("@Beginning_Date", SqlDbType.Char) parmArray(0).Value = "1/1/1996" parmArray(1) = New SqlParameter("@Ending_Date", SqlDbType.Char) parmArray(1).Value = "12/31/1996" 'Fill dataset Dim dsInfo As New DataSet() Call ExecSP("MySample", "[Employee Sales By Country]", intRetVal, parmArray, dsInfo) 'Save Schema dsInfo.WriteXmlSchema("c:\SalesInfo.xsd")

After executing the code in Listing 23.9, you should have a file called C:\SalesInfo.xsd located on your hard drive. If you open the file in Notepad, you can see your field definitions described in XML.

Designing a Report from an XML Schema

If you have created the XML schema successfully, you can easily use it as a data source for the Crystal Report. Let's re-create the sales report example from earlier, except this time using a dataset as the data source. Perform the following steps to create the new report:

1. Add a new blank Crystal Report to your project, named SalesReport2.rpt.

2. Right-click in the white area of the report designer and select Add/Remove Database from the Database menu. The Database Expert dialog box will appear (refer to Figure 23.8).

3. In the Available Data Sources list, expand the More Data Sources folder, and then the ADO.NET (XML) folder.

4. When the dialog box appears, browse to the C:\SalesInfo.xsd file and click Finish. The new data source appears as a table called Table.

5. Select the Table table and click the > button to add it to the Selected tables list.

6. Click OK to close the Database Expert dialog box.

From this point, we can add fields and design the report just as we did in the previous sections, when using the SQL Server as a data source.

Note

You can also add the .XSD file we created to your project and edit it using Visual Studio .NET. Datasets associated with a project appear in the Project Data folder of the Database Expert dialog box.

Displaying the Finished Report

With dataset-based reports, the report itself is only aware of the schema; it does not know how to connect to the underlying database. Therefore, you have to write code that creates an appropriate Dataset object and pass it to the report, as shown in Listing 23.10.

Listing 23.10 CRYSTALEXAMPLE.ZIP Reporting on a Dataset

Dim intRetVal As Integer Dim parmArray(1) As SqlParameter 'Set up parameters parmArray(0) = New SqlParameter("@Beginning_Date", SqlDbType.Char) parmArray(0).Value = "1/1/1996" parmArray(1) = New SqlParameter("@Ending_Date", SqlDbType.Char) parmArray(1).Value = "12/31/1996" 'Fill dataset Dim dsInfo As New DataSet() Call ExecSP("MySample", "spBrianTest", intRetVal, parmArray, dsInfo) 'Set up report object Dim MyReport As New SalesReport2() MyReport.SetDataSource(dsInfo) 'Display Report crViewer.ReportSource = MyReport

Listing 23.10 also takes a different approach to displaying the report, that is, setting the ReportSource property of the CrystalReportViewer control to an instance of the report class rather than the filename. This is necessary so that the report will have data associated with it before the viewer tries to display it.