Using SQL expressions in Crystal Reports

How adding a dash of SQL to your reports can give them a big boost in performance.

By Mike Lewis

A particularly effective way of improving performance in Crystal Reports is to use SQL expressions. In favorable cases, SQL expressions can dramatically cut the time needed to load data into a report. They can also greatly reduce network traffic and thereby improve the performance of the system as a whole.

SQL expressions work in much the same way as the more familiar Crystal formulas. You can place them directly on the surface of the report, or you can use them in grouping, selection and sorting criteria. But unlike their Crystal cousins, they are written in Structured Query Language (SQL) rather than the Crystal formula language.

Perhaps the most important point about SQL expressions is that they are executed by the database server. They are therefore most effective when used in selection criteria, as the following example will demonstrate.

Record selection without SQL expressions

To understand why SQL expressions are so beneficial, let's first look at how you might specify a record selection criterion without them.

Suppose you have an orders table, with a field named Order_Date. You want to select records from the table according to the day of the month on which the order was placed. If you didn't use SQL expressions, you might write the following Crystal formula to return the required day number:

Day ({Orders.Order_Date})

You would plug this formula into the Select Expert, specifying that you only want the report to include records where the formula equals a given day number. You might hard-coded that number, or use a parameter value. When you close the expert and refresh the report, you will see the required sub-set of orders.

If you now go to the Database menu and select Show SQL Query, you will see an SQL statement similar to the one in Figure 1. This shows the SQL code that CR uses to request the records it needs from the database.

The important point about this statement is that there is no WHERE clause. That means that all the records in the table will be retrieved from the server, regardless of whether they are needed for the report. The actual record selection will be done by Crystal Reports. Clearly, this situation is not ideal, as it means that far more records have to travel across the network than would otherwise be the case.

Show SQL dialog based on a Crystal formula

Figure 1: When selection is based on a Crystal formula,
the SQL statement lacks the vital WHERE clause

The SQL way

Now, let's see what happens when you use a SQL expression to do the record selection.

The mechanics of this are straightforward. You start by selecting SQL Expressions in the Field Explorer. Then, click the New button (or press Ctrl+N), and specify a name for the expression when prompted to do so. You will now see the SQL Expression editor, which is similar to the familiar Formula Editor.

However, instead of using Crystal or Basic syntax to write the expression, you must code it in SQL - and in the particular dialect of SQL which the database server will understand. So if your data source is Microsoft SQL Server, you would use that product's T-SQL implementation of the language to express the formula. In this example, you might use T-SQL's Datepart() function, which returns a specified element (day number, month number, day of week, etc.) from a date or datetime field.

The following T-SQL formula will return the day number within the month:

Datepart(dd,Orders.Order_Date)

Not only is a different function used in this example, the braces (curly brackets) round the field name are omitted as well. That's not a mistake. The use of braces to delimit a field name is specific to CR. SQL Server would generate an error if you used them in this context.

Now that you have created the expression, you can use it in the record selection criterion in place of the Crystal formula. So open the Select Expert again, delete the existing criterion, and create a new one based on the SQL expression.

After you have closed the expert and refreshed the report, you should see exactly the same records as before. However, if you now choose the Show SQL Query command from the Database menu, the SQL code will look something like that shown in Figure 2.

Show SQL dialog based on a SQL expression

Figure 2: The SQL statement now has a WHERE clause,
so fewer records will have to travel across the network

The big difference is that there is now a WHERE clause. This means that the record selection will be done on the server, and only records which meet the criterion will flow across the network. With a small table - a few hundred records or so - you might not notice much of a difference. But if the table contains tens of thousands of records, and if you only want to select a small percentage of them, the performance gains could be substantial.

Disadvantages

By now, you have probably realized the big disadvantage of SQL expressions: They are specific to the database. In many cases, you will be able to formulate your expressions using standard SQL - the language which is common across all SQL-based products. But in many others you will need to know the nuances of the particular database the report is talking to. It might also mean that you will have to re-code some or all of the expressions if you ever move your reports to a different back end.

Another snag is that SQL expressions are not available for all databases. You can only use them with so-called 'SQL databases', that is, SQL Server, Oracle, MySQL and the like. These are in contrast to what CR refers to as 'PC databases' like Microsoft Access. If the report is based on a PC database, you won't see an entry for SQL expressions in the Field Explorer.

Just to confuse the issue, there are a few database products for which Crystal does permit SQL expressions, but where in practice there is no benefit in doing so. A good example of this is Microsoft Visual FoxPro. Although this is a SQL database - in the sense that it can understand and execute the SQL commands which CR sends it - it is not a true client-server database. CR will send it the correct SQL code, complete with WHERE clause, but the actual record selection will be performed by the ODBC driver or OLE DB provider on the local machine. So there will be no reduction in network traffic, and no improvement in performance.

If you can live with the drawbacks, SQL expressions are definitely worth adding to your reporting toolkit, especially if you are working with large tables in a true client/server environment. Give them a try. You might be pleasantly surprised by the performance gains that they bring about.

Author's note: This article is based on one that I first wrote for my company's website in February 2004. I have re-posted the article here for the convenience of Hex Central visitors. To view the original article, see Use SQL expressions to improve performance.

December 2011

Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.

If you found this article useful, please tell your friends: