Creating Advanced Record Selection Formulas

Although creating a simple report can be very useful for an end user, highlighting notable information can increase the utility of the report because it saves time spent looking for trends and crucial data. Outliers, data that falls above or below the average of a specified threshold, often contain key information.

This chapter focuses on drawing attention to key data by using record selections and introducing SQL expressions, and introduces report alerting.

Although many filters are simple enough to be defined using the Select Expert, most real-world reports require editing the record selection formula itself. Before covering the best practices for creating formulas, review the material on record selections introduced in Chapter 2, "Selecting and Grouping Data."

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 opening the Report menu and choosing Selection Formulas, Record. A simpler way to build record selections is to use the Select Expert icon accessed via the Experts toolbar.

A record selection formula 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.

After this quick review, the following sections move on to some of the more important topics in creating record selection filters.

Displaying Record Selections

Although the techniques discussed in this chapter allow powerful filtering, this should not be applied without the end user's knowledge in most cases (certainly there are cases where end users should not know of hidden data, but this more often is handled via filters to secure the data in a Business View). In these cases, you can simply display the current selection filter or other special fields by either dragging the fields onto the report from the Field Explorer, Special Fields area, or by using the same fields in a formula to change the way these fields display as per the previous chapter.

Dealing with Dates

Use the select expert, which you access by choosing Report, Select Expert, to manipulate dates with the addition of four specific date-related comparators:

• Is between The selected items fall between these two dates.
• Is not between The selected items do not fall between these two dates.
• Is in the period The selected items fall in the periods selected from the drop-down list.
• Is not in the period The selected items do not fall in the periods selected from the drop-down list.

These date-specific comparators appear when you have selected a field of date type, and prompt for the specific values. However, in more complicated situations, you are forced to create a formula in the formula editor rather than using the select expert to create the formula for you.

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 becomes more complicated when the data types of the values to be compared are not the same. Filtering data based on dates often causes this type of situation; for instance, this formula:

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

NOTE

When clicking the Check button to check the formula's syntax, Crystal Reports provides a message saying "A date-time is required here" and after closing the message box, "1/29/1998" is highlighted. Because the Order Date field has a data type of date-time, the formula attempts to compare a date-time to a string, which is not 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)

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 needs 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 capability to omit the time portion. When this is 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 accomplish 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.

NOTE

A user function library is a library of functions that can be used from the Crystal Reports formula language. Business Objects 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 expert. 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.

NOTE

An array in the context of the Crystal Reports formula language is a collection 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.

NOTE

Although string arrays are 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 from which to choose.

Pushing Record Selections to the Database

When dealing with large sets of records, performance becomes important. The record selection used makes a significant difference in report performance. 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 operations results in a faster overall transaction. Because of this principle, Crystal Reports attempts to ask the database to perform these operations if possible.

In the context of record selections, when Crystal Reports queries the database it attempts 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 meet the criteria. However, because the SQL language doesn't support all the Crystal Reports formula language, 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 back-end server and Crystal Reports resides in your desktop machine, the difference becomes much more apparent. This is partly because of network traffic. There's a big difference between sending 50 records back over the network and sending 100,000. This performance hit becomes 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 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.

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 whether 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 11.1.

Figure 11.1. The Show SQL Query dialog displays the actual SQL code used to retrieve the results from the relational database.

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, if 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. A 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 usewhich would produce the same results, but performs the filtering on the database serverwould 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 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 enable 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 chapter, they can be a factor in report processing performance. SQL expressions provide an alternative 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, 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 Chapter 4, "Understanding and Implementing Formulas." 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 11.2).

Figure 11.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 inserting this SQL Expression into 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'

The SQL Expression that was defined in the report is inserted 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

NOTE

Remember that any formula evaluated after the first pass of the multipass system, for instance grouping criteria or information to prompt a subreport, can cause slow report processing. Again a SQL Expression can retrieve the correct data in the first place, speeding report processing significantly. The next chapter of this book covers the multipass system in detail.