Tutorial 4-3. Creating Parameters

Let’s do a tutorial illustrating what we’ve learned so far about creating parameters. We’ll create a parameter that lets the user specify which country to print. We’ll populate the list by importing all the records from the database. Secondly, we want to practice creating a record selection formula that is more complicated than what can be created using the Select Expert. We will give the user the option of printing just the specified country or printing all countries by selecting “ALL” from the drop-down list.

The sample report we are going to start with is the Group.rpt file that was used in the earlier tutorials. It shows last year’s sales by Country and Customer Name. Tutorial 4-1 was similar except that it hard-coded the country names into the selection formula. This tutorial improves upon that idea by letting the user select the country from a drop-down list.

Lastly, we are going to use the Select Expert to create a record selection formula that uses these two parameters to perform the filtering of the data. We are going to modify it so that the user can enter only one of the two parameters and the report will filter on just the field they want.

1. Open the Crystal Reports sample report Group.rpt. This report shows the last year’s sales for each customer and it is grouped by the Country field.
2. Save it as Basic Parameter Tutorial.rpt.
3. Right-click on the Parameter Fields list in the Field Explorer and select New. This opens the Create New Parameter dialog box.
4. Enter the parameter name “Selected Country” and leave it as a String data type.

We want to give users the ability to either select a specific country to print or print all countries. The standard way of doing this is to make the word “ALL” the first entry in the list of values and after that show the full list of countries.

5. In the Value list box, click on the first row and enter ALL.
6. The database has a huge number of countries to choose from and we aren’t about to type them all in manually. It’s much better to import them directly from the database. Click on the Value Field dropdown list and select the Country field.
7. Click the Actions button and select Append All Database Values. This imports every value from the database and automatically populates the list of values.
8. Click the OK button to save the parameter.
9. Now that the parameter has been created, it’s time to build the selection formula. Click on the menu items Report > Select Expert.
10. Select the field Customer.Country and click OK.
11. Select the comparison Is Equal To and select the parameter {?Selected Country}.

If we kept the selection formula as it is, then the user would only be able to select a single country. The selection formula doesn’t have the functionality to let the user print all countries. We need to modify it so that the user can select “ALL” from the list of values and print all the countries.

12. Click on the Show Formula button and look at the current selection formula prior to our changes.

13. Change it to the following:

This formula checks to see if the user selected “ALL” and if so it returns true. If the user didn’t select “ALL” then the second part of the formula checks whether the current record is the country that was selected. If so, then the record is printed. Otherwise it is skipped.

14. Click the OK button to save the selection formula.
15. Preview the report and play around with it for a minute. You’ll see that you can filter just by Country or view all countries in the database.