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

We will use the example mentioned earlier, the World Sales Report, throughout the remainder of this hour 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 9 installation.

Reviewing Parameter Field Properties

Before we discuss 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 CreateParameter Field dialog, as shown in Figure 12.2:

• Name A logical name for the parameter field.

• Prompting Text A statement or question that will be 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 Allows the business user of a report to enter more than a single value for the parameter field.

• Discrete Values Allows the business user of a report to enter only a single value for the parameter field.

• Range Values Allows the business user of a report to specify a range, using start and end values, for the parameter field.

• Discrete and Range Values Allows the business user of a report to enter specific single values as well as a range, using start and end values, for the parameter field.

• Allow Editing of Default Values Allows the business user of a report to edit any default values provided in the report parameter dialog.

• Set Default Values A dialog that allows the report designer to specify default parameters values based on either a database field, external pick list, or manual entries.

Figure 12.2. The primary parameter field properties are presented within the Create Parameter Field dialog.

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

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, we will 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 our 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 9 installation process:

 C:\Program Files\Crystal Decisions\Crystal Reports 9\Samples\En\Reports\General Business 

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

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. We will be using our parameter field to populate the report title.

2. Remove the current Top N sort order because it is not needed for our 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 12.3. Click OK to continue.

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

   

3. Open the Field Explorer dialog via either 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 select New from the pop-up menu.

In addition to using the right-click menu to create a new parameter field, you can also 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.

We will first create a manual text-entry parameter field to allow 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 will understand how the entered value will be 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 12.4.

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

   

We will now create a second parameter field to use later in the hour when discussing how to user 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 Country in the Name property and provide a meaningful prompting text so that the business user will understand 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 12.5.

    Figure 12.5. The Create Parameter Field dialog is used to create and edit parameter fields.

    

Setting Default Values for Parameter Fields

We now want to define the Country parameter field to include all database values within the Country field of the Customer table. We will map the parameter field to this database field and quickly import these values, allowing 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 to choose from. The Crystal Reports application allows 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 to Add Used to enter values in the Default Values list. You can type new values in the entry box and then press the Add button (>), or select a value from the list and then press the Add button (>).

• 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).

• Delete, Delete All Used to move one (<) or all (<<) values from the Default Values list (on the right) back to the Select or Enter Value to Add list (on the left).

• Import/Export Pick List Used to import and/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). The optional description for each default value is set by using the Define Description button, and corresponding dialog, located just below the Default Values list. The value is used in the DB and what is sent back to it. The description can be used to create a more user-friendly label for the business users. For example, the country field can be stored as a number in the database, but it is more intuitive for the user to select it by name.

• Display Controls whether the prompt to business user displays either the Value and Description, or just the Description for each default value. In either case, only the Value will be 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.) Refer to the Crystal Reports Help files for a complete list of masking characters and detailed instructions on how to use them.

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

1. Open the Set Default Values dialog by clicking on the Set Default Values button, as shown in Figure 12.6.

   Figure 12.6. The Set Default Values dialog allows 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 12.6.

We will now add descriptions to the default values that we 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 12.7, and click OK to close the Define Description dialog. If desired, repeat this step for any additional default values.

   Figure 12.7. The Define Description dialog.

   

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 types alphanumeric, numeric, and date/time as well as No Sort.

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 12.6.

   

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

   
   

7. For our purposes here, you can leave the remaining options within the Set 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.

9. You should now see the Country parameter field listed under Parameter Fields in the Field Explorer.

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.

• You can create a pick list for the business user to choose the parameter field value rather than having them enter it manually.

• A parameter field does not have to be placed in a report in order 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 report creating the actual parameter field objects. This section, and the exercises included here, will discuss how to apply these parameter fields and make use of them to provide the business user of the report with more dynamic and interactive reporting experience.

First, we will 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. We will continue working with the same report, the World Sales Report.

1. 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 12.8 in a size 20 Arial font.

   Figure 12.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 lightening bolt icon). As shown in Figure 12.9, the report will now prompt the business user to enter a value that will be used as the report's title.

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

   

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 will need to select the second option Prompt for New Parameter Values.