Using SQL Expressions

SQL Expressions are report formulas that only use SQL compatible functions. As mentioned in the last section, many formulas written with Crystal syntax don’t have a SQL compliant equivalent. Thus, the report engine has to take the data returned from the server and process it on the client’s computer. SQL Expressions alleviate this problem because they are passed directly to the server for processing. The data returned to the client computer has already been processed. SQL Expressions can be used as formulas that are used directly on the report output, or they can be included as part of the formulas.

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. Thus, you would want to, whenever possible, push as much work as possible onto the database server. The drawback to using SQL Expressions is that they aren’t as robust as formulas written with Crystal syntax or Basic syntax. Crystal Reports has an extensive library of functions that isn’t matched in other programs. The functions found in the SQL language pale in comparison to Crystal Reports. Many formulas can’t be rewritten using a SQL Expression.

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 effect on report performance because the report engine can quickly calculate these as it processes each record. The additional overhead incurred isn’t significant. SQL Expressions should definitely be used to replace formulas that are used for record selection or sorting/grouping. Both of these tasks are very resource intensive and can affect the number of records that are passed from the server back to the client. The database server is optimized to perform record selection and sorting/grouping on a large number of records very quickly. Replacing these formulas with SQL Expressions can result in noticeable improvements in report performance.

To create a SQL Expression, look in the Field Explorer window for the SQL Expressions category. Right-click on the SQL Expressions category and select New. A SQL Expression Name dialog box opens where you enter a name for the expression. Once you click the OK button the SQL Expression Editor window opens. You can see that the SQL Expression Editor looks almost identical to the Formula Editor.

Figure 13-6. 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 a more 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.

The list of available functions is specific to the database server you are connected to. If you create a SQL Expression and later change database servers, the expression may not be valid if the new database doesn’t support one of the functions used.

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 also include the SQL Expression in a record selection formula, or sorting/grouping formula, then this is also added to the query. Figure 13-7 shows how the formula is used as part of the SELECT portion of the query as well as the WHERE clause.

Figure 13-7. The SQL Query using a SQL Expression.