Crystal Reports 10: The Complete Reference
Parameter fields are created from the Field Explorer, which can be displayed when either the Design or Preview tab is selected. You can click the Field Explorer button in the Standard toolbar, or choose View Field Explorer from the pull-down menus . If the Field Explorer is already displayed, just click the plus sign next to the Parameter Fields category to show existing parameter fields.
If there are parameter fields in the report already, you can edit them by selecting the desired parameter field and clicking the Edit button on the Field Explorer toolbar. You can also rename or delete existing parameter fields with the Rename and Delete buttons on the toolbar. If there are no existing parameter fields, or if you wish to create a new parameter field, ensure that the Parameter Fields category of the Field Explorer is selected (click it), then click the New button. You can also right-click the Parameter Fields category of the Field Explorer and choose New from the pop-up menu. The Create Parameter Field dialog box appears, as shown in Figure 14-1; the various fields and options are described in Table 14-1.
| Field or Option | Description |
|---|---|
| Name | Name of the parameter field being created. |
| Prompting Text | Descriptive text that appears when the viewer is prompted for the parameter value. |
| Value Type | Data type to assign to the parameter field. |
| Allow Multiple Values | Check this box to allow more than one value to be assigned to the parameter field. |
| Discrete Value(s) Range Value(s) Discrete and Range Values | Choose between a single parameter field value, a beginning and ending value range, or a combination of both. You may choose Discrete and Range Values only if you check Allow Multiple Values. If you then choose Discrete and Range Values, you'll be able to enter a combination of one or more discrete (single) values and range (from/to) values. |
| Set Default Values | Displays the Set Default Values dialog box (described later in the chapter). |
| Allow Editing of Default Values When There Is More Than One Value | Check this box to allow the viewer to type values into the parameter field, even if the values are not in the drop-down pick list. |
Choose a name for your parameter field. It can be the same name as other database or formula fields, because Crystal Reports distinguishes parameter fields by preceding the parameter field name with a question mark. Choose a descriptive yet reasonably short name for your parameter field.
Although not absolutely required for parameter fields, you ll want to enter the message that will appear in the Supply Parameter Field dialog box when a viewer is prompted to provide the value. The message is entered in the Prompting Text field and should be easy to understand and helpful to the user , such as Enter the state code (2 characters only) for this report. You can enter a large amount of prompting text, although excessive prompting text will probably look unsightly when the parameter value is prompted for. Crystal Reports will word-wrap the prompting text when the viewer is prompted, if the prompting text won t all fit on one line.
Choose a value or data type for the parameter field from the Value Type drop-down list. This is a crucial step, as it determines how your parameter field can be used in record selection, formulas, and conditional formatting. For example, if you are planning on using the parameter field to compare to a string database field in the Select Expert, choose a String value type. If you are going to limit the report to a certain date range, based on a date/time field in the database, choose a DateTime value type.
These are the only items that are actually required for using a parameter field. However, there are many features in Crystal Reports that enhance the flexibility of parameter fields.
You can set up a pick list that provides one or more default values for a viewer to select. You can allow the viewer to select a from/to range of values, which is helpful for selecting beginning and ending date ranges. And if you are using a string parameter field, you can set minimum and maximum lengths for the parameter field, or use an edit mask to force the viewer to enter the string in a certain way. These features are selected in the Set Default Values dialog box. To display this dialog box, shown in Figure 14-2, click the Set Default Values button. The various options for this dialog box are described in Table 14-2.
| Field or Option | Description |
|---|---|
| Browse Table | Choose a database table to help provide default values. |
| Browse Field | Choose a database field to help provide default values. You will only see a list of fields that match the data type that you defined for your parameter field. |
| Select or Enter Value to Add | Type a value to be added to the Default Values list or select a value from the database with the scrolling list. You can select one value using the single arrow or all values using the double arrow. |
| Default Values | List of values that will appear in the parameter field's pick list. |
| Up/Down arrows | Use the up and down arrows above the upper-right corner of the Default Values list to change the order that the default values will appear in when the viewer displays the pick list. |
| Import Pick List Export Pick List | Imports an ASCII text file into the default values list or exports the values you've already added to an ASCII text file that can be used with other reports. If you have added a description it will also be exported and then can be imported into other reports. |
| Define Description | Displays a dialog box allowing you to type a description for the parameter field value that's currently selected. This is helpful when you are using coded fields for the parameter field and want the report viewer to see a description of the coded value. |
| Length Limit | Check this box to limit the length of data that can be added to the parameter field (will appear only with string parameter fields). |
| Min Length Max Length | If Length Limit is selected, enter the minimum and maximum lengths for the value to be entered into the parameter field (will appear only with string parameter fields). |
| Range-Limited Field | Checking this box allows the values a user enters into the parameter field to be forced to be within a certain range of values (will appear only with numeric or date/time parameter fields). |
| Min Value/Max Value or Start Date-time/End Date-time | Enter the minimum and maximum values or beginning and ending dates/times that a user will be allowed to enter for the parameter field (will appear only with numeric or date/time parameter fields). |
| Edit Mask | Control how information can be entered into the parameter field (will appear only with string parameter fields). You cannot define both a length limit and an edit mask. If you enter an edit mask, the length limit dialog will be grayed out. |
| Display | If you enter descriptions for parameter fields, determines whether both the value and the description or just the description appears when prompting for the parameter field. |
| Order | Choose whether not to sort the default values (leaving them in the same order you entered them here) or to sort them in ascending or descending order when prompting for the parameter field. Additionally, you can choose the type of sort (alphabetical, numeric, date-time) that you wish to use to sort the values. If you don't see the desired sorting behavior when you display the pick list, try another choice in this list. |
| Order Based On | If you choose a sort order, determine whether the order is based on the parameter field value or description. |
| Note | The Set Default Values dialog box will change, depending on the value type you choose for the parameter field, and whether you choose discrete or range values. You ll find options on this dialog box that are appropriate for the settings you choose on the Create Parameter Field dialog box. |
Setting Up a Pick List
If you don t add any default values when you first create a parameter field, the viewer will have to type in the value for the parameter field. While this sometimes may be desirable, it requires that the viewer know enough about the parameter field and the way the report and database are designed that they can type the value correctly. They may make a mistake by misspelling a name or entering an incorrect code for the field. By creating a pick list, you can let the viewer choose from a predefined list of default values.
The pick list presented to the viewer will be the set of values contained in the Default Values list (refer to Figure 14-2). You can add items to this list by typing them in the text box under the Select or Enter Value to Add label and clicking the right arrow next to the text box.
Or, you can choose a database table and field to choose values from. To do this, choose a table and field in the Browse Table and Browse Field drop-down lists. This will fill the list under the Select or Enter Value to Add label with sample data from the database. You can then select an item in the list to place in the text box. Then by clicking the right arrow, you can add the selected item to the Default Values list. If you want to add all the sample database values, click the double right arrow. If you decide you don t want some existing values to be included in the pick list, you can remove specific items by selecting them in the Default Values list with the left arrow, or you can remove all the values by clicking the double left arrow.
| Caution | While it continues to be an often- requested feature for a future Crystal Reports release, choosing a Browse Table and Browse Field in the Set Default Values dialog box will not automatically populate the parameter field s pick list with live database data whenever the viewer is prompted for the value. This table and field combination is used only to provide a list of sample values for you to manually add to the Default Values list. If the database later changes, the pick list won t reflect the changes unless you manually edit the parameter field and add new values to the Default Values list. |
You can force the viewer to choose only values from the pick list by unchecking the Allow Editing of Default Values check box back in the Create Parameter Field dialog box. If you leave this option checked, the viewer will be able to select an entry from the pick list or type in their own entry.
There are additional features that improve flexibility with pick lists. You can add descriptive values to hard-to-remember codes, as well as import or export ASCII text files that contain the values that appear in the Default Values list. You ll soon find situations in which you re using a parameter field to limit record selection based on a coded field. Perhaps you have one-letter codes to indicate colors of products; for instance, B equates to black, L to blue, R to red, and G to green. If you d prefer that the report viewer not have to remember these codes, but be able to choose the actual colors, define a description for the single-letter default values. Then, even though the user chooses Blue, the parameter field will supply the letter L to the Select Expert. Do this by selecting the default value you want to add the description to and then clicking the Define Description button.
If you have a large number of default values (and potentially descriptions) that you d like to add to your pick list, you may want to import a pick list file. When you click the Import Pick List button, you are presented with a standard File Open dialog box that asks you to select the ASCII text file containing your pick list data. When you choose the appropriate pick list text file and click OK, the Default Values list will be populated with the data from the text file. If you wish to create a pick list file from the existing values that you ve already added to the Default Values list (to use in another report, for example), click the Export Pick List button. A standard File Save dialog box will appear in which you can specify the filename for the pick list file.
Pick List File Format
If you wish to create pick list files on your own, using Notepad or some other programmatic option, such as a custom Visual Basic program, you need to know the particular file format that Crystal Reports requires for pick lists. A sample Region Pick List.TXT file that can be used with a pick list looks like this:
At a minimum, you simply need to type in the parameter field default values you want to appear in the pick list and press ENTER after each (or have your program follow each value with a carriage return/line feed combination). If you wish to add descriptions for the values, press the TAB key between the parameter field value and the description (if you re programmatically creating the pick list, a tab character can be added by inserting an ASCII value of 9).
You may optionally add one or two directives at the top of the file. Directives are words that control how Crystal Reports will display the pick list ”showing either values and descriptions or descriptions only ”and how to sort the pick list. Begin a directive with four percent signs, followed by the name of the directive. Directive names are Sort and ShowDescOnly . Then, press the TAB key and type in a value for the directive.
The Sort directive accepts four values:
| aa | Sort the pick list in ascending order using an alphabetical sort. |
| ad | Sort the pick list in descending order using an alphabetical sort. |
| na | Sort the pick list in ascending order using a numeric sort. |
| nd | Sort the pick list in descending order using a numeric sort. |
| da | Sort the pick list in ascending order using a date time sort. |
| dd | Sort the pick list in descending order using a date time sort. |
The ShowDescOnly directive accepts two values:
| 1 | Show Descriptions only. However, the actual code matching up to the chosen description (the value) will be provided to the report. |
|
| Show Values and Descriptions. Will show both the code and descriptions for the code. |
| Tip | Crystal Reports generally treats the first item in a pick list as a default value that is used if the viewer doesn t make a selection for that parameter when prompted. It may help the viewers if the first pick item for every parameter is the most commonly used criterion, especially if there are a lot of parameters in a report. |
Responding to Parameter Field Prompts
Once you ve created a parameter field, you will be prompted for it the first time you preview the report after the parameter field has been created. The Enter Parameter Values dialog box will be displayed.
If you haven t entered any default values or created a pick list, the viewer will need to type their response to the prompt. If you ve created a pick list, the prompt will be a drop-down list from which one of the predefined values can be selected. If you have created more than one parameter field for the report, only one Enter Parameter Values dialog box will appear, but each parameter field will appear in the list. Click the parameter field you want to select.
| Tip | Typically, parameter fields will appear in the Enter Parameter Values dialog box in the order you created them. If you d like to change the order in which they appear, return to the Parameter category of the Field Explorer and select the parameter field you wish to move. You can then either use the ALT- up arrow or ALT -down arrow keyboard combination, or right-click and select Move Parameter Up or Move Parameter Down from the pop-up menu. |
When you refresh the report, the Refresh Report Data dialog box will ask whether to use the currently set parameter field values or to prompt for new values. If you use the current values, the database will be reread with the current values in the parameter fields. If you choose to prompt for new values, the Enter Parameter Values dialog box will appear again, and you may specify new values for any necessary parameter fields.
| Tip | When you refresh the report and select Prompt for New Parameter Values, version 10 no longer requires you to resupply all parameter values. The information you had placed in them previously is now retained. You may simply click OK to accept all of the previous values, or make only desired changes to certain values or certain parameter fields. If you don t wish to retain any parameter values from the previous usage, click the Reset button. |
Value Type Considerations
The value type you choose for your parameter field determines how the parameter field can be used in the rest of the report. If, for example, you need to compare a parameter field to a date database field, you ll need to use a date parameter field. The value type will also determine how the report viewer must respond to the parameter field prompt.
String and number/currency parameter fields are fairly straightforward. In the case of strings, a viewer can respond with any combination of letters , numbers, or special characters. For numbers, only the numbers 0 through 9 and a minus sign can be used ”other characters will result in an error message. Using date, time, date/time, or Boolean parameter fields will introduce some new features and limitations.
| Tip | Ranges and edit masks can be used to limit or format what a viewer can enter into a parameter field. These special features are discussed later in this chapter. |
Dates and Times
You ll often want to use date or time parameter fields to limit your report to certain date or time ranges. You can choose value types of Date, Time, or DateTime. You can build a pick list for these value types just as you can for number or string fields. And if you leave Allow Editing of Default Values checked in the Create Parameter Field dialog box, you ll be able to choose dates or times other than those in the pick list.
When you are prompted for a date or time parameter field, a few special features are available to you:
If you didn t check Allow Editing of Default Values when you created the parameter field, you ll only be able to click the drop-down list and choose from the pick list. If you did check Allow Editing of Default Values when you created the parameter field, you can just type a date and/or time value into the prompt.
Preformatted dates and times will appear in the dialog box ”just type the correct date and time over any existing default value. If no pick list or default has been specified, or no previous value remains from an earlier report refresh, the current date and time will appear in the prompt. These can be left as they are, or you can use them as a guide for typing in the correct values.
If a pick list is available, click the down arrow to see the available values in a drop-down list. There may also be a Pick from Defaults check box available if Allow Editing of Default Values was checked when the parameter field was created. If you uncheck this option, or if there is no pick list, the pick list will not be available, and any DateTime values will split into separate boxes.
If you are working with a time value, there will be increment and decrement arrows to the right of the time value. Use these to increment the selected hour , minute, or second up or down. If you are working with a date value, the down arrow will show the date picker, which is a small calendar from which you can choose the desired date.
Boolean Parameter Fields
You may find a Boolean parameter field helpful when using record selection based on a Boolean database field or when conditionally formatting according to a parameter field value. A Boolean parameter field, like a Boolean formula (discussed in Chapter 5), can contain one of only two values: true or false.
When you choose a value type of Boolean in the Create Parameter Field dialog box, the dialog box changes to accommodate the special features of a Boolean parameter field, as shown in Figure 14-3. Specify the Name of the field and the Prompting Text for the parameter field just as you would for other parameter fields. You can also choose a True or False default value, as well as specify descriptions for the values, by clicking the Set Default Values button.
The Options section is different for a Boolean parameter field. If you check the Place in Parameter Group check box, you will add this parameter field to a grouping of one or more other Boolean parameter fields. Boolean parameter field groups allow you to simulate the radio button behavior you see in Crystal Reports and other Windows applications. Since you can have more than one group of Boolean parameter fields, use the Parameter Group Number text box to specify a group number for this parameter field.
If you check Group Is Exclusive, only one parameter field in a group can be chosen at a time in the drop-down list. The chosen field will return true, and all others will return false. If you don t check Group Is Exclusive, you will be able to click the Add button to choose more than one member of the group. Each chosen parameter field will return true, while others not chosen will return false.