Using the Select Expert

To open the Select Expert, select the menu items Report > Select Expert. The other method is to click on the Select Expert button on the Toolbar.

If this is the first time the Select Expert has been run for the report, it shows the Choose Field dialog box. This is where you chose the field to filter on. As you can see in Figure 4-1, it simply lists all the fields available. The fields already used on the report are shown first and they are followed by every available field from the report’s data source. A field from either of these groups can be selected.

Figure 4-1. The Choose Field dialog box.

Once you select a field from this dialog box and click the OK button, the dialog box is not shown again. Instead, you are always taken to the Select Expert dialog box shown in Figure 4-2.

Figure 4-2. The Select Expert dialog box.

There are two tabs in the Select Expert dialog box. The first is titled with the field that was just selected in the Choose Field dialog box. The second tab is titled . The first tab with the field name has a dropdown list for selecting the filter criteria. This is set to Is Any Value. By default, reports don’t have any filters turned on and every record gets selected. Click the dropdown list to view available filtering options and select the one you want. The dialog box in Figure 4-2 shows all the options in this dropdown list. This list is the textual equivalent of the basic comparison operators. They are described in detail in Table 4-1.

Table 4-1. Comparison operators for filtering data.

Selecting a DateTime field gives you two additional options listed with the comparison operators. These options let you select dates based on sophisticated date ranges. For example, you can select fields that fall within the last 7 days or select fields that are in the first quarter of the calendar year. See Figure 4-3 for a list of the available functions.

Figure 4-3. Available date range functions.

After selecting a comparison method, the right side of the dialog box changes so that you can enter the value to compare the field to. With the majority of the comparisons, only a single dropdown list is shown. For example, comparisons such as Is Equal To or Is Greater Than compare the field to a single value.

There are exceptions to the rule of only having a single dropdown list. The Is Between and Is Not Between comparisons give you two dropdown lists. This lets you create a range of values by entering the beginning and ending values. The Is Between comparison is inclusive. As mentioned in Table 4-1, if the range is from 100 to 200 then the numbers that would be included in this range start at 100 and go up to, and including, 200. The Is Not Between comparison is exclusive. The valid numbers would be any number less than 100 or greater than 200.

A very helpful feature for setting the filter is that all the current values for the field are listed in the dropdown list. Crystal Reports queries the database and populates the list with the current data for that field. Clicking on the arrow lets you see what those values are. In fact, the first time you click on the arrow you will probably notice a short delay while Crystal Reports opens the database and reads the records. You can either select one of these values from the list or type in a new value that isn’t in the list.

If a report uses data from a data source on an external server, make sure you have an active connection to it. Otherwise, Crystal Reports temporarily freezes up while it attempts to connect to the server and wait for a response.

The comparisons Is One Of and Is Not One Of also let you enter more than just a single value to compare to. These criteria let you build a list of items where the field should either be in the list or not in the list. As you select items from the dropdown list, they are added to the listbox below it. This dialog box is shown in Figure 4-4.

Figure 4-4. Building a list of items.

You can also manually type in values and add them to the list by clicking on the Add button. If you add an item by mistake, delete it from the list by clicking the Remove button.

You are not limited to selecting records based on a single field. The tab lets you select additional fields for record selection. Clicking on this tab shows the same Choose Field dialog box that you saw in Figure 4-1. After selecting a field, you are brought back to this tab and that field is now in the tab header. The rest of the selection process is the same as what was just discussed. You can add as many fields as you want.

When you have more than one field in the Select Expert, Crystal Reports examines each field separately to see if it matches the criteria specified. After making each comparison, it only selects a record if it matches every condition. This is referred to as a Boolean And comparison. If there are four fields listed in the Select Expert and a record only matches three of them, then it isn’t selected.

If you have a report that needs faster performance and it uses multiple fields in the Select Expert, you might get better performance by rearranging the order of the fields. When there are multiple fields in the selection formula, the performance is affected by which fields have an index and how much data each one filters out. Although this is far from an exact science, for large reports you should try experimenting with the field order and see if you get significant improvements.

The Report Options dialog box also has a setting called Select Distinct Records. When checked, it tells the database not to return any duplicate records. If every field in a record matches every field in a record that has already been printed, it is skipped. -->