Best Practices When Using Commands with Crystal Reports

In version 9 and newer Crystal has a feature called a “Command”.  A Command is a SQL Select statement that the user can write to extract data for a report.  Commands are available for most, but not all, databases.  To determine whether a Command can be created for a specific type of connection, open the connection in Crystal; the first line under the name of the open connection will be “Add Command”.

Commands use the SQL syntax of the database that is being connected to. So, for example, when connecting to MS SQL Server, the “getDate()” function is used to get the current date but in Oracle it would be “SysDate”.  Crystal formulas cannot be used in a command, so any calculations or filters must use SQL syntax.

What follows are some best practices for working with Commands in Crystal.

A Command should include ALL of the data required in a report.

When a Command is linked in the Database Expert with other tables or Commands, Crystal is unable to push the joins to the database for processing. Instead, all of the data from the Command along with all of the data from the tables is pulled into memory where Crystal will process the join in memory.  With inner joins, this frequently means that much more data is brought into memory than will actually be used on the report.  As a consequence, joining Commands to tables or other Commands can significantly slow down report processing

Any data filtering MUST be done in the Where clause of the Command.

When the Select Expert is used against a command, Crystal is once again unable to push the filter down to the database and instead pulls all of the data into memory and processes the filter there.  Just like when a Command is joined to tables or other Commands, this can significantly slow down report processing.

SQL Expressions.
It is not possible to create or use SQL Expressions in the Field Explorer for the report when using a Command. Instead, the syntax of the SQL Expression  should be used in the Command itself as either an additional “field” in the Select clause or as part of the Where clause.

Parameters.

1.  Parameters that are used in a Command MUST be created in the Command Editor.  The Command Editor cannot “see” parameters that are created in the Field Explorer for the report.  However, in the Command Editor only the Parameter Name, Prompting Text, Value Type, Default Value, and whether the prompt allows multiple values can be set.  After the Command is saved, the parameters can then be modified in the Parameters section of the Field Explorer to set additional properties.

Also, if a parameter is created but not used in the Command Editor, the parameter will not actually be created for the report.  However, once a parameter is created in the Command Editor and used in a Command, it can be modified in the Parameters section of the Field Explorer for the report. In this way the parameter can be made dynamic and almost any other properties can be set.

2.  When a parameter is a string value, it must be surrounded by single quotes in the Command in order for it to work.  When a parameter is multi-select, use “in” instead of “=”.  So, a where clause might look like this:

Where MyTable.StringField = ‘{?String Param}‘

and MyTable.NumberField in {?Multi-select Number Param}

and MyTable.DateField = {?Date Param}

3. Parameters that are used in Commands CANNOT be optional.   Instead, I generally use a default of “*All” (the asterisk causes this to sort to the top in dynamic parameters) to indicate that all values should be selected.  In the Where clause, it would be used something like this:

Where (‘{?String Param}’ = ‘*All’ or MyTable.StringField = ‘{?String Param}’)

4. DO NOT use the Command that provides data for the report to provide data for dynamic parameters.  The parameters will not show any field values until after the Command has been run. Instead, do one of the following:

a.  If the report is being run through BusinessObjects (BOBJ) or Crystal Reports Server (CRS), use the Business View Manager that is installed with the Client Tools to create the List of Values that will provide data to a dynamic parameter.  In general, ALL dynamic parameters for reports that are running in BOBJ or CRS should be created and managed in the Business View Manager – it’s more efficient and allows for parameters to be reused instead of recreating them for every report.  At some point in the future I plan to post a blog that walks through how to do this.

b.  If the report is not being run through BOBJ or CRS, then do the following:

• • • In the Database Expert, add a Table or Command
      that will provide the data for the parameter.
    • DO NOT link this Table or Command to anything.  Crystal will throw a warning that this is generally not supported.  This warning can be ignored because this type of configuration is supported for this purpose.
    • Use fields from the new Table or Command in the
      parameter configuration to provide data for the parameter.
    • DO NOT use fields from the new Table or Command
      anywhere else in the report.

When these best practices are followed, Commands can become a very powerful tool to provide data in Crystal Reports.  Especially since there are things that can be done in a Command that cannot be done when linking tables in Crystal.  For example, it is possible to filter dated data to show only the most recent date, use a “Having” clause to show only data that meets a certain summary threshold, work with “Union” queries, or even pivot data so that it can be shown as if it were a cross-tab without actually using a cross-tab.  Just about anything that can be done in a SQL query in a database can be done in a Command, which makes it a powerful tool for reporting.

(Edited 2/8/2022 – added info about setting parameter properties.)