SQL Commands
If you’re using an ODBC, OLE DB, or SQL native driver connection, a new feature built into Crystal Reports 9 lets you type a SQL SELECT command directly to create a result set to use as a data source. The result of the query appears to Crystal as a table object complete with fields of data that can be added to a report.To begin the process of creating a new SQL command in Crystal, first choose the type of connection you want to make and then double-click the Add Command option in the Database Expert. Figure 11.12 displays this option for the VistaNations database using an ODBC connection.Note | If you do not see an Add Command option for your data source, it is most likely not an ODBC, OLE DB, or SQL native driver data source. |
Figure 11.12. Adding a SQL command
Choosing this option opens the Modify Command dialog window, shown in Figure 11.13. Here, you can type directly the SQL SELECT command to use as the basis of the report. Note | At the bottom left of this dialog is the Add To Repository option. Enabling this option allows you to save this SQL command to the Crystal Reports repository for use in other reports. |
Figure 11.13. Modify Command dialog
After typing the SQL command, click the OK button save it and use in this report. Closing the Modify Command dialog returns you to the Database Expert and places the new command in the list of Selected Tables, as shown in Figure 11.14. You can modify the SQL command at any time by double-clicking the Command table object in the Selected Tables list to reopen the Modify Command dialog.Figure 11.14. Selected Tables
The Field Explorer treats the result of the SQL command as a table object and provides access to its fields, as shown in Figure 11.15. Notice that the SQL expression name remains since this calculation was performed in the query and doesn’t tie back to an actual field stored in the table. The resulting report using the three fields is shown in Figure 11.16.Figure 11.15. Field Explorer
Figure 11.16. Report based on a SQL command
Wasn’t that simple? If you have a good grasp of creating SELECT statements, you have the power of SQL commands at your fingertips for bringing data in Crystal Reports.
Parameterized Queries
The more generic a query is, the more reusable it becomes. One way to make a query reusable is to pass parameters to the query at runtime instead of hard-coding the values at design time. A SQL command can use a parameter list in which you provide the following:
A name for the parameter
The text you want to display for a user to ask for a data value
The data type of the value you’re expecting the user to provide
A starting default value for the parameter
Take a peek back at Figure 11.13. On the right side of the screen is a Parameter List. To create parameters to pass to this query, we’ll use the Create button, which is next to the Parameter List. Clicking the Create button opens the Command Parameter dialog, where you provide all the necessary information for the parameter. We’re going to convert the earlier query to a parameterized query that prompts the user to indicate whether they want to look for five-star resorts or not. Figure 11.17 shows the Command Parameter screen filled out and ready to go.