Using ALL in a parameter

From time to time, I get asked and I see questions in the OTN forum regarding how to create a condition / parameter that will allow a user to select all items from a list of values without actually checking off every item in the list.

Here's an example drawn from my own database.

As you can see I am starting out with a Crosstab workbook that displays the Cost and Selling Price for my Products by Channel and Product Line, with the Product Line being a page item.

Next, I started out by creating a new parametized condition. As usual, from the Values drop-down list I selected New Parameter. Below is my completed New Parameter dialog box. Of note you should see that I have told the user that they can enter ALL for all values, and I have even set the default to be ALL. Even though that value does not appear in my list of values, it doesn't matter. Discoverer will allow me to continue.

After clicking the OK button to close the New Parameter dialog box, back in the New Condition box I clicked the Advanced button to the side of the condition and from the enhanced list I selected OR so that I can add some Boolean logic to my condition.

The first thing I did was to click the drop-down alongside the Item. From the list I then selected New Calculation. In the calculation I typed in the following: UPPER(:ProdLine). I did this so that my users do not have to type ALL (in uppercase) but rather they can type all if they wish, or even All.

Finally, as you can see below, I completed my condition my typing in ALL into the Values area.

My final condition is therefore this:

Line Name = :ProdLine
OR
=UPPER(:ProdLine) = 'ALL'

Notice how Discoverer added the = character in front of my calculation. This is Discoverer's way of letting you know that what follows is to interpreted as a calculation and not a literal value.

That's all there is to it. With the condition finished, whenever my users are prompted to select one or more product lines, they can type in ALL and Discoverer will return all values.