A list of values is a list that contains the data values associated with an object. These data values can contain data a database, or a flat file such as a text file or Excel file. List of Values is a powerful feature that allows users to select or pick from a list when setting conditions in a query and while entering the prompt input.
Universe design tool automatically associates a list of values with the object whenever you create 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.
List of values are stored in a file with a .LOV extension in the universe sub folder created under the same folder that stores the universe file. Database object LOV can be refreshed automatically based on the Database values changes.
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 the universe design tool, the values are returned from the .LOV file and not from the database.
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.
List of values Properties:
Associate a List of Values – When selected, allows a list of values to be associated with the object. It is selected by default for dimensions and detail objects. Not selected for measure object. When cleared, no list of values is associated with the object.
List Name – Name of the .LOV file that stores the returned list data and by default designer will assign one distinct name for each LOV objects. If you want to change the LOV name you can edit.
Restore Default – Restores default name assigned to the .LOV file at object creation.
Allow users to edit this List of Values – Users can edit the list of values file in Web Intelligence. This option is does not apply to personal data files such as Excel spreadsheets because those files are not exported to the repository. A user can edit a local file, or change the target list of values for another local data file.
Automatic refresh before use – 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.
Hierarchical Display – Display the cascading list of values as a hierarchy in Web Intelligence.
Export with Universe – The .LOV file associated with the object is exported with the universe to the repository.
Delegate Search – Delegate search property to allow Web Intelligence users to limit the number of values returned in the list of values. This option will be disabled other than Character Data type objects.
EDIT – When you click by default object will be selected in Query panel with Distinct SQL. We can perform / customize your LOV in many different ways based on your requirement.
• If you don’t want distinct List of values for that object, you can edit the SQL and update with Custom SQL based on your requirement. Also you can add your condition into the condition pane in the query panel to restrict your LOV.
• If you want to display your LOV in a more detailed way, you can update the LOV as mentioned below. For example, in Country Code object LOV, you can display Country Name also, so that Users can understand the required country names easily.
Edit the Country Code Object and add Country Name object and click Save&Close.
In the LOV, Country Name is only for Display purpose and you cannot select or use in the query panel and this is user friendly to provide additional information.
Also you can add sorting for the LOV objects in Query panel to display them in order.
Display – Displays the LOV values either “Tabular View” or “Hierarchical View”.
Create Personal Data LOV:
1. Go to Tools – List of Values – Edit List of Values and pop up will open with all LOV available objects.
2. Select the object and click EDIT and perform whatever the changes you want in that Object LOV.
3. If you want to load external LOV for a particular Object, then click the object and select Personal Data from the Properties section. It will show warning, if you edit, it will replace with new values and click OK. It will ask you to select your personal data files and then RUN. Then the selected object LOV will get loaded with personal data.
Using Cascading List of Values (LOVs)
Similarly you can create Cascading List of Values. Cascading List of Values is a Hierarchical LOV selection method which allows users to select highest level of list first followed by the next hierarchical level when setting conditions in a query.
Select Tools-> List of values -> Create Cascading List of values.
More Details, please refer….
Linked Universe – Lists of values
Lists of values associated with core objects are not saved with the derived universe, when it is exported to the repository. For more details, please refer..