Creating and Implementing Parameters Fields

The process of using parameter fields in reports includes two distinct steps:

1. Creation of the parameter field.
    
2. Implementation of the parameter field into the report.
    

The remainder of this chapter uses the example mentioned earlier, the World Sales Report, to create and implement parameter fields into a report. The World Sales Report is one of the many sample reports that are provided by the Crystal Reports installation.

Reviewing Parameter Field Properties

Before you learn how to create and implement parameter fields, it is useful to understand a few common properties associated with creating parameter fields. Each of the following properties is presented within the Create/Edit Parameter Field dialog, shown in Figure 5.2:

• Name A logical name for the parameter field.
• Prompting Text A statement or question that is presented to the business user within the report prompt dialog for the parameter field.
• Value Type A list of available field types that correspond to how you want to use the parameter field within the report, including String (the default option), Boolean, Currency, Date, Date Time, Number, and Time.
• Allow Multiple Values Enables the business user to enter more than a single value for the parameter field.
• Discrete Values Enables the business user to enter only a single value for the parameter field.
• Range Values Enables the business user to specify a range, using start and end values, for the parameter field.
• Discrete and Range Values Enables the business user to enter specific single values as well as a range, using start and end values, for the parameter field.
• Allow Editing of Default Values Enables the business user of a report to edit any default values provided in the report parameter dialog or add their own manually.
• Default Values A dialog that enables the report designer to specify default parameter values based on either a database field, external pick list, or manual entries.

Figure 5.2. The primary parameter field properties are presented within the Create/Edit Parameter Field dialog.

Now that you have been exposed to the primary parameter field properties, you will use these items while creating parameters for a World Sales Report, as referenced earlier in the chapter.

Creating Parameter Fields

The first step in using parameters within a report is to create the actual parameter field and define the primary properties associated with it. In the following exercise, use the Field Explorer dialog to create two new parameter fields for the World Sales Report:

• A manual text entry field to use as the report's title
• A database field that prompts the business user to select one or more countries and use this selection to filter the data returned for the report

To begin your exercise, open the World Sales Report within the Crystal Reports designer. This sample report should be installed in the following directory, unless you have chosen an alternative location for the sample reports during the Crystal Reports 10 installation process:

C:Program FilesCrystal DecisionsCrystal Reports 10SamplesEnReportsGeneral Business

After you have opened this sample report, you can begin the steps necessary to create the parameter field objects in the following way:

1. Remove the existing report title text object. After you have opened the World Sales Report, navigate to the Design tab view, highlight and delete the text object currently used as the report's title that reads World Sales Report, located in the Report Header A section. Use your parameter field to populate the report title.
    
2. Remove the current Top N sort order because it is not needed for your exercises. From the Report menu, select Group Sort Expert, and within the presented dialog modify the For This Group Sort: drop-down setting to display All as shown in Figure 5.3. Click OK to continue.
    

   Figure 5.3. Use the Group Sort Expert dialog to remove the Top N sort order from the report.

   

    
3. Open the Field Explorer dialog by either clicking the appropriate toolbar button or using the View menu.
    
4. Open the Create Parameter Field dialog. To do this, right-click on Parameter Fields within the Field Explorer and either select New from the pop-up menu or click on the New button at the top of the Field Explorer.
    

   TIP

   In addition to using the right-click menu to create a new parameter field, you can use the Field Explorer's toolbar commands to create, edit, rename, and delete parameter fields. The operations available on this toolbar depend on what you have selected in the Field Explorer dialog.

   You first create a manual text-entry parameter field to enable the business user to define a title to display on the report.
    
5. Define the key properties for the parameter object. Within the Create Parameter Field, enter Title in the Name property, and provide a meaningful prompting text so that the business user understands how the entered value is used, such as, Enter a title to be used for this report.
    
6. Select Discrete Values under the Options area and click OK to return to the Field Explorer.
    
7. You should now see the Title parameter field listed under Parameter Fields in the Field Explorer, as shown in Figure 5.4.
    

   Figure 5.4. The Field Explorer is used to access, edit, and create parameter fields.

   
   You now create a second parameter field to use later in the chapter when discussing how to use parameter fields in coordination with record selections. In this way, you can filter the report data according to the selected parameter values.
    
8. Open the Create Parameter Field dialog. To do this, highlight the Parameter Fields item and click the New toolbar button inside the Field Explorer dialog.
    
9. Define the key properties for the parameter object. Within the Create Parameter Field dialog, enter Countries in the Name property and provide a meaningful prompting text so that the business user understands how the entered value will be used, such as, Please select one or more countries for this report.
    
10. Select both the Allow Multiple Values and Discrete Value(s) items under the Options area, as shown in Figure 5.5.
     

    Figure 5.5. Use the Create Parameter Field dialog to create and edit parameter fields.

    

     

Setting Default Values for Parameter Fields

You now want to define the Countries parameter field to include all database values within the Country field of the Customer table. Map the parameter field to this database field and quickly import these values, enabling the business user of the report to select one or more country values from the available list.

When setting default parameter values, a list of default values can be read from the database or entered manually to provide the business user with a list of available values from which to choose. The Crystal Reports application enables you to define the default values list only when you are designing reports, and no direct database connection exists to populate the prompting parameter field list when the business users run the report. The following is an overview of various options available within the Set Default Values dialog to make data entry easier for the business users:

• Browse Table The database table that contains the default values for the parameter field.
• Browse Field The database field that contains the default values for the parameter field.
• Select or Enter Value Used to enter values in the Default Values list. You can type new values in the entry box and then click the Add button (>), or select a value from the list and then click the Add button (>). This list is automatically populated when a field is selected through the Browse Field drop-down box.
• Add, Add All Used to move one (>) or all (>>) values from the Select or Enter Value to Add list (on the left) to the Default Values list (on the right).
• Remove, Remove All Used to move one (<) or all (<<) values from the Default Values list (on the right) back to the Select or Enter Value list (on the left).
• Import/Export Used to import or export a text file containing a pick list of parameter values to be used as the default values. (Note: These two buttons are not available when creating or editing parameters in the OLAP Report Creation Wizard.)
• Default Values and Description The list of values displayed when business users are prompted to populate the parameter field, and a description for each value (optional). To set the optional description for each default value, use the Set Description button and corresponding dialog, located just below the Default Values list. The value list can be populated from any database table included in the report using the Browse Table and Field drop-down boxes. The description can be used to create a more user-friendly label for the business users. For example, the Country field could have been stored as a number in the database, but it would be more intuitive for the user to select it by name.
• Display Controls whether the prompt to business user displays the Value and Description, or just the Description, for each default value. In either case, only the Value is used within any database interaction, such as with record selection definitions.
• Order The order that the default values are displayed in the prompt to the business users of the report.
• Order Based On The order of the default values can be based on either the Value or Description property of these values.
• Length Limit The minimum and maximum length limits for the parameter field.
• Edit Mask Used to enter an Edit Mask for string data types rather than specifying a range of values. The Edit Mask can be any of a set of masking characters used to restrict the values you can enter as parameter values. (The Edit Mask also limits the values you can enter as default prompting values.) Table 5.1 provides a listing of the masking characters and instructions on how to use them.

  Table 5.1. Edit Mask Characters

  Mask Character	Mask Description
  A	Requires entry of an alphanumeric character for its place in the parameter value.
  a	Enables an alphanumeric character but does not require the entry of a character for its place in the parameter value.
  0	Requires a digit (0 to 9) for its place in the parameter value.
  9	Enables a digit or a space but does not require such an entry for its place in the parameter value.
  #	Enables a digit, space, or plus/minus sign, but does not require such an entry for its place in the parameter value.
  L	Requires a letter (A to Z) for its place in the parameter value.
  ?	Enables a letter but does not require such an entry for its place in the parameter value.
  &	Requires a character or space for its place in the parameter value.
  C	Enables any character or space but does not require such an entry for its place in the parameter value.
  ., : ; - / (separator characters)	Inserting separator characters into an Edit Mask is akin to hard-coding the formatting for the parameter field. When the field is placed on the report, the separator character appears in the field object frame, like this: L0L-0L0. This example depicts an edit mask for a Canadian Postal Code (such as M2M-2L5) with a forced display dash.
  < or >	Forces subsequent characters in the parameter to be converted to lowercase (<) or uppercase (>).
  	Forces the subsequent character to be displayed as a literal.
  Password	Enables the setting of the Edit Mask to "Password," so that subsequent conditional formulas can specify that certain sections of the report become visible only when certain user passwords are entered.

You now define the default values for your parameter fields (if necessary, refer to the list of options within the Set Default Values dialog while completing these steps):

1. Access the Create/Edit Parameter dialog if you have closed it by highlighting the Countries parameter and clicking on the Edit toolbar button. Open the Set Default Values dialog by clicking on the Set Default Values button, as shown in Figure 5.6.
    

   Figure 5.6. The Set Default Values dialog enables you to define the default values for parameter fields.

    
2. Choose the database table and field from which to set the defaults. Under the Select from Database area of the Set Default Values dialog, select Customer from the Browse Table list, and then select Country from the Browse Field list.
    
3. Add the actual database values to the default value list. With all the country values listed under the Select or Enter Value to Add area on the left, use the Add All (>>) button to move all these values to the Default Values list on the right, as shown in Figure 5.6.
   
   Continuing the steps started above, now add descriptions to the default values that you have added to the parameter field.
    
4. Locate and highlight the USA value in the Default Values list. Click on the Define Description button located just below the Default Values list to present the Define Description dialog.
    
5. Add United States of America as the description for USA, as shown in Figure 5.7, and click OK to close the Define Description dialog. If you want, repeat this step for any additional default values.
    

   Figure 5.7. The Define Description dialog enables you to create user-friendly descriptions for parameter selections that can be presented to users instead of potentially cryptic database values.

   

   
   To make the default values friendlier to the business users of the report, you can sort the contents of the display prompt lists based on the Order and Order Based On option settings. Seven distinct options are available to sort the parameter field values that are reflective of the parameter value type, including both ascending and descending for the three data typesalphanumeric, numeric, and date/timeas well as No Sort. Continuing the Parameter Creation example, follow these steps:
    
6. Sort the Country parameter field in alphabetic order. Select Alphabetical Ascending from the Order drop-down list, and choose Value from the Order Based On list, as shown in Figure 5.6.
    

   TIP

   If you select a sort type from the Order list that does not match the parameter field value type, no sorting is applied.

7. For your purposes here, you can leave the remaining options within the Default Values dialog to their default state and click OK to return to the Create Parameter Field dialog. If desired, peruse the overview of default setting options and use these to modify the desired options for your parameter field.
    
8. Click OK to return to the Field Explorer. You should now see the Countries parameter field listed under Parameter Fields in the Field Explorer.
    

   TIP

   There are a few considerations to keep in mind when working with parameter fields, such as

   • Any parameter field prompting text more than one line in length will automatically word wrap.
   • The creation of a pick list enables the business user to select parameter field values from drop-down boxes instead of needing to enter them manually.
   • A parameter field does not have to be placed in a report to be used in a record or group selection formula. You can create the parameter field and then enter it in your formula as you would any other field.

Implementing Parameter Fields

You have now completed the first task necessary to use parameter fields within a reportcreating the actual parameter field objects. This section, and the exercises included here, discuss how to apply these parameter fields and make use of them to provide the business user of the report with a more dynamic and interactive reporting experience.

First, implement the parameter field created earlier, called Title, to serve as the title to the report. This example demonstrates how a manual text entry field can be used to add useful commentary or descriptive information to a report. Continue working with the same report, the World Sales Report and follow these steps:

1. Add and position the Title parameter object onto the report. Open the Field Explorer dialog and expand the Parameter Fields list. Click on the Title parameter field, drag it onto the report, and drop it into the upper-left corner of the Report Header A section, shown in Figure 5.8 in a size 20 Arial font.
    

   Figure 5.8. Drag and drop the Title parameter field into the upper-left corner of the Report Header A section.

    
2. Preview the report. To view how this parameter is now used within the generation of the report, run the report by clicking on the Refresh toolbar button (represented by the lightning bolt icon). As shown in Figure 5.9, the report now prompts the business user to enter a value that will be used as the report's title.
    

   Figure 5.9. Parameter fields offer a means to add additional interactivity for the business users within the report.

   

    

NOTE

If you have already run the report at least once and then select to refresh the report, you will also see the Refresh Report Data dialog that asks you to select from the following two options:

• Use Current Parameter Values
• Prompt for New Parameter Values

To enter or select new values for any existing parameter fields, you need to select the second optionPrompt for New Parameter Values.