Crystal Reports 10: The Complete Reference

Parameter fields provide a good deal of flexibility for various prompting scenarios. For example, a viewer can choose multiple values for a single parameter field to allow One-Of types of record selection. Parameter fields can be specified to include entire ranges of values, so a viewer can, for example, include all orders placed between January 1, 2001, and December 31, 2001. Also, string parameter fields can be limited to certain lengths (for example, no less than three nor more than six characters ) or limited to certain formats with edit masks.

Multiple Values

Often, you may want to be able to choose more than one value for a parameter field and have the report recognize the multiple values in record selection. You may, for example, want to initially specify only one region for a report and later run the same report including ten different regions. If you re not using parameter fields, you ll need to change the Select Expert operator from Equal To to One Of and select the multiple regions .

By clicking the Allow Multiple Values check box in the Options section of the Create Parameter Field dialog box, you allow multiple entries to be added to a parameter list ”you, in essence, turn the parameter field into a single object called an array that contains more than one value. Even if you choose an Equal To operator in the Select Expert with a multiple-value parameter field, all the values in the array will be included in record selection. When you are prompted for a multiple-value parameter field, you can use the Add and Delete buttons to add or remove multiple values. The values that are added to the list can be either chosen from a pick list or typed in the text box and then added with the Add button (typing values is dependent upon the setting of the Allow Editing of Default Values check box in the Create Parameter Field dialog box).

Range Values

Crystal Reports provides range-value parameter fields, which allow you to create just one parameter field that can contain both low and high values. When this parameter field is supplied to the Select Expert with the Equals operator, it effectively supplies both the low and high values and changes the operator to Between.

To create a range-value parameter field, click the Range Value(s) radio button in the Create Parameter Field dialog box (this is the opposite of a discrete-value parameter field, which doesn t contain high/low values). This will change the way the parameter field prompt appears when the report is refreshed.

There are now two prompts to choose or enter values: the Start of Range prompt and the End of Range prompt. These two prompts behave the same way a single prompt would behave, being based on pick list creation, allowing editing of default values, and so forth. However, when the viewer clicks OK, both prompts will be supplied to the Select Expert or record-selection formula, and all records between and including the selected values will be returned.

Normally, range-value parameter fields are inclusive ; that is, the values returned to the report include the two values that are specified in the Enter Parameter Values dialog box. If you uncheck Include Value, however, the chosen value will not be included in the range. For example, if you choose the number 300 as the Start of Range and leave Include Value checked, any records including the number 300 will be included in the report. If you uncheck Include Value, anything over 300 will be included, but not 300 itself.

There are also No Lower Bound and No Upper Bound check boxes to allow you to make the range an open -ended range. If you leave both boxes unchecked (the default), the range will be limited to the finite values you enter as Start of Range and End of Range. If you check No Lower Bound or No Upper Bound (you can t select both), the corresponding range value will be discarded and the range will include only the other value. For example, if you specify a range of 100 to 1000, checking No Lower Bound will discard 100 and return records where the value is simply less than 1000 (or less than and including 1000 if you leave Include Value checked). Checking No Upper Bound will return records exceeding 100 (or equal to or greater than 100 if you leave Include Value checked).

If you drag a range-value parameter field directly onto your report to display supplied values, the parameter field will not show anything, because the parameter field is actually a range value. A range value is a single object (in this case, the parameter field) that actually contains the entire range of values specified by the parameter field. If you just put the object on the report by itself, Crystal Reports won t return a value, because it s not sure which value in the range you want to return. You can use range functions in the Formula Editor to return the first or last entries in the range. For example, the following formula will display the starting and ending dates of a date-range parameter field:

"Orders between " & Minimum({?Date Range}) & " and " & Maximum({?Date Range})

The Minimum and Maximum functions return the first and last entries in the range, respectively. Even though the Minimum and Maximum functions return date data types, the ampersand concatenation operator (&) automatically converts them to strings.

However, when either No Lower Bound or No Upper Bound is selected for a date range, the corresponding Minimum or Maximum value is displayed oddly ”it may appear to return a nonsensical or blank value. To display a more helpful message, you can use a formula to evaluate the date using the HasLowerBound and HasUpperBound functions and display text in place of the beginning or ending date:

"Orders placed between " & IIf(HasLowerBound({?Date Range}), ToText(Minimum({?Date Range})), "beginning of time") & " and " & IIf(HasUpperBound({?Date Range}), ToText(Maximum({?Date Range})), "into the future")

If, in addition to checking the Range Value(s) check box, you click the Allow Multiple Values check box in the Create Parameter Field dialog box, the parameter field will allow entry of multiple range values, or an array of ranges. For example, you could choose to see orders placed between January 1, 2000 and March 31, 2000, January 1, 2001 and March 31, 2001, and January 1, 2002 and March 31, 2002. When you are prompted for a range-value parameter field that allows multiple values, a list will appear in which you can add multiple ranges.

Different values can be specified in the Start of Range and End of Range areas and added to the list with the Add button. If you want to delete an existing range, select it in the list and click the Delete button.

This single parameter field, when supplied to the Select Expert or record-selection formula, will effectively change the selection operator to Between and One Of at the same time.

Controlling Parameter Field Data Entry

One of the issues Crystal Reports users face is how to best customize the user interface for turnkey report users ”those that aren t familiar with the intricacies of Crystal Reports. In an ideal world, the user interface will contain business rules, limits, and customized formatting to guide an end user through proper choices of parameters. While this ideal world is best provided with a customized front end program developed, perhaps, in Visual Studio .NET, Crystal Reports still gives the report developer a fair amount of control over how end users can enter data into parameter fields.

Limiting Entry to Certain Ranges of Values

For parameter field types, except string and Boolean, you can limit the range of entries that a viewer can supply. By checking Range Limited Field in the Set Default Values dialog box, you can specify a beginning and ending value for the range in the controls that become enabled below the check box. When the viewer is prompted for the parameter field, they will be unable to enter values that are below the beginning range or above the ending range.

An added feature of range limiting is the group of default values that can be added to the parameter field. When you range-limit a parameter field and choose a database table and field to help populate the Default Values list, only database items that fall within the specified range can be added to the Default Values list. If you use the double right arrow to add all browsed database values to the list, only those that fall within the beginning and ending ranges will be added. If you have already supplied values that are outside the range, a message will appear when you click OK indicating that you will lose the default values that fall outside the range.

When a viewer is prompted to supply a range-limited number or currency parameter field, they will receive an error message if they supply a value outside the range. If the range- limited parameter field is a date, time, or date/time field, the viewer will not be able to even type dates or times outside the range. The date picker will display only dates that fall within the range.

Minimum and Maximum Lengths

If you create a string parameter field, the Length Limit check box appears in the Set Default Values dialog box. By checking this option, a Min Length and a Max Length text box are enabled below the check box. You can specify the minimum and maximum number of characters that must be supplied when responding to the parameter field s prompt. If you enter too few or too many characters, an error message will appear.

If you supply a length limit, you are also restricted from adding any default values to the pick list that fall outside the minimum and maximum lengths.

Edit Masks

The most flexibility for controlling string parameter field entry comes from edit masks. An edit mask is a string of characters that controls many different aspects of data entry. One example might be an edit mask that allows only two uppercase characters to be entered (perhaps for a state abbreviation). Another example would be an edit mask that sets up the parameter field to accept data in a social security number format, accepting only number characters, and automatically adding hyphens between the third and fourth characters and between the fifth and sixth characters.

The key to using edit masks is learning the correct use of masking characters, listed in Table 14-3. Note that not only is the character you use significant, but so is the case of the character ”uppercase and lowercase versions of the same character perform different masking functions.

So, an edit mask of (000) 000-0000 when used with a phone number parameter field will require entry of the area code and phone number portions, and will include the parentheses, space, and hyphen in the parameter prompt to format the entry. The resulting content of the {?Phone} parameter value will include the literal characters, so if used for record selection it will find only phone strings formatted with exactly those literals, e.g., (204) 949-6053. If the phone number string in the database is stored as 204-949-6053, it would not match the parameter value with the parentheses, but an edit mask of 000-000-0000 would find it.

An edit mask of Password will replace characters typed in the parameter prompting field with asterisks. This is commonly used for entry of passwords or other sensitive information to prevent the information from being learned by someone looking at the screen. When the viewer clicks OK, the actual characters typed will be passed to the report as the parameter value.

If you enter an edit mask for a parameter before creating your defaults for the viewer s pick list, when you click the double right arrow button to add all possible values to the defaults list, only values that match the edit mask will be copied . For example, an edit mask of >LL used for the Region field parameter adds only two-character uppercase values to the defaults pick list, but no spelled-out region names . Edit Masks can be used to quickly select only the correctly formatted entries from a larger imported pick list.