SQL Commands

Chapter 11 - Using SQL in Crystal Reports
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

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.

Figure 11.17. Query parameter

From this point, all you need to do is modify the SQL query to reference the name of your parameter. SQL Command parameter fields are referenced with their name preceded by a percent sign (%). When the report is opened and before the query is sent to the database, the person opening the report is prompted to supply a data value using the prompting text you supplied. The query is then formulated using the values specified by the user, and the query is sent to the database.

The Modify and Remove buttons in the Modify Command dialog shown in Figure 11.13 can be used to make changes to a parameter field after it has been created.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback