Creating Advanced Record Selection Formulas

Although many filters are simple enough to be defined using the Select Expert, most real-world reports require editing the record selection formula itself. Being able to create advanced record selection formulas is one of the key skills you'll need to acquire to create effective reports. Before diving into the best practices for creating these formulas, let's do a quick review of what you've learned so far about record selections.

Record Selection Review

Record selections, or filters, are defined by a record selection formula built using the Crystal syntax of the Crystal Reports formula language. You can build a record selection formula using the Formula Editor by selecting the Report menu, Selection Formulas, Record. Another more simpler way to build record selections is to use the Select Expert accessed via the Experts toolbar. The Select Expert builds a record selection formula behind the scenes for you.

A record selection formula is a formula that returns a Boolean value indicating whether a given record should be included in the report. It is evaluated for each record in the database. Any time a database field is used in the formula, it is replaced by the actual field value.

Now that you've been reminded about what you've learned thus far about record selection formulas, let's build up your knowledge by arming you with some of the best weapons for conquering tough record selections.

Dealing with Dates

One of the most common record selection formulas is {field} = value, where {field} is a database field and value is a corresponding value of the same data type. An example of this would be

 {Customer.Country} = "Canada"  

This kind of formula is very easy to create, but it gets a bit more complicated when the data types of the values to be compared are not the same. This tends to manifest itself with new report developers who first attempt to filter data based on dates. A common attempt would be to use a formula like this:

 {Orders.Order Date} > "2/25/2000"  

When clicking the Check button to check the formula's syntax, Crystal Reports pops up a message saying A date-time is required here and when closing the message box, "1/29/1998" is highlighted. The problem here is that because the Order Date field has a data type of date-time, the formula is attempting to compare a date-time to a string, which is not implicitly allowed. Comparisons must always be performed on objects of the same data type. To rectify this, instead of using a string literal to describe a date, the formula could use the DateTime function to return a date-time value. Here is an example of the corrected formula:

 {Orders.Order Date} > DateTime(2000, 2, 25, 0, 0, 0)  

You'll notice that when the DateTime function is used, it takes arguments for not only year, month, and day, but also for hour, minute, and second. This is because in order to compare this value to the Order Date field, it needed to be a date-time value. In this case, you might not care about the time part of the date-time value. The best way to solve this would be to first convert the Order Date field into a date from a date-time, and then use the Date function instead of DateTime. The improved formula follows:

 Date({Orders.Order Date}) > Date(2000, 2, 25)  

To make this even simpler, the Crystal Reports formula language also supports dates specified in the following format:

 #YYYY/MM/DD HH:MM AM/PM#  

Using this syntax, the following formula is also valid:

 {Orders.Order Date} > #2000/2/25 12:00 AM#  

Another nice feature of this syntax is the ability to omit the time portion. When this done, a default of 12:00 AM is used.

Various functions are available for converting between strings, dates, and date-times. These can be found in the Function Tree window of the Formula Editor, under the Date and Time folder.

Another issue that comes up often is filtering on a field in the database that contains dates but is defined as a string field. The following fictitious formula, although it will not return any errors when checking the syntax, does not do what you might expect:

 {Shipments.Ship Date} > "1/1/2001"  

This will not perform a date comparison because both fields are of type string. To correct this formula, you could use one of the functions provided by the DTS (date time string) user function library called DTSToDate.

A user function library is a library of functions that can be used from the Crystal Reports formula language. Crystal Decisions provides several of these with the product, and others are available from third-party vendors. If you are proficient with Visual Basic or C++, you could even create a user function library yourself. The user function library can be found under the Additional Functions folder in the Function Tree of the Formula Editor.

The DTSToDate function takes a string that is in the proper date format and converts it to a date value. The correct formula is shown here:

 DTSToDate({Shipments.Ship Date}) > Date(2001, 1, 1)  

Where the Ship Date field contains a date in DD/MM/YYYY format.

Working with Strings

As with dates, simple string comparisons are easy to achieve using the record selection formula. Slightly more complex comparisons can easily become tedious unless you are armed with knowledge for effectively dealing with strings. A simple example is a listing of customer data for a set of countries. Creating a record selection formula like the following can become quite tedious:

 {Customer.Country} = "England" or  {Customer.Country} = "France" or {Customer.Country} = "Germany" or {Customer.Country} = "Denmark" 

Rather than using multiple comparisons, this can be accomplished with a single comparison using a string array.

An array in the context of the Crystal Reports formula language is a set of values that can be referenced as a single object.

The previous record selection formula can be rewritten to look like this:

 {Customer.Country} in ["England", "France", "Germany", "Denmark"]  

Notice that there are several differences. First, instead of using multiple comparisons, only a single comparison is used. This is both simpler to read and easier to maintain. The four country values are combined into a string array. Arrays are indicated by square brackets with values separated by commas. Finally, instead of an = operator, the in operator is used. This operator, as its name implies, is used to determine if the value on its left is present inside the array on its right.

Although string arrays are being described here, arrays can be made holding other data types, such as integers and currency values.

In this example, the countries are hard-coded into the selection formula. Although this makes it easy to read, the report would need to be modified if the country list were to ever change. A better way to handle this would be to create a multiple value parameter and use it in place of the country list. If you did that, the formula would look like this:

 {Customer.Country} in {?CountriesParam}  

During the parameter prompting, the user will be allowed to enter multiple values, and you can even provide a list of default values to choose from.

Pushing Record Selections to the Database

When dealing with large sets of records, performance will become important. The record selection you use can make a huge difference in the performance of a report. Crystal Reports does have the capability to perform database-like operations on the data such as grouping, filtering, summarizing, and sorting. However, in general, asking the database to perform those kind of operations will result in a faster overall transaction. Because of this principal, Crystal Reports will attempt to ask the database to perform these operations if possible.

In the context of record selections, this means that when Crystal Reports queries the database, it will attempt to incorporate as much of the logic of the record selection formula as possible into the query. Ideally, all the logic can be incorporated into the query, which means that the database will perform all the filtering and only return the records that met the criteria. However, because the SQL language doesn't support all of what the Crystal Reports formula language does, there could be certain situations in which some or all the logic of the record selection formula cannot be converted to SQL. In this case, Crystal Reports needs to pull some or all the records from the database and perform filtering itself.

When working with a desktop database like Access or FoxPro, the performance difference between the database engine or the Crystal Reports engine doing the filtering would be minimal because it really comes down to which filtering algorithm is faster. Because databases are made for just this purpose and are customized for their own data structures, they will generally perform this kind of operation faster. However, when dealing with client/server databases in which the database resides on a backend server and Crystal Reports resides in your desktop machine, the difference becomes much more apparent. This is mostly because of network traffic. There's a big difference between sending 50 records back over the network than there is in sending 100,000. This performance hit gets even worse when using a slow connection such as a dial-up modem.

To determine whether the logic you've used in the record selection formula or select expert is being incorporated into the query sent to the database, it's helpful to have a basic understanding of the SQL language. You need not be an expert at SQL, but being able to recognize if the query is performing a filter on a certain field makes record selection formula tuning much more effective. For more information on SQL, see Hour 22, "Optimizing SQL Queries in Crystal Reports."

Although there are some guidelines for creating record selection formulas that will be fully passed down to the server, often the best approach is to simply check the SQL statement manually and determine if the record selection logic is present. To view the SQL statement that Crystal Reports has generated, select Show SQL Query from the Database menu. The resulting dialog is shown in Figure 15.1.

Figure 15.1. The Show SQL Query dialog.

You can infer from the preceding SQL query that this report is based on the Customer table, is using the Customer Name, Web Site, and Last Year's Sales fields, and has a record selection of

 {Customer.Last Year's Sales} > $20000  

All the logic of the record selection formula has now been passed down to the database in the SQL query. However, let's say that this report had a formula field that calculated the tax. That formula might consist of the following:

 {Customer.Last Year's Sales} * 1.07  

This formula field might be placed on the report to indicate the tax for each customer. The problem occurs when this formula is used in the record selection formula. Although the following formula seems logical, it is inefficient:

 {@Tax} > $10000  

If you were to look at the SQL query being generated for this report, you would see that there is no WHERE clause present. In other words, the report is asking the database for all the records and doing the filtering locally, which, depending on the size of the database, could result in poor performance. A better record selection to use which would produce the same results, but perform the filter on the database server would be

 {Customer.Last Year's Sales} > $142857  

This works out because at a tax rate of 7%, $142,857 is the minimum a customer would need to sell in order to have tax of more than $10,000. Using the previous record selection would result in a SQL query with the following WHERE clause:

 WHERE `Customer`.`Last Year's Sales` > 142857  

Although this approach returns the correct data, a slightly less cryptic approach would be to use a SQL Expression.

An Introduction to SQL Expressions

Crystal Reports formulas are useful because they allow you to use the full Crystal Reports formula language as well as a suite of built-in functions. However, as you've learned in this hour, they can be a factor in report processing performance. SQL Expressions are often the answer to this.

A SQL Expression, as the name implies, is an expression written in the SQL language. Instead of consisting of a whole formula, a SQL Expression consists of an expression that defines a single field just like a formula field does. The difference between a formula field and a SQL Expression is based on where it is evaluated. Formula fields are evaluated locally by Crystal Reports, whereas SQL Expressions are evaluated by the database server and thus produce better performance when used in a record selection formula.

To better understand this, let's look at the example discussed in the previous section. The example had a report with a Crystal Reports formula that calculated tax based on the Last Year's Sales field. Although there certainly are situations in which formula fields need to be used, this is not one of them because the logic being used in the formula is simple enough that the database server is able to perform it. Instead of creating a formula field, a SQL Expression could have been created. SQL Expressions are created via the Field Explorer, which was introduced in Hour 4. Right-clicking on the SQL Expressions item and selecting New will begin the process of creating a SQL Expression. When choosing to create a new SQL Expression, the SQL Expression Editor is launched (see Figure 15.2).

Figure 15.2. The SQL Expression Editor.

This editor is, in fact, the same editor used to create Crystal Reports formulas, but with a few small changes. First you'll notice that in the field tree, only database fields are present to be used in the expression. Because SQL Expressions are evaluated on the database servers, Crystal Reports constructs, such as parameter fields and formula fields, do not exist and thus cannot be used in the expression.

To create a SQL Expression that calculates the tax, the following expression can be used:

 `Customer`.`Last Year's Sales` * 0.07  

Notice that instead of using the {Table.Field} syntax for fields, the `Table`.`Field` syntax is used. This is because the quoted syntax is how you define fields in the SQL language.

When dropping this SQL Expression on the report and checking the SQL Query, you will find Crystal Reports has generated SQL similar to this:

 SELECT `Customer`.`Customer Name`, (`Customer`.`Last Year's Sales` * 0.07)  FROM `Customer` `Customer` 

Basically, the SQL Expression that was defined into the report is plugged into the main SQL statement that Crystal Reports generates. This means that you can use any database specific syntax or function inside a SQL Expression.

Getting back to the topic of performance, you'll remember that using the tax calculation formula field in the record selection formula resulted in all the records being returned and Crystal Reports having to locally perform the filtering. Fortunately, any SQL Expressions used in the record selection are always passed down to the database server. Therefore, a better record selection for filtering out customers who pay less than $10,000 in tax would be the following:

 {%Tax} > 10000  

In this record selection formula, {%Tax} is the SQL Expression discussed previously. This record selection formula would result in Crystal Reports generating the following SQL query:

 SELECT `Customer`.`Customer Name`, (`Customer`.`Last Year's Sales` * 0.07)  FROM `Customer` `Customer` WHERE (`Customer`.`Last Year's Sales` * 0.07)>10000