SQL Expression Fields

Chapter 11 - Using SQL in Crystal Reports
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

The COUNT function was introduced earlier as a keyword used with a SQL SELECT statement and resulted in a SQL expression. Referring back to Figure 11.8, you’ll recall that a SQL expression is a derived value based on other database fields or attributes and that the result displays in a column with a placeholder column name. So the idea behind a SQL expression is that it is a calculation based on stored values but is not a stored value itself.

Within a report, Crystal Reports provides this type of calculated value in the design environment through the use of SQL expression fields in the Field Explorer. If the database is SQL-capable and has been connected to using ODBC, OLE DB, or SQL native drivers, then the SQL Expression Fields category displays in the Field Explorer, as shown in Figure 11.10; otherwise, this category does not exist.

Figure 11.10. SQL Expression Fields

This type of field is a calculation written using SQL functions, not formula language. These database functions are passed to the database server for processing and return a result to Crystal Reports through the SQL expression field.

To create a new SQL expression field, right-click to display the context menu and choose New, or click the New icon at the top of the Field Explorer. After giving the field the name of your choice, the Formula Workshop - SQL Expression Editor opens and displays only the database functions that apply for the type of database you are using. It builds the list of functions dynamically after detecting the database type and connection type. Figure 11.11 shows a partial look at the database functions that are present when you connect to SQL Server using OLE DB.

Figure 11.11. SQL Expression Editor

One of the database functions available in a SQL Server database allows you to pick up the name of the currently logged-in user from the operating system. Figure 11.11 shows the coding of this expression in the coding area of the editor. Note that all you need to provide is the function call itself; even though it looks incomplete, it is an expression that returns a result, and that is exactly what is needed here. As with other areas of the Formula Workshop, double-clicking the User()function in the list above adds it to the editing area below. Once the function is written, check and save it as you do with other code, and then drag and drop it onto your report to display its value.

You use SQL expression fields to perform calculations that are a native part of a database’s language but that would take a great deal of coding effort to re-create in Crystal Reports.

Note 

There is no built-in help in Crystal Reports to assist you with these functions; they are database functions, not Crystal functions. To learn more about how to use database-specific functions and which ones are available in which database, refer to the database documentation provided by the software vendor.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback