Oracle Discoverer – creating non-mandatory parameters with lists of values (LOV’s)

1. Firstly create a Custom Dicoverer Folder to return the list of values, as follows. In Discoverer Administrator, Right-click on the Business Area containing your reports-related Discoverer objects (Folders, etc.), and choose New Custom Folder…

2. Paste in the SQL statement which will reurn the required list of values. For example, and General Ledger accounting segment value:

SELECT 'All'                      DEPARTMENT
FROM   dual
SELECT ffv.flex_value             DEPARTMENT
FROM   apps.fnd_flex_value_sets   ffvs
,      apps.fnd_flex_values       ffv
WHERE  ffv.flex_value_set_id = ffvs.flex_value_set_id
AND    ffvs.flex_value_set_name = 'DEPARTMENT'
AND    ffv.enabled_flag = 'Y'
AND    ffv.summary_flag = 'N'

Click on Validate SQL to ensure that the syntax is correct, and that the database object names are valid.

3. Right-click on the new folder, choose Properties, and and assign useful values for the Name, Description and Identifier fields; say GL_DEPARTMENT_LOV.

4. Expand the folder to show the Department Item, right-click on the item, and choose New Item Class. Click Next > three times, accepting the default attribute values, then finally giving the new item class a meaningful name: say GL_DEPARTMENT_LOV. The Item Class should now be visible in the Item classes tab in Discoverer Administrator.

5. Return to the Data tab, and expand the folder on which the actual Discoverer report is based. Find the item for which this list of values is being built, right-click on it, and click Properties. In the Item class attribute, choose your new item class from the dropdown, and click Apply.

6. In Disoverer Desktop, re-connect to the database, to reflect the changes just made to the EUL, and open the Disoverer Workbook.

7. Go to Tools > Parameters > New, to create a new parameter (based on the Department field, in this example). Choosing the Department field in the For Item field means that the resulting parameter will automatically use the ist of values created above.

8. In order to provide an “All” option for the list of values (ie. to make the parameter effectively non-mandatory), you must ensure that the list-of-values SQL query contains a UNION (see above example).

9. Then, Discoverer Desktop, go to Tools > Conditions, highlight the condition created for the new Parameter, and click Edit.

10. Click Advanced >>, and then Add. Using the OR grouping, create a new condition :GL_DEPARTMENT = ‘All’. Click OK to save.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s