Filtering Records
When printing records from one or more tables, you probably don’t need to print every single record. It is common to print only a subset of the original records. For example, rather than print every customer in the database, the report only prints customers that have been added within the past thirty days. SQL statements use the WHERE clause to filter out records that you don’t want.
The WHERE clause lists each condition that must be met before a record is selected. For example, if the sales figure must be greater than $100,000, then the WHERE clause would read like this:
If there is more than one condition, you need to use the Boolean operators AND or OR to specify which conditions need to be met. If you use the AND operator, both conditions must be met for the record to be selected. If you use the OR operator, only one of the two conditions must be met for the record to be selected. Let’s add on to our previous example and say that in addition to the total sales being greater than $100,000, the region must be Rio De Janeiro. Since both conditions need to be met, we’ll use the AND operator.
Crystal Reports uses the record selection formula for filtering records. In Chapter 4 we learned how selection formulas can be created using either the Select Expert or the Record Selection Formula Editor. We also saw how we can be in the Select Expert dialog box and click on the Show Formula button to see the actual formula that Crystal Report is creating behind the scenes. But now that we have a better understanding of SQL, we want to see the actual SQL statement passed to the database. By selecting the menu option Database > Show SQL Query we can see the SQL statement. Figure 10-12 shows how the previous example would look in Crystal Reports.
Figure 10-12. SQL query for a WHERE clause.
