Exploring the Select Expert


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.

click to expand
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.

    click to expand
    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.

    click to expand
    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.

Note

If your comparison operator is set to is any value, then you will not have a comparison value (the third piece of information). This is because the is any value option returns all records. You'll need to change the comparison operator to another option (such as is equal to) in order to set the comparison value information.

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.

Table 7-1: The comparison operators of the Select Expert

Comparison Operator

Description

Data Type

Is Any Value

Returns all records within a field. There is no filtering done on the field.

Text, Numeric, Date

Is Equal To

Returns all records that EXACTLY match the comparison value.

Text, Numeric, Date

Is Not Equal To

Returns all records that DO NOT EXACTLY match the comparison value.

Text, Numeric, Date

Is One Of

Returns all records that MATCH one or more of a series of values you enter.

Text, Numeric, Date

Is Not One Of

Returns all records that DO NOT MATCH one or more of a series of values you enter.

Text, Numeric, Date

Is Less Than

Returns all records that are less than the comparison value.

Text, Numeric, Date

Is Less Than Or Equal To

Returns all records that are less than or equal to the comparison value.

Text, Numeric, Date

Is Greater Than

Returns all records that are greater than the comparison value.

Text, Numeric, Date

Is Greater Than Or Equal To

Returns all records that are greater than or equal to the comparison value.

Text, Numeric, Date

Is Between

Returns all records that FALL BETWEEN a range of values you enter.

Text, Numeric, Date

Is Not Between

Returns all records that DO NOT FALL within a range of values you enter.

Text, Numeric, Date

Starts With

Returns all records that START with a text comparison value (for example, if you define a record to start with the letter T, your report returns all records that begin with the letter T).

Text

Does Not Start With

Returns all records that DO NOT start with a text comparison value.

Text

Is Like

Returns all records that MATCH values you've entered that utilize a wildcard character. We learn more about wild-card characters later in this chapter.

Text

Is Not Like

Returns all records that DO NOT MATCH values you've entered that utilize a wildcard character.

Text

Formula

Returns all records that use filtering instructions from a formula. We learn more about selecting records with formulas in Chapter 11.

Text, Numeric, Date




Mastering Business Analysis with Crystal Reports 9
Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)
ISBN: 1556222939
EAN: 2147483647
Year: 2004
Pages: 177
Authors: Chris Tull

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net