| 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 field names that 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 Amount` AS 'Amount', `Orders`.`Order Date` AS 'Date' 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 blank report. After opening Crystal Reports, click on the New option from the File menu and select Blank Report. | 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 11. 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 Add Command option. The Modify Command window pops up. Copy the SQL Command from Notepad into the box as shown in Figure 1.32. |
Figure 1.32. The Modify Command 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 four 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 that 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. Empowers SQL Experts. If you are a SQL Expert or have them in your organization, you can leverage their expertise in creating optimally performing SQL through use of SQL Commands.
Caution SQL Commands cannot currently be created against native connections on Sybase, DB2, and Informix. To leverage the power of this functionality, you can create an ODBC connection against these data sources and then create a SQL Command against that ODBC source. |