Crystal Reports: Record Selection Formula

The Record Selection Formula

Although the Select Expert is quite powerful, there are certain situations where you need to define a filter that is more complex than the Select Expert allows. Fortunately, Crystal Reports has a built-in formula language that enables custom expressions to be defined as a filter. In fact, this is one of the strengths of the Crystal Reports product: being able to use the formula language to attain a high level of control in various aspects of report creation.

Although you might not have realized it, even when you were using the Select Expert, a formula was being generated in the background that defined the filter. To see this in action, open the Select Expert and click the Show Formula button. This expands the Select Expert dialog to reveal the formula being generated. This formula is called the record selection formula. Notice that the formula’s value is as follows:

{Customer.Last Year's Sales} < $5000.00 and
{Customer.Country} = "Scotland"

The formula language is covered in our training sessions, but the following are the key points to learn right now. In formulas, braces denote a field. For database fields, the table and field name £5,000.

Think of a record selection formula as an expression that evaluates to a true or false result. For each record in the database, Crystal Reports applies the record selection formula, plugging in the current field values in place of the fields in braces. If the result of the statement is True, the record is included in the report. If the result of the statement is False, the record is excluded from the report. Let’s look at an example. The first record in the Customer’s table is that of City Cyclists who had sales of £20,045.27.

For this record, Crystal Reports evaluates the preceding formula, substituting £20,045.27 in place of {Customer.Last Year's Sales}. Because this value is larger than £5,000, this statement is False and the record is not included in the report. To see what other formulas look like, change the filter using the Select Expert to a few different settings and observe how the formula changes.

See our Crystal Reports Introduction & Crystal Reports Advanced outlines form further details.

Leave a Reply

Your email address will not be published. Required fields are marked *