Using Parameter Fields with Formulas | Crystal Reports 10: The Complete Reference

There will be many situations in which you need to prompt for a parameter field in a certain way or choose a particular value type for a parameter field so it will work properly with record selection. However, you might want to display the parameter field on the report in a different way, perform some calculation based on the parameter field, or otherwise manipulate the parameter field in a formula. Or, you may want to provide some variable information to a formula, such as a sales tax rate. By setting up a parameter field to prompt for the tax rate, it s very easy to run the report for different states or cities that have varying sales tax rates.

When you create a parameter field, it will appear in the Formula Editor s Field Tree box under Report Fields. It can be added to a formula just like a database field or another formula. Just remember the value type you choose when creating the parameter field ”you must use it correctly inside the formula to avoid type-mismatch errors. You may need to use ToText, ToNumber, or other conversion functions.

Using a Parameter Field for Partial Text Matches

A handy capability of the Select Expert is the Like operator that can be used when selecting records based on string database fields. Like allows you to supply wildcard characters , such as the question mark (?) and asterisk (*) to indicate single-character and partial-field matches, respectively. For example, supplying a Select Expert operator of Like with the literal ?eor?e would return George, Jeorge, Jeorje, or Georje. Using the Like operator with an asterisk in the literal Je* would return Jean, Jenny, Jennifer, and Jerry.

Note	A more complete discussion of record selection is contained in Chapter 8.

By allowing question marks and asterisks to be included in parameter fields, and by using the Like operator in the Select Expert, you give your report viewer great flexibility in choosing only the records they want to see. However, if you want a meaningful message to appear on the report indicating what the user has chosen , you ll need to use a formula to display different information than the parameter field actually supplies to the Select Expert.

Consider a parameter field called Customer Name that will prompt the viewer to add question marks or asterisks for partial-match searches:

If you wish to place a descriptive message in the page header indicating which customers have been chosen, you can create the following formula:

 If {?Customer Name} = "*" Then 
 "All Customers" 
 Else 
 If Instr({?Customer Name},"?") > 0 or 
 Instr({?Customer Name},"*") > 0 Then 
 "Customers matching the pattern " + {?Customer Name} 
 Else 
 "Customer: " + {?Customer Name}

This formula simply uses the Customer Name parameter field as you would another database or formula field in an If-Then-Else statement. If the parameter field contains only an asterisk, it will return all records, so the formula returns All Customers. Otherwise, if the parameter field contains at least one asterisk or question mark (the Instr function will return the location of the first occurrence of the character, or zero if there isn t any occurrence), the formula indicates that the report is based on a partial pattern. Finally, if there are no asterisks or question marks at all, the report will be returning an exact text match to the parameter field, and this formula indicates that.

To make it easy for the report to default to all customers without the viewer knowing or remembering to enter an asterisk in the prompt, you may wish to add it as a default value for the parameter prompt, with the description All Customers.

Note	If you make use of this type of parameter field in the Select Expert, remember that you ll need to use the Like operator instead of Equal To. This will ensure that record selection interprets asterisk and question mark characters properly.

Using a Parameter Field to Change Sorting or Grouping

You may often wish to have a report viewer choose the report sorting or grouping on the fly. Since a parameter field can t actually return a database or formula-field name itself, you need to create a formula based on the parameter field, and use that formula as the sort or group field in the report.

You could, for example, create a parameter field called Group By that prompts for grouping by Country, Region, or City. These would be the only three options, as the viewer will not be allowed to edit the default values. The prompt for this parameter field would look like this:

Because the parameter field will contain only the string Country, Region, or City, you can t use the parameter field directly as a sort or group field. You need to create a formula based on the parameter field and supply it as the sort or group field. Look at this formula:

 If {?Group By} = "Region" Then {Customer.Region} 
 Else 
 If {?Group By} = "City" Then {Customer.City} 
 Else {Customer.Country}

This will actually return a different database field based on the parameter field s value. Then, this formula can be supplied as the grouping or sorting field, and the group or sort will change according to the viewer s response to the parameter field.

Tip

You may pay a slight performance penalty when you use this method with SQL or ODBC databases. Since the sorting or grouping will be done with a formula field and not directly with a database field, the ORDER BY clause won t use this formula, requiring Crystal Reports to actually sort the data once it arrives from the database server.

Using a Parameter Field to Control Top N Reporting

Top N reporting (covered in more detail in Chapter 3) accepts a hard-coded value for N. That is, if you want to see the top 5 groups, or the bottom 10 groups, you must choose Top N or Bottom N and type an actual 5 or 10 into the Group Sort Expert dialog box.

There may be instances, however, where you desire to supply the value of N with a parameter field. You ll soon discover, though, that there s no way to place a parameter field value in the Group Sort Expert text box for N. If you try to type the parameter field in manually (starting with the curly brace ), you won t achieve success ”the text box only accepts numbers . While you may be convinced that this renders Top N via a parameter field only a dream, some creative reporting techniques can still be used to achieve the same goal.

To approximate the same behavior, perform the following steps:

Rather than choosing Top N or Bottom N in the Group Sort Expert dialog box, choose Sort All. Then, choose the summary field you want to use to control top or bottom N grouping in the based on drop-down list, and choose Descending order to show Top N values (high to low), or Ascending order to show Bottom N values (low to high).
Create a parameter field to accept the value of N from the report viewer. The parameter field should be set as a number value type.
Conditionally suppress all group sections (such as the group header and footer) that you don t want to appear for any groups other than N. Do this by adding the following formula using the conditional suppression button in the Section Expert ( assuming that your parameter field is named {?Top N Months}). Note the use of the GroupNumber function from the Print State category of the Functions box in the Formula Editor ”this function returns a sequential number for each group displayed on the report.
```
 GroupNumber > {?Top N Months} 
```

The result will be groups appearing in Top or Bottom N order (depending on whether you chose Descending or Ascending order in the Group Sort Expert), with groups being suppressed if the GroupNumber function exceeds the value of the parameter field.

While this technique will, in effect, allow Top N reporting with a parameter field, it introduces an additional challenge if you want to show grand totals at the end of the report for only the Top or Bottom N groups. As conditional suppression occurs after grand totals or running totals in the Crystal Reports processing cycle, you can t use them to calculate totals for only the Top or Bottom N groups displayed in this manner. Instead, you will need to create your own manual running total formulas, utilizing variables .

Tip	Chapter 5 contains more information on creating formulas with variables. Also, an example of this technique, including a proper totaling formula, can be found on the book s companion web site (www.CrystalBook.com). Look for Top N-w Parameter Field.rpt.