A Comprehensive Website for Oracle Learning

A �List of Values�, can be used as a validation mechanism for values defined in user parameters. Your LOV or �List of Values� can consist either of static values or items queried from the database using select statements.

Hands-on

Since your parameter report is very handy for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol.

Also add more parameter to pass such as the destination of printer, and so on.

See Figure 11.

Your tasks are:

1- Modify the �Customers List Parameter� Form.

2- Add LOV to the �symbol� item.

3- Add one more parameter �Destination Type� to the �customers List Parameter� Form.

4- Run the report.

5- Test the report.

You will learn how to: use parameter, modify parameter properties using property palette, use query builder, and add more parameters to existing parameter form.

Figure 11

List of Values

Open the Report Builder tool.

Open a Module

In the �Object Navigator� window, go to the Main menu and choose �File,� select �Open� to open an existing report (REPT09) from your folder

Save a Module

Save it as report number 11 (REPT11). You do this to leave the REPT09 module untouched.

(File > Save as)

In the Object Navigator, on the vertical toolbar, double click on the "Layout Model" icon."

Open Parameter Form Builder window

In the �Report Editor - Layout Model� window, click on the "Parameter Form" icon which is on the horizontal toolbar.

The Report Editor - Parameter Form window, will be opened. In the Parameter Form window, click on the "Parameter Form Builder" icon which is on the horizontal toolbar.

Add objects to Parameter Form

The Parameter Form Builder window will be displayed. In the �Parameter Form Builder� window, change the Title item to �Customer�s Symbol List,� click on the DESTYPE (Destination Type) parameter to select it, notice that the �Symbol� parameter has already been selected, and then click "OK."

Now in your �Report Editor - Parameter Form,� you should see only two parameters such as Destination type and Symbol (PF_DESTYPE and PF_SYMBOL).

Change a Fields� properties

Right click on the PF_SYMBOL field and open its property palette. In its parameter palette, type �ORCL� in the �Initial Value� box, and double click on the "List of Values" box.

In the �Parameter List of Value� window, checkmark the �Select Statement� radio button and type the following SQL statement into the �SQL Query Statement� box.

SELECT DISTINCT symbol

FROM stocks

Click "OK," close the property window, and then close �Parameter Form Builder.�

Run a report

In the Object Navigator, highlight the report and click on the Run icon on the toolbar to run the report.

A parameter window will be displayed. Now, you can generate your report on a different destination type such as Screen, File, Printer, Mail, and Preview.

Select your prefer destination type, your company�s symbol, and click on the Run icon to run the report

Repeat this process for different companies.

At the end, highlight the report and save the changes (REPT11).

Questions:

Q: Describe a List of Values in the report module?

Q: How do you create a user parameter item?

Q: How do you define a system parameter item?

Q: How do you modify a user parameter properties using property palette?

Q: How do you use the Query Builder tool?

Q: Since your parameter report is very handy for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol.