Crystal Reports SQL Commands

As shown previously, you may see the SQL query created by Crystal Reports in the Show SQL Query dialog box. This query is designed internally by Crystal Reports based on choices you make in the Database Expert, the Select Expert, the report Design tab, and so forth. However, if you are versant in your particular database s version of the structured query language, you may find a need to create your own SQL query and base a report on it. This can be accomplished with the SQL Command.

SQL Commands allow you to create a pass-through SQL statement that performs more database-specific query processes on the database server, such as aggregate functions, UNION queries, or sub-SELECT queries. Once you ve created a SQL Command, you can base a report on it (it looks like a table) or add it to the Crystal Enterprise Repository so that other report designers sharing the same repository can base their reports on it.

Creating the SQL Command

Before you can create a SQL Command, you must log on to the database that the Command will be based on. When first creating a report, you can log on to a database from the Database Expert as described earlier in the chapter. Or if you want to create a SQL Command after you ve already commenced report design, redisplay the Database Expert with the toolbar button or Database Database Expert menu option.

Once you ve logged onto a database, notice the Add Command option that appears above the tables, views, and stored procedure categories of the Database Expert Available Data Sources list. Double-click the Add Command option to display the Add Command to Report dialog box, shown in Figure 16-8.

Figure 16-8: Add Command to Report dialog box

Begin by typing the SQL statement you wish to use for the command in the appropriate box. Note that there is no Expert or Build functionality in this dialog box ”you re on your own for typing correctly formatted SQL particular to the driver or database connection you re using. If you are unsure of proper SQL syntax, you may wish to develop the SQL in another tool provided by your database vendor. Once you ve debugged the SQL query in that tool, copy the SQL code to the Windows clipboard and paste the statement into this dialog box.

When you click OK on the Add Command to Report dialog box, Crystal Reports will submit the command to the database to be checked for proper syntax. If you have made an error, the database driver will return a message indicating the particular problem with the SQL statement submitted. The Add Command to Report dialog box will then return, where you may correct the SQL statement and click OK again. Once you ve submitted a correct SQL statement, the dialog box will close and Command will appear as a table name on the Selected Tables side of the Database Expert. If you wish to give your SQL Command a more descriptive name, select it in the Selected Tables list and then hold your mouse button down for a few seconds. This will place the command name into edit mode, where you may replace the word Command with a new name .

If you wish to view or edit the contents of a command you ve created previously, select the command in the Selected Tables list and then right-click. Choose View or Edit from the pop-up menu. Both options will display the SQL Command in the same dialog box it was created with. If you chose View, the command will not be editable ”Edit will allow the command to be changed; parameters to be added, changed, or deleted; and the command to be added to the Crystal Enterprise Repository.

Caution

To delete a command that you no longer wish to use, select the command in the Selected Tables list and click the left arrow. Be aware; this is a permanent deletion. The command won t move to the left side of the Database Expert where you can add it back to the report. It will be permanently deleted.

Adding a Parameter to the Command

As with reports, you may wish to have the SQL Command prompt a viewer for one or more variable pieces of information when it s submitted to the database. This will allow, for example, the SQL Command to run for a specified department, date range, or sales level threshold. You may accomplish this by creating a parameter in the Add Command to Report dialog box, and then placing that parameter within the SQL statement.

To add a new parameter, click the Create button next to the parameter list. The Command Parameter dialog box will appear, as shown in Figure 16-9.

Figure 16-9: Command Parameter dialog box

Supply desired options in this dialog box. You must, at least, choose a name for the parameter and a value type. The other two choices, prompting text and default value, are optional. Type a name for the parameter that hasn t been used by any other parameters in the SQL Command. And, ensure you choose a data type that matches the use you have in mind for the parameter ”if you are going to use it to compare to a numeric database field, choose Number as the value type for the parameter. Once you ve made these choices, click OK. The parameter will be added to the Parameter List.

If you later wish to modify or delete a parameter, select the parameter in the Parameter List that you wish to work with. Then, click the Modify or Delete button. Delete will simply remove the parameter from the list (make sure you also remove any references to the parameter from the SQL statement). Modify will redisplay the Command Parameter dialog box, where you may change any of the parameter elements.

Once you ve created the parameter, you must now reference it somewhere in the SQL statement for it to have any effect. For example, if you wish to replace a hard-coded number in the WHERE clause with the parameter, edit the SQL statement and replace the hard-coded number with the name of the parameter. You can either type the parameter name in directly (including curly braces around the parameter name and a question mark preceding the name) or simply place the cursor where you want the parameter name to be placed and then double- click on the parameter in the Parameter List.

Thus, a previous WHERE clause with a hard-coded number:

 WHERE "Order Amount" > 1000

can be replaced with a parameter-driven version:

 WHERE "Order Amount" > {?Order Limit}

Once you ve added one or more parameters to the SQL Command, click OK. You will then be prompted to supply values for any parameters, and again, the command will be submitted to the database to check for proper SQL syntax. Any errors generated by adding the parameter will appear in an error message, and you ll be returned to the Add Command to Report dialog box, where you may fix the problem. If the command is syntactically correct, the Database Expert will reappear.

Adding the SQL Command to the Repository

Once you ve created the SQL Command and clicked OK, it will appear in the Selected Tables list of the Database Expert and will be treated as a table when you continue with report design. After you save the report, the SQL Command will still be in the Database Expert the next time you open that one particular report. However, if you want to use the same SQL Command in other reports, or if you want to share it with other report designers in your organization, you must add it to the Crystal Enterprise Repository.

To add a SQL Command to the repository, right-click on the SQL Command in the Selected Tables box in the Database Expert. Choose Add to Repository from the pop-up menu. If you haven t already done so, you ll be prompted to log on to Crystal Enterprise. Then, the Add Item dialog box will appear, as shown in Figure 16-10.

Figure 16-10: Add Item dialog box

Type the name you want the SQL Command to have in the repository in the first text box. The author and description entries are optional ”they will be filled in with your Crystal Enterprise user ID and the SQL statement that makes up the command. You may change these if you choose. Then, choose the location in the repository where you wish to place the command. The location can be the root level of the repository (select the name of the Crystal Enterprise server at the very top of the list), or a subfolder within the repository, if folders have been added. If you click the plus sign to open a particular folder, you ll see any existing SQL Commands that have been added to that folder. If you select just the folder itself, the command will be added to that folder. If you select an existing command within a folder, it will be replaced with the command you are currently adding.

Once you ve added the command to the repository, you ll see some changes to the way the SQL Command appears in the Database Expert. In particular, you ll notice the database name will change to the word Query in the Selected Tables list, as well as a new item labeled Query in the Repository category within the Available Data Sources list. You ll also notice a small link icon next to the SQL Command in the Selected Tables list indicating that the SQL Command is connected to the repository ”you won t be able to edit the command while it s still connected.

The SQL Command will now be available to use in other reports ”both by you, and by anybody else who is connected to the same repository (information on how to use the repository-supplied command follows later in the chapter). If, however, you wish to change the command in the repository to reflect some change in requirements, you must first disconnect the command from the repository before you can change it.

To disconnect a SQL Command from the repository, right-click on the command itself (right-clicking on the higher-level barrel object won t provide the proper option) and choose Disconnect from Repository from the pop-up menu. The small link icon next to the barrel will disappear, indicating that the command has been disconnected from the repository. You may now edit the command by choosing the Edit command from the same pop-up menu.

Once you ve made changes to the command, don t forget that the changes will reside only on the current report ”you must replace the command in the repository with the updated command on your report. To replace the repository command, just right-click on the SQL Command and choose Add to Repository from the pop-up menu. The Add Item dialog box (shown in Figure 16-10) will reappear. Just choose the existing SQL Command you wish to replace in the folder list to replace the repository command with the updated command on your report.

Tip	More information on the Crystal Enterprise Repository, including how to set up the repository, create folders, and so forth, can be found in Chapter 7.

Using a SQL Command in a Report

Once you ve created a SQL Command, it will appear in the Selected Tables list of the Database Expert just like a native table from a database. Once you close the Database Expert, any fields that the SQL Command returns will appear in the Field Explorer ”just drag and drop them onto the report as you would any other database field.

You can even link a SQL Command to another database table in the Database Expert before you proceed to report design. And, you can add an additional table to the report later, linking it to the existing SQL Command on one or more common fields. If you link additional tables to the SQL Command, be aware that you ll be limited in your join and link type choices, as described earlier in the chapter under Using Multiple Database Types in the Same Report.

Using a Parameter-Based SQL Command

If you added one or more parameters to your SQL Command, you ll find that the command will prompt you for parameter values as you create the command, and later when you refresh a report based on the command. Notice, also, that the parameter will appear in the Field Explorer as though you added it to the report (Chapter 14 discusses report-based parameter fields).

When you refresh the report, you ll be prompted to choose whether to rerun the report with existing parameter values or to specify new parameter values. If you choose to specify new, the familiar parameter prompt will appear asking for values for the SQL Command parameters. You can even place the parameters you added to the command right on the report, or use the command parameters in report formulas.

Caution

When you create a SQL Command “based parameter, you don t have the choice of advanced parameter types, such as multi-value or range. Even if you edit the resulting parameter from within the Field Explorer, you probably won t be able to make such choices. You can, however, click the Set Default Values button to supply a pick list of default values, set length limits or an edit mask for string parameters, or set range limits for number or date parameters. See Chapter 14 for more information on these options. You can even rename a command-based parameter in the Field Explorer the same way you would rename a regular parameter field (although this won t change the name of the parameter in the SQL Command).

Using a SQL Command from the Repository

If you ve added a SQL Command to the repository using steps described previously in the chapter, the command will be available to other reports you design, as well as to others who are connected to the same repository. When you initially display the Database Expert, simply click the plus sign next to the Repository category of the Available Data Sources list. If prompted, log on to Crystal Enterprise. Then, navigate to the desired folder in the Crystal Enterprise Explorer. Only SQL Commands will appear within the folder ”other repository objects such as text objects and bitmap images won t appear. Either double-click on the SQL Command you wish to add to the report or select it and click OK. The command will then appear in the Available Data Source list with a lower-level category object labeled Query. Click the Query entry for the desired SQL command and use the right-arrow button to add it to the Selected Tables list. You may now add and link other tables, or click OK to close the Database Expert and add the repository-based command to the report.