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
