Using SQL Expression Fields
Throughout this chapter we have been referring to SQL expression fields as the best way to improve report performance. Let’s discuss them in more detail now. SQL expression fields are report formulas that only use SQL compatible functions. As mentioned in a previous section, using SQL expressions lets Crystal Reports pass much of the workload down to the database server so that reports run faster. The data returned to the client computer has already been processed and is ready to print.
Writing reports requires achieving a balance of functionality and performance. Many reports need to use custom formulas and functions to produce the proper output. But this can result in slower performance because it requires the report engine to do more work on the client computer using SQL expressions. The drawback to using SQL expressions is that they aren’t as robust as formulas written with Crystal syntax or Basic syntax. Many formulas can’t be rewritten using a SQL Expression. The functions found in the SQL language pale in comparison to Crystal Reports.
When deciding when to use SQL expressions instead of the standard formulas, you have to decide which gives you the best cost-benefit ratio. There are three places where formulas are used: as part of the report output, in the record selection formula, and as a sorting/grouping field. You should focus your attention on using SQL expressions in formulas that are used for either record selection or sorting/grouping. Formulas that are used as part of the report output don’t have a major impact on report performance because the report engine can quickly calculate these as it processes each record.
To create a SQL expression field, look in the Field Explorer window for the SQL Expression Fields folder. Right-click on the SQL Expression Fields folder and select New. A SQL Expression Name dialog box opens for you to enter a name for the expression. Once you enter a name and click the OK button the SQL Expression Editor window opens. You can see that the SQL Expression Editor is really just the Formula Editor with new functions and operators listed. These functions and operators are specific to the database server you are connected to.
Figure 11-2. The SQL Expression Editor dialog box.
The process of creating a SQL expression is the same as creating other formulas. You select the functions to use and apply them to the listed database fields. The important difference between regular formulas and SQL Expressions is that SQL Expressions have limited functionality. The only fields that you can use in a SQL Expression are database fields. You can’t use other formulas, parameters or special fields. The available functions are also limited to SQL specific functions.
Even though it is helpful to have the list of SQL functions listed within the Formula Workshop window, you still need to be familiar with proper SQL syntax for your database. This isn’t always compatible with Crystal syntax. For example, in Crystal syntax you can concatenate two strings together using the & operator. But this will give you an error when used with SQL Server because it requires you to use the + operator.
After adding a SQL Expression to the report, the SELECT statement passed to the database server is modified to include the SQL Expression. It becomes an additional field that is requested from the server. If you include the SQL expression in a record selection formula, or sorting/grouping formula, this is also added to the query. Figure 11-3 shows an example of how the SQL expression field is now included as part of the SELECT portion of the query as well as the WHERE clause. This increases the performance of your report because the database server is going to do the work of processing the formula and filtering the data.
Figure 11-3. The SQL Query using a SQL Expression.
Using SQL expression fields whenever possible can give you a noticeable improvement in report performance.
If your database allows nested sub-queries (a SELECT statement within a SELECT statement), you can use SQL Expressions to implement them. However, you have to use parentheses around the entire statement so that it recognizes it as a sub-query. Here is an example:
