Viewing the SQL Query

Because Crystal Reports works with SQL databases, it must eventually translate the tables, fields, links, sorting, and grouping that you ve used to design your report into SQL. You can view the SQL statements Crystal Reports creates by choosing Database Show SQL Query from the pull-down menus .

Consider the following report Design tab that uses data from the XTREME sample database, converted from Microsoft Access to Microsoft SQL Server, accessed via ODBC. This report uses the Orders and Customer tables, linked with a left outer join and equal link on Customer ID. The join isn t enforced, but fields from both tables are included on the report. The Select Expert is limiting the report to U.S.A. customers only. Notice the fields that have been placed in the details section. Also, notice that a group based on Customer.Region has been created.

To view the SQL statement that Crystal Reports creates to query the database, select Database Show SQL Query. You will see the dialog box illustrated here.

Notice the different parts , or clauses, of the SQL statement:

• SELECT Matches the database fields that your report needs (for the details section, formulas, grouping, record selection, and so on).

• FROM Chooses the tables to use and specifies the join type for table linking.

• WHERE Supplies record selection to the server.

• ORDER BY Requests that the SQL server sort records in Customer.Region order (for the Region group) before sending them back to the client.

The SQL syntax may change, depending on the type of database you are reporting against and whether you re using ODBC or direct database drivers to communicate with it. You ll also see different syntax for joining tables. And, you may see the actual table join appear in either the FROM or WHERE clause.

Here is the exact same Show SQL Query dialog box using a native connection to Oracle (notice, among other things, that the join and link are done in the WHERE clause and not the FROM clause):

This is the Microsoft Access version of the XTREME.MDB sample database via ODBC (this is similar to the SQL Server query without the N character in the WHERE clause):

Although you may not consider yourself a database expert and might not make a habit of writing huge SQL statements off the top of your head, there is one advantage to being able to see the SQL query that Crystal Reports is sending to the server: this is useful if you are experiencing performance problems or other peculiarities when running the report. In particular, you want to ensure that as much as possible, if not all, of the report s record selection is translated into a WHERE clause in the SQL query. This will maximize the work performed by the database server and minimize the amount of data and amount of processing left for Crystal Reports to deal with once data is returned from the server. More details on performance considerations can be found later in the chapter under Performance Considerations.

Note  

In Crystal Reports 8.5 and earlier, you could directly modify every SQL clause except SELECT right in the Show SQL Query dialog box. This ability has been removed from Crystal Reports 9 and 10. However, you have complete control over the SQL Query when creating SQL Commands (discussed next ) or Business Views (discussed in Chapter 17).