Crystal Reports in the Real World—SQL Commands
Experienced report developers will notice that the sample database is very simple (only a dozen tables) and that all the fields in the tables have useful names. In practice, it's very common for a database to have many more tables with very complex relationships and that the field names are not descriptive. This is where SQL Commands can help. This section explores the advantages of using SQL Commands to create reports. To take SQL Commands for a test-drive, follow these steps:
1. | Open Notepad and type the following lines of SQL exactly as they appear here: SELECT
'Customer'.'Customer Name' AS Name,
'Customer'.'City' AS City,
'Orders'.'Order Date' AS OrderDate,
'Orders'.'Order Amount' AS Amount
FROM
'Customer' 'Customer' INNER JOIN 'Orders' 'Orders' ON
'Customer'.'Customer ID'='Orders'.'Customer ID'
This is the SQL statement that will be used in the report. |
2. | Select a report template. After opening Crystal Reports, either click on the New button or access the New option from the File menu. Once in the Crystal Reports Gallery dialog, select As a Blank Report and click OK. |
3. | Select an appropriate data source. From the Database Expert dialog that opens, in the Available Data-sources list, browse to Create New Connection, ODBC. As soon as you choose ODBC, the ODBC (RDO) dialog pops up. Scroll until you find the Xtreme Sample Database 10. Select it and click Finish. (There are no other settings to get this database working, so you can ignore the Next button. These additional options will be discussed in a later chapter.) |
4. | Rather than selecting tables, double-click the SQL Command option. The Add Command To Report window pops up. Copy the SQL Command from Notepad into the box as shown in Figure 1.32. Figure 1.32. The Add Command To Report window with the SQL Command pasted into the text box. |
Click OK; notice that there is no need to link the tables because the SQL Command already defines the relationship between the tables. Notice also that there are only four fields to choose from and that the names have been changed.
The SQL Command does three things:
Hides database complexity. Converts many tables into one view with the table relationships defined.
Hides unnecessary fields. Many database fields are ID fields that simply aren't intended to be seen by users. The SQL Command can be constructed so these fields don't pass through.
Renames database fields. Database field names are often unreadable and give no hint about what they contain. The SQL Command can rename these obscure names into something more meaningful.