You’ve had the opportunity to create basic report formats, including sorting, grouping and adding parameters. This chapter takes that knowledge a little further by showing you how to add more customization to your report. This customization consists of filtering records, using report sections for advanced formatting techniques, and creating running totals. Once you are finished with this chapter, you will have the foundation needed to generate the majority of reports you need on a daily basis.

Selecting Records

Up to this chapter all the reports have all selected records from a table without regard to filtering the data. It was assumed that you wanted to display every record in the table. While this is true some of the time, you frequently want to filter the data so that only a subset of records gets printed. This lets you customize a report to only show the information that pertains to the current user. For example, you can design a sales report so that it selects data based upon the region, the sales person, or even on a sales person for just the last month. Almost any way that you can imagine to filter data can be done for a report.

Crystal Reports makes selecting records easy. It provides a Select Expert dialog box that lets you pick one or more fields and set the selection formula. The Select Expert dialog box is similar to other Crystal Report experts and is easy to learn.

Using the Select Expert

To open the Select Expert, right click on the report and choose Report | Select Expert. If this is the first time the Select Expert has been run for the report, it shows the Choose Field dialog box. As you can see in Figure 6-1, it simply lists all the fields available, including report fields as well as database fields.

Figure 6-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 6-2.

Figure 6-2. The Select Expert dialog box.

There are two tabs in this dialog box. The first is titled with the field that was selected in the Choose Field dialog box and the second is titled . The tab with the field name has a combobox for selecting the filter criteria. By default this is set to is any value. By default, reports don’t have any filters turned on and every record gets selected. Click the combobox to view available filtering options and select the one you want. The dialog box in Figure 6-2 shows all the options in this combobox. This list is the textual equivalent of the basic comparison operators that you would normally use in your programming code (e.g. =, >, <=, etc.). Except for the formula: option (discussed later), these are all standard comparisons and require no explanation.

Selecting a field that is of the DateTime data type give you additional options listed with the comparison operators. These options include selecting dates that are within a certain fiscal quarter, within the past month, or even aging dates according to how many days ago they occurred. These advanced filtering options demonstrate the power of Crystal Reports for working with dates. See Chapter 9 for more information on how these date functions work.

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 combobox is shown. A very helpful feature is that all the current values for the field are listed in the dropdown box. Crystal Reports populates the list with the current data for that field from all the records. In fact, you will probably notice a short delay as it opens and reads in the records from the table. You can either select one of these fields from the list or enter a new value that isn’t in the list.

If the report uses a connection to the database on the server, make sure you have an active connection to it. Otherwise Crystal Reports temporarily freezes up while it pings the server waiting for a response.