The Select Expert

Crystal Reports provides the Select Expert to help you create useful record selection criteria. You can use the Select Expert for simple, straightforward record selection, and as a starting point for more sophisticated record selection. The Select Expert can be run from the Record Selection section of any of the report wizards, or after you have chosen and linked tables using the Blank Report option. In either case, you ll want to make sure to use it before you preview the report.

To use the Select Expert while using one of the report wizards, choose at least one table in the Data section, and, if necessary, link the tables in the Link section (table linking is covered in more detail in Chapter 16). Then progress through the other sections until you reach the Record Selection section, which looks like this:

If you are using the Blank Report option to create a report, you must initially select and link tables. Once the Design tab appears, you can either immediately run the Select Expert or add fields to the report before running the Select Expert. Again, you ll want to run the Select Expert before you preview the report.

Run the Select Expert by clicking Select Expert in the Expert Tools toolbar. You can also choose Report Select Expert from the pull-down menus . If you have already added fields to the report and want to use one of them from the Design tab or Preview tab for record selection, select the field on the report before you start the Select Expert. You can also right- click your selected field and choose Select Expert from the pop-up menu. In each case, the Select Expert will start with a tab already created for the chosen field.

Caution  

You may use this feature unintentionally. If you start the Select Expert and see a tab for a field that you don t want to select on, just click the Delete button on the right side of the expert to delete the current tab. Then click the <New> tab or the New button to choose the correct field to select.

If this is the first time you ve run the Select Expert, and you haven t chosen an existing report field, you ll see a Choose Field dialog box listing all report and database fields. You ll also see this dialog box if you are already displaying the Select Expert and click the <New> tab or the New button to add additional criteria.

Click the report or database field that you want your record selection to be based on. If you want to see sample data from the database for that field, click Browse. Once you re satisfied with the field you want to use for record selection, select it and click OK. The Select Expert will appear with a tab for that field.

Once you ve chosen a field to select on, you ll see an additional pull-down list with the default of Is Any Value. You will use this list to choose the comparison operation you want to use for your record selection. The pull-down list will reveal all the comparisons you can use to select records; it may vary somewhat based on the type of field you ve chosen for record selection. Table 8-1 explains the different operators that may appear in the pull-down list. Note that the pull-down list will contain the operators discussed in the table, as well as operators like them except that they begin Is Not. As you might imagine, choosing the Not version of the operator will include all records where the condition is not true, instead of where the condition is true.

Table 8-1: Select Expert Comparison Operators

Operator	Description
Is Any Value	This is the same as having no selection criteria at all. Is Any Value means it doesn't matter what's in the field ”all records will be included in the report.
Is Equal To	The field must be exactly equal to what you specify.
Is One Of	You can specify more than one item to compare to by adding multiple comparison items to a list. If the field is exactly equal to any of them, the record will be included.
Is Less Than	The field must be less than the item you're comparing to. If you are comparing numbers , the field must be smaller numerically . If you are comparing dates, the field must be an earlier date. If you're comparing strings, the field must be lower in the alphabet. If you choose the Less Than Or Equal To option, the field can be equal to or less than what you're comparing to.
Is Greater Than	The field must be greater than the item you're comparing to. If you are comparing numbers, the field must be larger numerically. If you're comparing dates, the field must be a later date. If you're comparing strings, the field must be higher in the alphabet. If you choose the Greater Than Or Equal To option, the field can be equal to or greater than what you're comparing to.
Is Between	Allows you to select two items to create a comparison range. The field must be between the two items, or equal to one of them. Is Between uses the same type of comparison as is used with Is Less Than and Is Greater Than: numbers compare numerically, dates compare chronologically, and strings compare alphabetically .
Starts With	Allows you to specify leading characters to compare to. If the first characters in the field equal the specified characters , the record will be returned. If you want to perform several Starts With comparisons, you can add multiple criteria to a list. This operator will only appear when you are using a string field.
Is Like	You can look for partial text matches using wildcard characters to search for records that contain particular characters or groups of characters. When you specify your comparisons, you can use a question mark to indicate that one character in the field at that position can contain anything. You can use an asterisk to indicate that the rest of the field from that point on can contain anything. If you want to perform several Like comparisons, you can add multiple criteria to a list. This operator will appear only when you are using a string field.
Is In the Period	Allows you to compare a date field to a group of built-in date ranges, such as the last week, last month, last quarter, current year, etc. These built-in ranges are all based on the system clock of your computer when you run the report. This operator will appear only when you are using a date or date-time field.
Is True	Includes records where the field equates to true. This operator will appear only when you are using a Boolean field.
Is False	Includes records where the field equates to false. This operator will appear only when you are using a Boolean field.
Formula	Allows you to enter any Boolean formula using the Crystal Reports formula language. Similar to the Show Formula button in the lower-right corner of the Select Expert.

Once you ve chosen a comparison operator in the pull-down list, the Select Expert will change based on the selection you ve made. If you ve chosen an operator that only compares to one item (such as Equal To, Less Than, or Greater Than), one additional pull-down list will appear. If you ve chosen an operator that can compare to multiple items (such as One Of, Like, or Starts With), a pull-down list will appear, along with a multiple-item box. You can add and remove items from the multiple-item box by clicking the Add and Delete buttons that appear next to the box.

The new pull-down list allows you to choose the item you want to compare the field to in either of two ways: you can type it directly or choose it from the pull-down list. You can simply type the literal item you want to compare to directly in the pull-down list.

If you click the arrow on the pull-down list, the Select Expert will browse the database and list a few sample items from that database field. You may choose one of the items in the pull-down list for comparison.

If the comparison operator you ve chosen allows multiple entries, you can add an item you ve typed to the multiple-item box by clicking Add. If you choose a browsed database item from the pull-down list, it will be added to the multiple-item box automatically. In either case, you can remove an item from the multiple-item box by selecting it and then clicking Remove.

Note that you can choose Is Not versions of the comparison operators as well. This will, in essence, reverse the selection criteria you ve chosen. If, for example, you chose a country field, selected the Is Not Equal To operator, and specified USA as the item to compare to, your report will now include records for every country except the USA.

The Select Expert does not limit you to comparing just one field. Once you have added one database field, you can click the <New> tab or the New button. This will redisplay the Choose Field dialog box, from which you can pick another field to compare to. Once you pick this field, a new tab will display in the Select Expert, enabling you to create another comparison. You may create as many tabs and comparisons as you need.

Tip  

Crystal Reports applies a logical AND to all the tabs in the Select Expert ”all the criteria have to be true for a record to be selected. If you would rather have a logical OR applied to some or all of the tabs (so that if any one of them is true, but not all of them, a record is returned), you must manually edit the selection formula created by the Select Expert. This is discussed later in this chapter in Manipulating the Record Selection Formula Directly.

Refreshing the Report Versus Using Saved Data

When you preview a report on the screen for the first time, Crystal Reports has to actually read the database and perform record selection, and only thereafter can it format and display the report. To enhance future performance while you work with the report, Crystal Reports creates a set of saved data. This saved data consists of the records that were retrieved from the database, which are then kept either in memory or in temporary files on your hard drive. If you perform simple formatting changes, move fields around, or make other minor modifications that won t require the database to be requeried, Crystal Reports will use the saved data every time you preview the modified report, thus improving performance. If you add new fields to the report, Crystal Reports knows it has to requery the database, and it does so without prompting. You may notice a bit of a wait (or maybe a long wait, depending on your database) while it runs the new query.

But when you change record selection criteria, Crystal Reports doesn t know whether or not it needs to requery the database. You will be given the option to Refresh or Use Saved Data. The choice you make is dependent upon whether you widened or narrowed the selection criteria. If you narrowed your selection criteria so that the new selection criteria can be completely satisfied with the existing saved data, you can choose to use the saved data. Since the database doesn t have to be requeried, the changes will appear very quickly in the Preview tab.

If, however, you widened the selection criteria so that the saved data won t contain all the records you re specifying, you need to refresh the report so that the database can be requeried. Choosing to use saved data in this situation will result in your report showing too few (if any) records, even though they actually exist in the database. However, the new query will take time to perform. If you make the wrong choice and end up with too few, or no, records, you can refresh the report manually by clicking the Refresh button in the Standard toolbar, pressing the F5 key, or choosing Report Refresh Report Data from the pull-down menus.

When you save a report, you have the option to store the saved data in the .RPT file. If you include the saved data, the report will immediately display the Preview tab showing the saved data the next time you open the .RPT file ”no database requery will be required. However, this will also make the .RPT file larger (sometimes significantly so), since it has to keep the saved data along with the report design.

Tip  

Even if you open a report with saved data, the saved data will be discarded and only the Design tab will appear if the Discard Saved Data on Open option is checked on the Reporting tab of File Options.

To choose whether or not to save data, check or uncheck File Save Data with Report from the pull-down menus, and then resave the report after making your choice. You can also make the choice with the appropriate check box on File Report Options. If you wish to set default behavior for this option for all new reports in the future, turn on or off the Save Data with Report option on the Reporting tab of File Options.

Record Selection with Date Fields

Many reporting requirements can be satisfied by creatively using date fields in record selection. Crystal Reports provides a good selection of built-in date ranges you can use to compare to, or you can use other operators to compare date fields. When you choose a date field in the Choose Field dialog box, the Select Expert makes the In the Period comparison operator available. If you choose this operator, another pull-down list containing Crystal Reports built-in date ranges appears.

By using these built-in ranges, you can create a report that will return, say, only orders in the previous month by comparing to the LastFullMonth. What s particularly appealing about using built-in date range functions is the self-maintenance of the report. When you use the LastFullMonth range, for example, the report will always use the system clock in your computer to include orders from the previous month, no matter when the report is run. You don t have to manually change the date range every month.

There may be times, however, when you have to manually enter a date range for record selection. If, for example, you want to see all orders for 2001, you need to specify those dates manually. There is no built-in X Years Ago date range. In this case, you choose the date field you want to select on (for example, Order Date) and use a Between comparison operator to indicate orders between January 1, 2001, and December 31, 2001. You can enter a beginning date of 1/1/2001 and an ending date of 12/31/2001.

Tip  

Crystal Reports allows a fair amount of flexibility in date formatting with the Select Expert, generally allowing you to type in free-form dates as you wish. However, you ll still receive error messages if you type in dates that Crystal Reports isn t sure about, such as dates that include only a two-digit year.

If you re selecting on a Date/Time field with the Between operator, supplying just a date, as opposed to a date and time, will work, but Crystal Reports will automatically assume times on or after midnight of the first date. However, only records that include times of exactly midnight for the second date will be included ”any times of even one second after midnight for the second date won t be included. Therefore, make sure you include a time value alongside a date value if you want to select records based on some time other than midnight.