Reasons to Use SAP Crystal Reports SQL Expression Fields

Overview

These objects provide an excellent alternative to using a formula in Crystal Reports. The main advantage being that the processing of an SQL Expression field is performed at the database server side and not on the client machine.

The SQL Expression functionality will vary depending on the database you connect to. One useful thing to know is that Crystal Reports supports any SQL function for the database you connect to, even if the function name is not visible in the Functions area of the SQL Expression Editor.

Figure 1 below shows an example of the Microsoft SQL Server DATEADD function.

(Figure 1)

Comparisons between a Formula and an SQL Expression field?

The SQL Expression Editor bears many resemblances to the Formula Editor. As a report designer, you will need to know the SQL Syntax to successfully utilise SQL Expressions in your Crystal Reports. Let’s take a look at the example in Figure 1. The DateAdd formula syntax is almost identical. Figure 2 below shows the formula syntax.

(Figure 2)

Report Optimisation

This is the main reason for using SQL Expressions over formulas. SQL Expression fields allow a user defined calculation in a Crystal Report to be processed on the database server. This means that the column is derived and sent to the client machine. Compare this with a formula that is wholly processed on the client machine. Figure 3 shows the SQL syntax for the DATEADD function being derived on the database server.

(Figure 3)

A valid equivalent to ‘if-then-else’

Using the SQL CASE statement is a very useful alternative to the very popular ‘if-then-else’ Crystal syntax. Figure 4 shows a simple CASE statement.

(Figure 4)

Pre-defined SQL Expression functions

These are set out in an almost identical way to the formula functions in the formula editor. Figure 5 shows the main function area.

(Figure 5)

Using a pre-defined SQL Expression function is easier because you can simply select the function, field and arguments without typing the SQL. Figure 6 shows an example of the pre-defined MONTHNAME function.

(Figure 6)

And as you would expect, it is derived on the database server. Figure 7 shows the SQL syntax below.

(Figure 7)

Written by Steve Chapman, Senior BI Consultant, DSCallards