Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)

To understand the concept of filtering, let's first create a new report, which you'll use throughout this chapter.

Start Crystal Reports and create a new blank report. Using the xtreme.mdb database, add the Customer and Orders tables. Allow the Smart Linking feature to link these tables, or refer to the ER Model diagram in Appendix A.

Once you're at the Report Design area, place the Customer Name and Region fields (from the Customer table) and the Order Date (from the Orders table) in the Details section. Once you've done this, preview your report. Your report should look something like Figure 7-1.

Figure 7-1: An unfiltered report

By default, Crystal Reports processes every record within a data-base table. Thus, in the report you've just created, all customers display. As you page through this report (all 37 pages or so), you'll see different customers from different states with different order dates.

The truth is, rarely does a report require all records to display. Usually, report users only wish to view a subset of a table's records. For example, a report user may only wish to view customer information that occurred on a particular date. Or perhaps a report user only wishes to view information for customers of a particular region.

The Select Expert allows you to create filters that define what records a report should (and should not) display.

Let's take a look at that Select Expert and see how it works. Working with the report that you just created, try the following:

1. From the menu bar, select Report, Select Expert.

   You may also press the Select Expert icon on the Standard toolbar (this button looks like a hand selecting one of three marbles).

2. A Choose Field dialog box displays.

   Figure 7-2 displays this Choose Field dialog box. This is not the Select Expert; you must first choose the field for which you wish to filter data.

   Figure 7-2: The Choose Field dialog box

   Tip

   If you forget which values are within any of the fields, you can always use the Browse button.

3. Expand the Customer table and select the Region field.

   This field contains the data regarding whether a product is made by Xtreme or a competitor.

4. Press the OK button on the Choose Field dialog box.

   The Select Expert displays, as shown in Figure 7-3.

   Figure 7-3: The Select Expert

Notice the tabs at the top of the Select Expert dialog box. These tabs represent the filters defined within your report. There's also a tab labeled <New>, which allows you to create additional filters.

Whenever you create a filter within Crystal Reports, there are three pieces of information you must define to perform a record selection. These pieces of information are:

• The table and field to which the filter is to be applied.

• A comparison operator. The comparison operator compares data in a field with another value (the comparison value).

• A comparison value. The comparison value is a value that is fixed and unchanging. Examples of comparison values include other database fields, a text string, data, or numeric or dollar values that you enter.

Understanding the Comparison Operators

In SQL (the programming language used with Crystal Reports), comparison operators compare two values and then generate a true or false value from this comparison. This true or false value is known as a Boolean result. Crystal Reports uses this true or false value to determine which records display within a report.

The following table displays the available comparison operators available in the Select Expert. The Data Type column lists the data types that may use that comparison operator.