Crystal Reports: SQL Commands

Use SQL commands to retrieve your data.

A great feature introduced in Crystal Reports 9.0 is the SQL command, which you can use to retrieve your data.

Sometimes we need to manipulate our data with SQL prior to processing a report.

For example, we combine a SELECT DISTINCT, UNION join and Sub SELECT to retrieve some data from our SQL server.

The SQL command can be added to the repository, so you can share it between several reports. If you wish to change your SQL, you need to detach the SQL from the repository, make your changes and then place it back in the repository.

The SQL Command feature is why Crystal Reports 9 does not include the Crystal SQL designer. You can do far more with Crystal Reports SQL commands. 

SQL command parameters

Use SQL command parameters. Using SQL commands as the source of data for your report has several benefits. This SQL command feature also includes the ability to include a parameter in the SQL command.

For example, we have created a parameter {?Region} and use that to select a single region 
from our table.

The SQL command now says Select * from Customer where Region = '{?Region}' 

The parameter editor in the SQL command dialog box lets you enter only a single default value. So if you wish to load more default values, return to the Field Explorer and add more default values to the parameter there.

This feature supports string, date and numeric parameters, but they must be a single value. Multi value parameters are not supported.

The real benefit of this feature is where you need to extract a few records from a large table. Doing the selection in an SQL command will have a major improvement in report performance. 

Need to know more about SQL? Here's a list of SQL articles: Crystal Reports: SQL Commands (2002) Crystal Reports: SQL Commands for Creating Audit Records (2003) Crystal Reports: SQL Designer (2001) Crystal Reports: SQL Expressions and Subselects (2007) Crystal Reports: SQL For Extracting the Date Value in Crystal Reports (2010) Crystal Reports: SQL Generation to Change Selected Records (2003) Crystal Reports: SQL Introduction (2001) Crystal Reports: SQL Left Join (2004)

If you do a "Full" install of Crystal Reports, you get the Crystal SQL Designer and Crystal Dictionary programs installed. These can be very useful additions to your reporting weaponry.

Click here for an article on the Crystal Dictionary.

The Crystal SQL Designer has an expert that can gently lead you through an inquiry against your database or you can enter your SQL directly. The retrieved records can then be used as the source for a report, or exported to another format.

So why use it? There are several advantages you might discover.

1) You can save data in your QRY file. This means you execute the SQL against the main database once, and then can process your Crystal Reports against the QRY file without compromising the performance of your main database.

2) You also have the ability to send the QRY file to a remote site, so a user there can process reports without requiring a connection to your original data

3) If the Application developer doesn’t want you to have permission to make changes to the Db, you can use this tool to process the SQL you would normally use in a view or stored procedure.

4) A full set of SQL functions is available here including Summary functions, and UNION joins.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.