Restricting Records Using Select Expert

Objectives

After completing this lesson, you will be able to:

  • Filter records using the Select Expert

Select Expert Overview

Use the Select Expert to restrict data.

By default, the report will print all records in your data source. You may want to restrict the number of records and focus on a specific set of data. For example, you may want to report on customers in Australia that had more than $30,000 worth of sales last year. Use the Select Expert to set the record selection of the report to include (or exclude) the data that you want in your report.

Select Expert Activation

To activate the Select Expert, select a field, then:

  • Click Select Expert on the Expert Tools toolbar.

  • Choose Report → Select Expert from the menu.

  • If the field that you want to set record selection on is on your report, right-click that field and choose Select Expert from the shortcut menu.

The Select Expert opens automatically for the field that you select. You are not presented with a pick list of fields.

Note

If you have not selected a field by clicking it to make it active before using one of these methods, a dialog box opens that displays the fields.

Select Expert Features

In addition to the New and Delete buttons, the Select Expert contains several function buttons including:

  • OK: This option accepts any changes that you have made and closes the dialog box. The report is then updated to reflect those changes.

  • Cancel: This option closes the dialog box, ignoring any changes that you made while in the dialog box. The report is not affected.

  • Browse: This option opens a Fields dialog box that shows a sample of values from the database.

  • Help: This option opens the Crystal Reports online Help.

  • Show Formula: This option expands the Select Expert to include a display of the formula created.

Record Selection Criteria

The first step in record selection is to define your criteria for selection. For example, do you want to focus on one city or customer, or are you interested in specific regions or countries? Would a date range or all records below a particular value work best?

The Select Expert dialog box defaults to "any value." If you enter a choice other than "any value" in the drop-down list box, a second drop-down list appears that contains some sample data from your database. You can choose values directly from the drop-down list or manually enter the value at the cursor prompt.

Note
If you are familiar with the Crystal formula language, you can enter your formula here. You can click Show Formula to view the formula, if any, used by Crystal Reports. You can also activate the Formula Editor from this extended window and modify the formula. For example, you want only the records in which profits are over a certain percentage. Once the percentage is calculated, the record selection decides whether the record is included on the report.

Operators

Using the drop-down list, you can build selection criteria that meet your needs. Depending on the field that you select, the available operators in the drop-down list are:

is any value
This option selects all records, meaning no selection at all.
is equal to

This option enables you to specify one specific value as the criteria, so that only matching records are included on the report; for example, only records from California (CA).

is one of

This option enables you to specify a series of values as the criteria, so that only records matching one of these will be included on the report; for example, only records from CA, BC, or NY.

is not one of

This option enables you to exclude a series of values as the criteria, so that those matching records will be excluded on the report; for example, not records from CA, BC, or NY.

is greater than or less than

This option enables you to indicate a specific value that records must be above or below to be included on the report; for example, only records with sales greater than or over $50,000 for the last year.

is greater than or equal to or less than or equal to

This option enables you to include all records in which the linked field value is above or below (or equal to) to be included on the report; for example, only records with sales that are the same amount (that is, $50,000) or greater than or over $50,000 for the last year.

is between

This option enables you to select records that have a value falling between or matching one of the two specified values; is inclusive and includes the end values. For example, only records with last year’s sales between $10,000 and $20,000, including $10,000 and $20,000.

is not between

This option enables you to exclude records that have a value falling between or matching one of the two specified values; is inclusive and excludes the end values. For example, excludes records with last year’s sales between $10,000 and $20,000, including $10,000 and $20,000.

starts with

This option enables you to indicate character(s) or value(s) that each data field must begin with in order to pass the selection criteria. For example, only customers whose names begin with the letter "A."

does not start with

This option enables you to indicate character(s) or value(s) that each data field does not begin with in order to pass the selection criteria; for example, no customers whose names begin with the letter "A."

is like

This option permits wildcard characters (? and *) to specify criteria that must be met; for example, crystal, comical, and critical could all be selected using "is like c*."

is not like

This option permits wildcard characters (? and *) to specify criteria that must be excluded. For example, using "is not like c*" would exclude crystal, comical, and critical.

in the period

This option enables you to specify a date range in which records must fall in order to be included on the report and is only available if a date field is chosen. With this option, a scroll list of all Crystal Reports date ranges is made available.

is not in the period

This option enables you to specify a date range in which records must not fall in order to be included on the report and is only available if a date field is chosen. With this option, a scroll list of all Crystal Reports date ranges is made available.

formula

This option expands the dialog box where you enter your criteria.

Refresh Versus Saved Data

Choosing Between Refresh and Saved Data

It is important to know when to choose Use Saved Data or Refresh Data (live from data source). Whichever choice you make, Crystal Reports displays the first page as quickly as possible so that you can see your report while the rest of the data is being processed. This same choice will be offered each time that you change the record selection.

Refresh Data

Refreshing report data returns the records that are currently available from the data source.

If the data isn’t rapidly or constantly changing, then all those requests to the data source do little more than increase network traffic and consume resources. In such cases, you may prefer to use reports with saved data.

Saved Data

Reports with saved data are useful for dealing with data that isn’t continually updated. When users navigate through reports with saved data, and drill down for details on columns or charts, they don’t access the data source directly; instead, they access the saved data. Consequently, reports with saved data not only minimize data transfer over the network, but also lighten workload of all resources.

Log in to track your progress & complete quizzes