Creating Compound-Record Selection Statements

So far, you've created a record selection statement on a single field. Of course, you're not limited to only one filter per report. You can create as many filters as your report needs. A record selection statement with more than one filter is known as a compound-record selection statement. A compound-record selection statement is a selection statement that filters more than one field within a report.

When you're creating compound-record selection statements, you can create the first filter, test it out, reopen the Select Expert, and then add the second filter. You can also create both selection filters all at once within the Select Expert by using the <New> tab.

Either method works fine. However, sometimes creating one filter at a time and then previewing your work ensures that your report filters exactly the records you want.

Let's try an example, continuing with the report you've been creating in this chapter. Perform the following steps:

1. Access Report, Select Expert from the menu bar.

   The Choose Field dialog box displays.

2. Select the Country field from the Customer table.

   Just because a field isn't displayed within your report doesn't mean it can't act as a filter. In this example, you'll create a filter using the Country field, which is a field that's not displayed.

3. Press the OK button.

   The Select Expert displays.

4. In the Comparison Operator drop-down field, select the Is Like operator.

5. In the Comparison Value drop-down field, select USA.

   In this field, you're defining your filter to return only customers within the United States.

6. Select the <New> tab.

   The Choose Field dialog box displays again.

7. Select the Order Date field from the Orders table.

   The Select Expert displays.

8. In the Comparison Operator drop-down field, select the Is Greater Than Or Equal To operator.

9. In the Comparison Value drop-down field, select 1/1/2001 12:00:00AM.

   You screen should look like Figure 7-7.

   
   Figure 7-7: Creating a compound-record selection statement in the Select Expert

10. Press the OK button on the Select Expert dialog box.

    Crystal Reports asks if you'd like to refresh your data.

11. Press the Refresh Data button.

    You're returned to the Report Design area.

12. Preview your report.

    Scroll through the pages within your report. You should only see customers within the USA whose order dates are greater than or equal to 1/1/2001 12:00:00 AM.

    If you wish, add the Country field to your report to make sure the report displays customers from the USA only. Your report should look like Figure 7-8.

    
    Figure 7-8: Your report with a compound-record selection statement