Wednesday, July 25, 2007

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


Justin said...

Thats very nice info abt using 'All' in the parameter form.

I have a question here in Disco 10g ver

Can we have a default parameter which can change dynamically.

For eg., if i am adding a new parameter - "Set of Books". The default value should be the Set of Books ID defined in the Oracle Apps profile options at responsibility level. So whenever i switch to some other responsibility and run this report using Disco Viewer, this default parameter should display me the Set of Books ID from the Profile.

Please explain if this is possible in discoverer 10g.

Michael said...

Hi Justin
This cannot be done out. The default value of a parameter is set by the owner of the workbook.

You can get the values used in a parameter to change by using a VPD (Virtual Private Database) where a user's Set of Books could be looked up during the login and stored as an in-memory variable. This variable can then be read by Discoverer and, of course, the database.

Best wishes