Crystal Reports: Parameters Optional

This is a new feature in Crystal Reports 2008 and one we haven’t had a need for until recently. Parameters can now be optional. Previously all parameters must be answered. If you wanted a parameter to select all values, you needed to enter a value and allow for it to be for "ALL" records. This new feature is a better way to make your parameter list more flexible.	Click image to enlarge
You set the parameter to Optional, on the Parameter form. Set the "Optional Value" to "True". You then need to modify your record selection formula to : not HasValue ({?Country}) or {Customer.Country}={?Country}

We think it is always important to display parameter information in the Page Heading. It is needed to show what parameter values were used to select the data in the report.

Remember you can display a single value parameter by simply placing the parameter in the Page Heading but to see more than the first value, you need to write a formula using the Join function.

If you are using Optional parameters, you can display them using a formula such as:

if HasValue ({?Country})

then "Country=" + join({?Country

Did you know you can also use parameters 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. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.