Crystal Reports: Parameter Behavior

Use parameters to make your report more flexible

Parameters are useful objects to include in your Crystal Reports. They can be used for record selection, conditional formatting, or in calculations. They can make a simple Crystal Report much more flexible with user interaction, and raise not only the perceived value of the report but also the perceived value of the report designer.

If you have a collection of Crystal Reports where there are only small differences between each report (record selection, a group field, a minor calculation), you can use a single report and create a parameter to decide which variation to invoke.

The most common use of a parameter is in record selection where you might enter a selection formula of:

{table.field} = {?Selection}

Now the interesting thing is that this will work when the parameter is a single value, multiple values, range, or multiple value range. Crystal Reports will create the necessary database query to get the correct data.

However: {table.field} >= {?Selection} //and similarly for other relational operators

will work only if the parameter is a single value. You can’t use some relational operators if the parameter is a range or multiple values. The correct syntax for the statement above (if the parameter is a range) is:

{table.field} >= Minimum({?Selection})

The various summary functions (maximum, minimum, count, etc.) can all apply to a range or multiple values parameter.

Use a Parameter for Dynamic Groups 

We often find we have developed one useful report, and then are asked to develop a variation where the grouping is different. While it would be easy to create a second report which is a variation of the first, there is another approach you might want to consider. 

Use a parameter to select the grouping of your report. And then group on a formula that selects the required field for the group. The formula could look like:

If {?group} = “Name” then {Customer.Name} else 
If {?group} = “City” then {Customer.City} else 
If {?group} = “Contact” then {Customer.Contact} else “No Group” 

If you wish to include a numeric or date field in the grouping, it is best to convert it to a string and format it so that the sort sequence works properly. For dates, that means a “yyyy-MM-dd” format. For numbers, it is safest to place a bunch of 
leading zeros into the number. For example:

If {?group}=”Last Sale” then ToText({Customer.Last Sale Date},”yyyy-MM-dd”) else 
If {?group}=”YTD Sales” then ToText({Customer.YTD Sales},”000000000000.00”) else …. 

These techniques work with any recent version of Crystal Reports. If you have version 8 or above, you can use Customize Group Name to display a more relevant description of the group. 

Use Parameter for Dynamic Ascending or Descending Sequence.

The technique above works well, but has one limitation. The group using the formula you have developed must always be in a specific sequence (Ascending and Descending are the most common). If you wish to make the sort dynamic, you will discover that you cannot use a formula to control this.

The solution to this requirement is to have two groups, two group formulas, and a second parameter which can have the value “Ascending” or “Descending.” The first group is sorted Ascending and based on one formula: 

If {?Seq} <> ”Ascending” then “Sorted” else // continue with the formula from the hint above.

The formula for the second group does the same thing for a value of “Descending,” but otherwise is a copy of the first group formula. This technique will add an extra level to your group tree, but otherwise will allow you to dynamically control the sorting of your groups. 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.