Working with SQL Statements

Reports connect to databases which in turn return a set of records. The portion of the report’s design that works with data is translated into a SQL statement that is syntactically valid for each specific data source.

Crystal Reports gives you many ways to customize the SQL statement that is passed to the database. This can be done while designing a report as well as during runtime. A few reasons for customizing the SQL is to create more sophisticated SQL queries, increase the database’s performance, or to perform runtime customization according to a user’s input.

Crystal Reports breaks a SQL statement into three distinct parts: table selection, filtering records, and sorting/grouping. Each of these parts is identified by a SQL keyword. The SELECT keyword specifies the tables and fields to use. The WHERE keyword specifies which records should be included and which should be filtered out. The SORT BY and GROUP ON keywords specify how to perform the sorting and grouping of the records. The following sections explain these three parts of the SQL statement and show the options for customizing them.

Selecting Tables and Fields

The Database Expert dialog box makes the selection of tables and fields a piece of cake. You get to select the tables to use, how the tables are linked, and the fields to print by nothing more than a mere point and click action. As to the final SQL statement that your report passes to the data source, this is the SELECT part of the SQL statement.

There are times when doing simple joins between tables isn’t sufficient for your reporting needs. For example, Crystal Reports doesn’t support the UNION statement for doing a non-linked merge of two tables. In these circumstances, you can specify your own SQL statement rather than use the one that Crystal creates.

The SELECT portion of the SQL statement can only be customized while the report is being designed. There is no functionality in Crystal Reports to modify the SELECT statement during runtime.

When creating custom SQL statements for a report, it’s critical that you are well versed in the proper syntax for the database you are using. Although most databases state that they are ANSI SQL-92 compliant, there are minor differences between each implementation as well as enhancements to the standards. You should, if you aren’t familiar with these differences, familiarize yourself with the SQL language reference guide that came with the database.

A good way to learn how SQL works is to look at the SQL statements that Crystal creates for your reports. Right-click on the report in design mode and select Database | Show SQL Query. This opens the dialog box (Figure 13-8) that shows the SELECT portion of the SQL statement that is passed to the database.

Figure 13-4. The Show SQL Query dialog box.

This dialog box displays the SELECT portion of the SQL statement, but it doesn’t let you change it (it is read only). Writing a custom SQL statement for your report requires creating a new data source based on a Command object rather than modifying an existing connection.

Enter a custom SQL statement by opening the Database Expert and selecting the OLE DB (ADO) category and creating a new connection to the server. If the connection you want already exists, you can use it. Once the connection is created, double-click on the Add Command option to open the Add Command To Report dialog box. Enter a SQL statement and click OK.

Figure 13-5. The Add Command To Report dialog box.

The new command is listed under the connection node. And the available fields are listed under the command node. At this point the command is treated as a standard data source and you can select the fields you want to appear on the report.