Tutorial 4-8. Adding “ALL” to the List of Values
When using the Select Expert for filtering report records, it forces the user to either enter a single value to filter on or pick from a list of values. The problem with this is that sometimes the user will want to print every record in the data source but they can’t because the selection formula doesn’t account for this. You need some way to let the user specify that they want to print all the records when the need arises.
You can use Command objects with DCPs to create a list of values that has the word “All” at the beginning of each list. This lets the user choose the “All” value when they want to print every record or they can pick any other value to filter the list by that value. The Crystal Reports sample report, “Prompting.rpt” shows us exactly how this is done. Let’s examine it to see the details.
Open the report “Prompting.rpt” found in the Crystal Reports Samples folder. After it opens, click on the Refresh Report button and select the option to prompt for parameters. You’ll see that each prompt has the value “All” as the first item in the list. As you select different values for each prompt, you’ll see that each successive prompt still lists “All” as the first item in the list of values.
Let’s look behind the scenes to see how the report was built. First click on Database > Database Expert to view the data source. Find the Command object GeographyPrompt and right-click it to select View Command. It uses the following SQL statement:
The first SELECT statement in the SQL statement selects a complete list of every value for Country, Region and City. The second SELECT statement selects a complete list of every value for Country and Region but for the City, it substitutes the word “All” in each record. This makes it possible for the user to see the word “All” in the list of values no matter which combination of Country and Region was selected. The third SQL statement does the same for every possible Country value. The last SQL statement uses “All” for each field so that the user can pick “All” for every prompt in the DCP.
It’s important to use the UNION keyword between each SQL statement. This keyword lets you combine multiple SELECT statements into a single resultset. Crystal Reports thinks it is reading from a single table when in reality it is multiple tables combined into one.
Notice that each “All” value is preface with “…” Since the list of values is sorted alphabetically, you don’t know where the All option will appear in the list. By prefacing it with “…”, you insure that it always appears as the first item on the list.
After creating the Command object that builds the list of values for each prompt, you need to create a record selection formula that selects every record when the user selects the “All” value. Click on the menu items Report > Selection Formulas > Record. This opens the Formula Workshop dialog box. The selection formula is as follows:
This selection formula looks at each parameter value and determines if it is equal to “All”. Depending upon which parameter this applies to, it sets the other fields to match their respective parameter field. It does this in a tree-like manner where it starts at the least restrictive level and works its way down through the more restrictive levels. This results in the report only filtering on the fields that do not have the value “All” selected for them.
