Filtering Data Using Parameters

Chapter 7 - Selectively Refining a Report Using Data Values
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Do you have a report that you need to run periodically with exactly the same data format except that each time you run it, you go into Design mode and change the query to use a different date range? This is a great example of when using a parameter field in a report makes sense. The idea behind a parameterized report is that you can write a general report, prompt the user to provide a value, and pass that value into the report where it is used as a variable in the request for data, filtering out data that doesn’t meet the criteria. This makes the report very adaptable since the user is in control of the input used to retrieve the data.

Earlier we discussed using the Select Expert to create the WHERE clause for a SQL statement, and we presented the following SQL statement as a way to limit the data in the report to those resorts located in Canada or the United States:

SELECT Resorts.ResortCode,Resorts.StateProvince, Owners.PurchasePrice,Resorts.Country, Owners.PurchaseDat FROM Resorts INNER JOIN Owners ON Resorts.ResortCode=Owners.ResortCode WHERE     (Resorts.Country="CN" or Resorts.Country="US") 

While this is a great approach to refining the data, it is entirely dependent on the fact that the report designer knows which countries to include in the report when the report is being built rather than when the report is being run. The report would be more flexible for the user, and ultimately require less maintenance for the programmer, if the report user could decide and vary which countries to include at runtime rather than design time. The idea is to let the user choose a value for the country in an ad hoc and on-the-fly manner. Parameter fields can help you accomplish this task.

Creating a Parameter Field

Parameter fields can be created and used directly from the Field Explorer, as shown in Figure 7.8. With the Parameter Fields category selected, you can use the New button or right-click and select New from the menu in order to create the parameter field. If there are existing parameter fields, they appear indented under the category like Purchase Date does, and from here, you can edit the parameter by clicking the Edit button beneath the Field Explorer’s title bar or right-clicking the parameter itself and selecting New from the submenu that displays.

Figure 7.8. Parameter fields in the Field Explorer

Prompting the User

Any time you use a parameter field, you are doing so with the goal of asking the user to provide information. That means that a dialog will pop up for the person using the report and, with the question you provide, ask them to type or choose a value for a field in the report. The question should be meaningful and concise. To create a parameter field in Design mode, three key pieces of information are required:

1. A name for the parameter field

2. A question or prompting text the user will see and need to answer

3. The data type allowed for the expected answer

Let’s create a parameter that will ask the user for the country to be used in the query above instead of hard-coding values in the Select Expert. This will let the user decide at runtime which countries to use. Figure 7.9 shows that the initial information of name, prompting text, and value type is added to top portion of the Edit Parameter Field dialog. Valid data types are Boolean, Currency, Date, DateTime, Number, String, and Time.

Figure 7.9. A new parameter field

The bottom half of the Edit Parameter Field dialog asks for additional details needed for the new field. When the user is asked to provide a data value, they may want to specify one value or multiple values. For instance, you can have the user provide one country or multiple countries for the parameter. To let the user provide more than one value as an answer to a question, enable the Allow Multiple Values check box option. The set of three radio buttons for Discrete Values, Range Values, or Discrete And Range Values tells Crystal whether the user will be choosing or typing a single value (which is known as a discrete value) or providing a starting range and ending range for data values. The option to do both provides ultimate flexibility to the user, letting them choose to add a single value at a time as well as ranges of data and to intermix the two ways of choosing values.

You can provide a list of values for the user to choose from; this is known as a default value list in Crystal. If a list is provided for a user and the value desired doesn’t appear in the list, the Allow Editing Of Default Values When There Is More Than One Value option (which appears below the Set Default Values button) can be used. This lets the user add additional values for the report being run but does not permanently add values to the set of value choices.

Creating a List of Valid Values

It’s possible to generate a default list of choices for a parameter that is to be used interactively. This gives the person using the report a bit of a head start on choosing valid values. You can create the list of valid values in three ways:

• Manually by typing individual values

• Using a field from a database table

• Importing a pick list from an external file

When you click the Set Default Values button, the dialog shown in Figure 7.10 or one similar to it will appear. The options available on the Set Default Values dialog are dependent on the value type of the parameter. To manually add a valid value, type it into the Select Or Enter Value To Add box and then use the right arrow button to move the value to the Default Values list. This is useful for adding values like None or Type something here. In Figure 7.10, we typed the word None and we can move it to the list of default values by clicking the button with the > symbol on it.

Figure 7.10. Setting default values for a parameter

To present a list based on values stored in a database column, use the Browse Table and Browse Field drop-down boxes at the top-left corner of the dialog. These drop-down boxes let you choose the table and field to use to populate the list of values available on the left side of the screen; then you can add valid values to the list on the right by using the arrow buttons between the two lists. It is the list on the right that is seen by the person using the report.

If you already have the values stored externally in a text file, you can use the Import Pick List button to retrieve the values from the file directly into the Default Values list on the right side of the dialog. You can also export the values of a Crystal Default Values list and store it in an external file using the Export Pick List button.

The Define Description button can be used to add descriptive text for the user to see that might better explain the value that they are choosing. Values in the list can be sorted using the Display, Order, and Order Based On drop-down boxes.

Warning 

The Default Values list is what the user will see when prompted for a parameter value. Crystal will not change or automatically update the list of values even if you chose the option to browse a table and a field. Adding values or deleting values is done manually in the Set Default Values dialog.

Allowing the User to Type Values Directly

A default list of values is definitely the best way to get input from a user, since there’s less chance of input error if all that has to happen is a click of the mouse on a valid value. Sometimes, though, you need to allow direct editing. To help prevent errors in this case, you can use a set of options at the bottom left of the Set Default Values dialog to do the following:

• Set a length limit on the typing

• Set a minimum length

• Set a maximum length

• Specify an edit mask for validity of input on string data

For edit masking, Crystal uses characters to represent valid values in specific positions. For instance, an edit mask of ###-###-#### would require that the user type three numbers then a dash then three numbers and a dash and finally four numbers, like this: 800-555-1212. Edit masking helps prevent bad data by checking the values for validity and commonsense values. Table 7.2 describes the edit mask characters.

Table 7.2: Edit Mask Characters

Character	Position Restriction
A	Alphanumeric character required.
a	Alphanumeric character allowed but not required.
0	Digit required.
9	Digit or a space allowed but not required.
#	Digit, space, plus/minus sign allowed but not required.
L	Alphabet character required.
?	Alphabet character allowed but not required.
&	Any valid character or space required.
C	Any valid character or space allowed but not required.
. (period)	Separator character.
, (comma)	Separator character.
: (colon)	Separator character.
; (semicolon)	Separator character.
- (dash)	Separator character.
/ (slash)	Separator character.
< (less than)	Converts subsequent characters to lowercase.
> (greater than)	Converts subsequent characters to uppercase.
\ (backslash)	Escape character used to allow A, a, 0, 9, #, L,?,&,C in the text being typed by the user; e.g., type the value as \A to allow an A in the data.

Data Type Variations

The options available on the Set Default Values dialog are dependent on the value type of the parameter. For example, string data allows edit masking while number fields do not. Boolean data (TRUE or FALSE values) presents a dialog that lets you group values into a dialog box from which you can choose one value (exclusive) or multiple values.

Using a Parameter Field

With a parameter field created, you can now put it to work in a report. Since parameter fields appear in the Field Explorer beneath the Parameter Fields category, you can treat the field like any other field, even dragging and dropping it into your report. Parameter fields can be used in any of the following ways:

• Select Expert to create a WHERE clause

• Formulas to suppress values or sections

• Highlighting Expert

• Sorting criteria

• Grouping criteria

• Directly on the report to print the parameter’s value

The Select Expert and Parameters

One of the main purposes of using a parameter field in a report is to ask the user a question so that data can be fetched based on the criteria they specify. To get a parameter field to ask for a value from the person using the report, you need to enter it in the Select Expert. This makes the parameter value a part of the WHERE clause to query the database. In the WHERE clause, a field value is compared to the value provided by the user. The example we’re building uses the Country field, and we want to ask the user to supply a value, so we’re building a WHERE clause whose structure resembles this:

WHERE Country = {value specified by the user}

In order to get the report to ask the user for a value, select the field you’re matching in Design mode of the report, and then open the Select Expert by choosing Report > Select Expert. The dialog in Figure 7.11 appears, and you can put the skills you learned earlier in the chapter on using the Select Expert to use here. In this dialog, one parameter has already been added to the report, {?Purchase Date}. To add the first or additional parameters, click either the <New> tab or the New button.

Figure 7.11. Adding a parameter to a report query

When you click the New button, the Choose Field dialog shown in Figure 7.12 opens. From the list of fields, choose a field to compare against in the WHERE clause. In this example, we’re going to compare against the Country field in the Resorts table.

Figure 7.12. Choosing a comparison field

After choosing a field, you must then choose the comparison operation. Comparison operations like less than, greater than, and equal to appear in the first drop-down box in the Select Expert. For a complete discussion of the options here, refer back to Table 7.1. Figure 7.13 shows the second tab added for Resorts.Country and the list of comparison operations available for the data type of the field.

Figure 7.13. Choosing a comparison operator

A second drop-down box will appear after choosing the comparison operator. In this second drop-down box, the parameter field shows up on the top of the list of available values. Parameter field names display with a question mark as the first character and then whatever you named the field. The list of values in the Select Expert sorts parameter fields to the top since the question mark sorts alphabetically before the letter A. Figure 7.14 shows the approach taken to choose a parameter field. Clicking the {?Country} item will add it to box above the list of values.

Figure 7.14. Choosing a comparison value

That’s all you need to do to get Crystal to ask the users for a value! When the report is opened or refreshed, you’ll first see a dialog asking whether you want to use the current parameter values or prompt for new values. If you prompt for new values, a dialog similar to that shown in Figure 7.15 displays.

Figure 7.15. Ask the user a question or two.

The Single-Screen Input Approach

Crystal uses a single-screen approach to asking for all parameter input. It uses this single screen regardless of how many parameters the report requires. The input screen has three distinct areas, starting at the top:

• List of parameters interactively requiring a value

• Prompting text asking a question for the selected parameter

• Value area to answer the question and provide the value

The example shown in Figure 7.15 asks for two parameter values: Purchase Date and Country. The parameters are listed in the order they were added to the Select Expert. In the top part of the screen, the user must select with the mouse the parameter for which they are providing input. With a parameter selected, the prompting text and value area change to be specific to the question. For the Purchase Date, a date range is required and drop-down boxes are provided for the starting and ending date and time values. After you select values for the Purchase Date parameter, it is very important that you not click the OK button yet. There is a second parameter for this report that needs a value before you click OK.

Warning 

If the user clicks OK before providing values for all parameters, the report will have unexpected results since the remaining parameters will not have valid values.

Once a value is provided for the Purchase Date parameter, the user selects the Country parameter in the list. Figure 7.16 shows this second input dialog. When it was created, the Country field used the Discrete Value and Range Value (refer back to Figure 7.9). This allows either single values or a range of values to be added as answers to the parameter prompt. It also makes the dialog a bit more complex!

Figure 7.16. Single and range value parameter

In the Discrete Value area of the screen, use the drop-down box to select a single value from the list. After selecting the value, click the Add button to its right to add the value to the list of values at the bottom of the screen. This list at the bottom of the screen is used in the WHERE clause of the query when the report runs.

In the Start Of Range and End Of Range area of the dialog, use the two drop-down boxes to pick a starting value and an ending value from the lists. After picking both values, click the Add button to add them to the list at the bottom of the screen. Optionally, you can use the check box to the right to include the value itself in the range; not checking the box excludes the value from the range. Below the specific range setting area are two checkboxes that can be used to avoid needing to provide specific ranges. Enabling the No lower Bound option effectively tells the query to retrieve all data earlier than the end of the date range. Likewise, enabling the No upper Bound option retrieves all data later than the starting date range. Only one of these options can be enabled at any one time.

When all parameters have valid values, it’s time to click the OK button to create the query that is sent to the database, and the report is then displayed.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback