Chapter 9: List of Values

---

The list of values is a powerful feature that allows users to select from a pick list when setting conditions in a query. You as a designer determine which objects have lists of values via the object properties.

Because users can select conditions from a list of values, they do not need to enter conditions manually and therefore do not need to memorize lists of codes or guess how many leading zeros there may be in a particular field. Designer allows you to customize the default list of values even further to present meaningful names with the codes or to shorten particularly long lists into a more manageable size.

How List of Values Works

When a user adds a condition to a query, BusinessObjects essentially launches a second query and returns a list from the dimension or lookup table in the RDBMS, as shown in Figure 9-1.

Figure 9-1: A list of values query is associated with an object in the universe. It queries the dimension or lookup tables in the RDBMS to present users with a pick list for conditions in a user query.

The query file exists on either the full client's local disk or on the WebI midtier as \BusinessObjects\userdocs\universe\object.lov, where universe is the name of the universe used in the query and object is the object used as a condition in a query. Designer automatically creates these query files whenever a universe designer enables a list of values on a universe object. Unless a designer customizes the list of values, the SQL generated is always:

SELECT DISTINCT   Table.object FROM   Table

Notice that BusinessObjects adds a DISTINCT keyword to all list of values queries. This ensures that users receive only a single row for each distinct value. For example, a Customer dimension table may have multiple rows for each customer ID as changes to customer information are kept and time-stamped. In setting conditions in a query, users will need to see the unique customer ID only once.

The BusinessObjects repository, universe domain sends customized object.lov files to the client or midtier when the user accesses a new or modified universe (refer back to Figure 5-6, step 3). When users add a condition to a query, they must select an operand, as shown here:

Users can either manually enter the value for the condition or select the operand Show list of values (Figure 9-1, step 1). This operand will send the object.lov query to the dimension table in the RDBMS (Figure 9-1, step 2). The RDBMS sends the query results back to the client (step 3). Users then select which condition value(s) they want in the original query condition (step 4). When the user launches the main query, the object.lov query is no longer involved.

Designers can customize the object.lov query to shorten long lists of values. For example, if you have millions of products, you may want to prompt users first to select a product category. If the users do not know the codes or spellings of the product categories, then BusinessObjects may first launch a prodcat.lov query. In this respect, steps 2 and 3 may be repeated multiple times until the user finally selects values to add to the query in step 4. The size of the object.lov files also may change over time as the number of products changes or as users select different product categories.

Once a user has accessed a particular object.lov file, the query results are permanently installed on the full-client PC or the thin-client midtier. The next time a user wants to use the same object as a query condition, the list of values or pick list is immediately available without having to query the RDBMS.

---