|Oracle® Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer
11g Release 1 (11.1.1)
Part Number B32519-01
Use this dialog to change advanced list of value (LOV) options for an item class. The settings made here affect how Discoverer Plus users select and display LOV options.
This dialog is also displayed as:
For more information, see:
Retrieve values in groups of
Use this spin box to specify the maximum number of rows to display to an end user (for each array fetch of data from the database), when a list of values is expanded in Discoverer Plus.
Discoverer end users see the results of each database array fetch as a block of data that they can scroll up and down. End users can also easily display the next block of data (for more information, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus).
This setting is useful in Discoverer where a LOV might contain many rows, as it removes the risk of having to wait to retrieve all the values from the database at once. Discoverer displays the LOV values in blocks of a specific number of rows.
Note: The value set here is used as a default when the Require user to always search for values check box is cleared (for more information, see the "Require user to always search for values" item below).
Note: In Discoverer Plus, the server always fetches from the database, the number of rows specified in the Retrieve values in groups of field, no matter how large that number is. However, the maximum number of values that can be fetched by the client from the server is 100. So if the Discoverer manager sets the fetch size to be 1000 (more than the 100 maximum above), in Discoverer Plus the rows are always fetched by the client in groups of 100. If the Discoverer manager sets the fetch size to be 50 (less than the 100 max), in Discoverer Plus the rows are always fetched in groups of 50.
Sort the values and remove duplicates
Select this check box to hide duplicate values and alphabetically sort the display order of the list of values (for an item class) in Discoverer.
Clear this check box to display duplicate values and display rows in the same order as the values stored in the database.
This option enables you to decide whether LOV statements should automatically add the DISTINCT and ORDER BY clauses. If you know that your data is already distinct and ordered (or if it doesn't matter if your data is returned this way) then you can choose to turn this setting off.
Note: The time taken to display large lists of values depends upon how long it takes to complete a search of the database tables. Clearing this check box can improve the time taken to display large lists of values.
Show values in "Select Items" page of Worksheet Wizard
Select this check box to display LOV items in the Select Items dialog of the Worksheet Wizard (the item navigator) in Discoverer Plus.
Clear this check box not to display LOV items in the Select Items dialog of the Worksheet Wizard (the item navigator) in Discoverer Plus.
Note: In the Item Navigator, the LOV does not use the array fetch size (as specified in the "Retrieve values in groups of" spin box). In this specific instance only, Discoverer will fetch all the values first before they are displayed. If you have millions of rows, this could potentially take a long time and you would want to have this option selected.
Require user to always search for values
Sometimes, for performance reasons you might want to force end users to query a subset of the LOV. To do this you use this check box.
Select this check box when you want Discoverer to display a Search Criteria field that forces end users to enter search criteria to reduce the list of values to a subset of the whole list.
Clear this check box when you want Discoverer to display the number of rows specified in the Retrieve values in groups of spin box (for more information, see "Retrieve values in groups of").
Cache list of values during each connection
Select this check box to cache the LOV (during the current session) after it has been displayed for the first time. Use a cache when your data is static during your session. This is the recommended setting.
Clear this check box to fetch LOVs from the database each time. This option is most useful when the data is extremely dynamic and you want end users to always see the very latest (up-to-the-second) list of values.