Crystal Reports: An Easy Way to See the SQL Behind Your Report

If you have been using a SQL database but never really understood what SQL was or what it looked like, then here is an easy place to start.

If you have a report that uses an ODBC/SQL data source, look at "Show SQL query" in the database menu. This will display the SQL that Crystal Reports sends to the database server to retrieve records.

Look at the SQL each report creates and see the SQL the report writer generates. This is an easy way to learn this database language.

You can make some changes here, but we don’t recommend it because changes to selection criteria in SQL override the Select Expert and Selection Formula, and we believe those are more flexible and powerful tools.

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)

You can also use SQL to retrieve 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. 

SQL Designer

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.