List of Values ( LOVs )

Using a List of Values

A list of values is a list that contains the data values associated with an object. A list of values can contain data from two types of data source:

1) Database file: When you create an object, Designer automatically associates a list of values with the object. The list of values is not created until a user, or you the designer, choose to display
a list of values for the object in the Query pane. A SELECT DISTINCT query is then run against the column or columns inferred by the object. The returned data is stored in a file with a.LOV extension in the universe sub folder created under the same folder that stores the universe file.The.LOV file is then used as the source for values for the list.

2) External file: Personal data, for example a text file, or an Excel file can be associated with a list of values. A list of values that is based on an external file is fixed. You cannot have a dynamic link with an external file. You must refresh the.LOV file if your external file has changed.

How is a list of values used in Web Intelligence?

In Web Intelligence, a user can create a query in the Query pane using the operand “Show list of values” to apply to an object when applying a condition.

Note: A.LOV file is also created whenever any condition is applied to an object in the Query pane that requires a restriction on the column values inferred by the object.

A.LOV file is also created whenever any condition is applied to an object in the Query pane that requires a restriction on the column values inferred by the object.

The List of Values for an object appears showing values available for the object, allowing the user to choose the terms for the condition. The first time a list of values is used, it is saved as a.LOV file in the universe sub folder on the file system. This allows the SELECT DISTINCT query to be run only once for an object. This folder also stores the.LOV files created in Designer which are used to restrict the list of values returned for objects for which the designer wants to control access to the data.

How List of values are used with an object

When you create a dimension or detail object in Designer, it is automatically assigned an associated list of values. This list does not physically exist when you create an object, but by default, the object has the ability to query the database to return a list of its values when used in the Query pane.

Note: No default list of values is assigned to measure objects. When a condition is first placed on an object in the Query pane that requires a list of values to be displayed in Designer, a SELECT DISTINCT statement is run against the appropriate columns inferred by the object, and the list of values is returned.

A.LOV file is automatically created in the universe subfolder to hold the list values. The next time that the list of values is required for the object in Designer, the values are returned from the.LOV file and not from the database.

The designer’s role in controlling lists of values

As the universe designer, you can define how the data is presented in the list, and define restrictions on the amount and type of data returned to the list. You can set the properties for an object to determine the following actions for a list of values:
• If a list of values is associated with an object.
• When the list is refreshed.
• Define a query that sets conditions on the SELECT DISTINCT query that an object uses to return a list of values. You save this query in the properties of an object.
• Display list values either as a simple list, or as an object hierarchy.
• If the list is based on column values, or values from an external file, for example an Excel spreadsheet.
You can also create a permanent list for values for an object and export this list to the repository. This.LOV file is then always used as the list of values for that object. It is not updated.

List of values properties and options

Associate a List of Values:
• When selected, allows a list of values to be associated with the object. It is selected by default.
• When cleared, no list of values is associated with the object.
• Selected by default for dimensions and details. Not selected for measures.

List name: Name of the.LOV file that stores the returned list data. Limited to 8 characters.

Allow users to edit this List of Values:
• When selected, users can edit the list of values file in Web Intelligence.
• When cleared, the user cannot edit the list.

Note: This does not apply to personal data files such as Excel spreadsheets. These are not exported to the repository. They remain on a local machine. A user can edit a local file, or change the target list of values for another local data file.

The purpose of a list of values is usually to limit the set of available values to a user. If they can edit a list, you no longer have control over the values they choose. Normally, if you are not using a personal data file as a list of values source, you clear this option to ensure that users do not
edit lists of values.

Automatic refresh before use:
• When selected, the list data is refreshed each time the list of values for an object is displayed in the Query pane. This can have an effect on performance each time the .LOV is refreshed. This option does not apply to Web Intelligence reports.
• When cleared, the list is refreshed only once at the start of a user logon session.
-> If the list contains values that regularly change, then you can select this option, but you should take into account the effect on performance.
-> If the list contents are stable, then you should clear this option.

Export with universe:
• When selected, the.LOV file associated with the object is exported with the universe to the repository. The universe domain and document domain must exist on the same data account. A list of values is stored in the document domain. The document domain does not have to be visible to the a user’s profile in Supervisor.
• You must create the list of values that is associated with the object for it to be exported. This list is saved as a.LOV file.
• When cleared, a.LOV file for the object is not exported to the repository. Select this option if you customize this list regularly. This allows your modifications to be exported and imported with the universe.

Exporting List of Values

You can export a list of values with the universe to the CMS. On the file system, the associated .LOV file is copied to a universe sub directory in the same folder that stores the universe file.

When a user runs a query in Web Intelligence using an object that is associated with a .LOV file exported from Designer, the list of values that is returned for the object is determined by one of the following:
• The data contained in the .LOV file.
• The SQL for the SELECT DISTINCT query defined in the .LOV file.

If you have created a condition in Designer to restrict the data values returned for an object, the restricted list appears, and not the default list of all the data values. The list retains all conditions and formatting implemented in Designer.
If you had not exported the .LOV file with the universe, then the object would simply return the default list with no conditions and formatting. A default .LOV file would then be created to hold the data.

Administering lists of values in the universe

You can manage all the lists of values in the active universe from the Lists of Values dialog box (Tools > Lists of Values). All the classes and objects are presented in a tree view. You can select any object, and access its list of values. You can perform the following actions from the Lists of Values dialog box:

-> Edit: Displays the Query pane used to define a query for the selected
object. You can define and edit existing queries for a list of
values.
-> Display: Displays the current list of values for the selected object.

-> Purge: Clears the contents of the list of values currently assigned to the selected object.
-> Refresh: Refreshes the display of the list of values.