What’s a list of values (LOV)
A list of values contains the data
values associated with an object. A LOV can have two origins : a database or an
external file such as a text or an Excel File.
LOV from Database.
By
default, when you drag and drop a table column to create an object, Business
Objects will create an object with an associated LOV. The LOV's name will be a
generic one until you change it. The “Restore Default” button will give it back
its generic name.
A
LOV is generated by a simple SQL statement like “SELECT DISTINCT TABLE.COLUMN
FROM TABLE”. But you can modify the statement by adding some other objects
through the Query Panel, you can also add some conditions on these objects.
You
can’t associate a LOV coming from another database because the LOV is linked
through the connection - you can use a call to a DBLINK however.
You
can apply an ascending or a descending sort on the LOV, always through the
query panel.
Particular example: How
to sort the list of values with a number and not with a label when you want the
label to be at the first column of your LOV?
If you have the number of month and the
12 months' names as labels, you want your list of values to be sorted as
follows:
January 1
February 2
March 3
.......
So you create your list of values as
follows :
Month + number of month, but it now appears
like this :
April 4
August 8
........
The SQL of the query is: SELECT
DISTINCT MONTH.MONTH, MONTH.NUMBER FROM
MONTH
Solution:
Edit the SQL, Add ORDER BY NUMBER, MONTH and check the option Do not generate
SQL before execution, it will solve your problem. You can also apply a sort on
number.
LOV from
a flat file
You can also assign personal data to a
list of values. This is very useful if you want to allow users to select values
among the values stored in an Excel file for example, define personal LOV on
product based on Myproduct.xls.
To do this, in Designer module, you
have to:
-
Select the 'Lists of Values' command
from the Tools menu
- Select the object whose list of values you
wish to edit
-
Click “Personal Data Button”
-
Select a file and a format and run it.
If your external file is sorted in a particular way, note
that Business Objects will automatically modify the sort and apply an ascending
sort.
Note that you can’t have any dynamic
link with the external file, it means that you have to refresh the LOV if your
external file has changed.
You can allow 3 things on LOV based on
the database :
-
Allow
users to edit the LOV
-
Automatic
refresh before use
Export with universe
(before being exported, a lov must be generated by a display or an edit)
0 comments: