Using SQL Expression Fields

Sometimes you ll want to have the database server actually perform some calculations for you, sending the already calculated field back with the database result set. This is accomplished with SQL expression fields, which can be created right from the Field Explorer. A SQL expression is like a formula, except that it s made up entirely of database fields and SQL functions that are supported by the language of the particular SQL server that you re working with. Sometimes there are advantages to using a SQL expression instead of a Crystal Reports formula. The expression is evaluated on the server, not by the client, which may improve performance.

A particular advantage involves calculations or other specialized functions that are used in record selection. If you use a Crystal Reports formula in record selection, the database server typically won t be able to perform the selection, because it doesn t understand the Crystal Reports formula language. However, when you create a SQL expression and use that in record selection, the SQL server will fully understand the expression, and the record selection will be performed by the database server.

Creating SQL Expressions

The first prerequisite for using SQL expressions is that you must be using a SQL or ODBC database. If you re using a PC-style database that doesn t utilize a SQL-based interface, SQL expressions don t apply and you won t even see the SQL Expression category in the Field Explorer. You also won t be able to use SQL Expressions if your report is based on one or more SQL Commands, stored procedures, or Business Views.

However, when you are using a direct SQL database connection, the Field Explorer includes an additional category labeled SQL Expression Fields. Click the Field Explorer button in the Standard toolbar, or use View Field Explorer from the pull-down menus to display the Field Explorer. Then, click the SQL Expression Fields category and click the New button in the Field Explorer toolbar, type CTRL-N, or right-click on the SQL Expression Fields category and choose New from the pop-up menu.

Creating a SQL expression is very similar to creating a Crystal Reports formula (discussed in detail in Chapter 5). You first are asked to give the SQL expression a name . As with formula names , you should make the SQL expression name reasonably short and easy to understand. You may include spaces as well as upper- and lowercase letters in the name. The name you give the SQL expression will also be the column heading if you place the SQL expression in the report s details section.

After you give the SQL expression a name and click OK, the SQL Expression Editor appears, as shown in Figure 16-11.

Figure 16-11: SQL Expression Editor

Note the similarities between the SQL Expression Editor and the other Crystal Reports formula editors. Creating SQL expressions is essentially the same as creating other formulas: you can type the expression directly into the Formula text box, or double-click in the top three boxes to help build the expression. Notice that, as when dealing with formulas, parameter fields, and running total fields, Crystal Reports appends a special character to the beginning of the SQL expression s name. The percent symbol (%) is used to denote SQL expression fields.2

The functions and operators available in the SQL Expression Editor change, depending on the database and database driver in use. If you look at the Function Tree box in Figure 16-11, you ll notice a certain set of available SQL functions. This example is for a report using Microsoft Access via ODBC. If you use another method to connect to the same database, or another type of database, the SQL Expression Editor will show a completely different set of SQL functions. For this reason, you will probably have to edit or modify some SQL expressions if you change your report from one database to another using Set Location from the Database pull-down menu. To get detailed descriptions of the different built-in SQL functions for your particular database or driver, consult documentation for that database or driver.

For example, you may want a report viewer to be able to specify with just one parameter field a full or partial contact name to search for. If a customer exists whose contact matches what s entered, the customer will be included on the report. In the Customer table of the XTREME sample database that s included with Crystal Reports, the contact information is actually split into separate first and last name database fields. Without using a SQL expression, you have two choices for setting up this search:

• Create two parameter fields ”one for first name and one for last name ”and try to compare those to database fields.

• Create a Crystal Reports formula that combines the contact s first and last names, and compare the formula to the parameter field.

There are problems with both approaches, however. If you choose the first option, it s much harder for the viewer to simply type a full or partial contact name, such as Chris (to find both Christopher and Christine, for example), because some additional logic will be necessary to ignore the last name parameter field if the viewer just wants to search for full or partial first names. Or if the viewer wants to see everyone whose last name is Jones, regardless of first name, then similar logic will have to apply for the first name parameter field. It would be much simpler for the viewer to be able to type Chris* or *Jones to search for their desired customers.

If you choose the second option, the full or partial searches mentioned in the previous paragraph will work; but because the first and last names are combined in a formula, the record selection will not take place on the database server (remember that most Crystal formulas can t be converted to SQL, so the client will perform record selection).

By using a SQL expression, you can have the best of both worlds . The first and last names can be concatenated into one object and compared to the parameter field. But because the concatenation takes place on the database server, it will still perform the record selection, sending only the resulting customers back to the client, not all customers.

The SQL expression to accomplish this is surprisingly similar to a Crystal Reports formula:

 `Customer`.`Contact First Name` + ' ' + 
 `Customer`.`Contact Last Name` 

Notice that a couple of differences exist, however:

• There are no French or curly braces around the field names.

• The literal string that separates the first and last name fields must use apostrophes ” quotation marks won t work.

Once this SQL expression has been created, it can be dropped onto the report just like a database field, formula field, or other object. It can also be used inside regular report formulas or in conditional formatting formulas. Notice the change to the SQL query ( viewed with Database Show SQL Query) after placing this SQL expression in the details section:

Even though the SQL expression field was given a name in the report, Crystal Reports just adds the SQL expression formula itself right into the SQL statement. The name is simply used by Crystal Reports in the Select Expert or elsewhere on the report.

To now use this SQL expression in record selection, you use the Select Expert s Like operator to allow a wildcard search on the combined first and last names of the contact. If you have already created a string parameter field called {?Contact Prompt}, the Select Expert will look like this:

When you refresh the report and are prompted for the Contact Prompt parameter field, entering the Chris* wildcard will return the following records:

Now, look at the SQL query being sent to the database server:

Notice that the WHERE clause includes the SQL expression formula text and a SQL comparison operator that allows wildcard searches. The end result: the flexibility of Crystal Reports formulas with the speed of server-based record selection.