Hiding key field in LOV when using that field in the query

building reports desktopintelligence
1

Mike Malone@CONTEXT
09/18/98 02:08 PM

We have a large table with a unique numeric key which we want to use in our queries. However, these numbers have no meaning to the users. We would like to create a list of values which returns this numeric key, but which only shows the account name, which is meaningful to the user. Is there any way to do this?

Listserv Archives (BOB member since 2002-06-25)

2

Mike,

the first solution that springs into my mind would be to display the LOV in the hierarchial view:

  1. Goto object properties
  2. Go for List of Values|Edit…
  3. Define two result objects: KeyValue, AccountName 4) Make sure that the objects are in the same order as above 5) Sort by AccountName
  4. Click OK (or Run), can’t remember the button name now… 7) If you display the results in hierarchial view, the AccountName should appear as the root node for each KeyValue 8) If users select the AccountName, the KeyValue will be used for SQL,still.

HTH,

-Harri

Listserv Archives (BOB member since 2002-06-25)

3

Mike,
You can not hide a field in a LOV. However there are some good workarounds.

You could include both the code and a description in the LOV side-by-side. Just edit it and include the extra field. The field on the left is the one used in the where clause. If the description is in another table though, this will create an extra join. If the table is very large, then for performance reasons, you might want to consider using a subquery. Create a predefined condition with SQL like this: fact_table.numeric_code IN (select numeric_code from lookup_table where desc_field IN @Prompt() )

The @Prompt will reference the description from the lookup table.

Best Regards,

Andrew J. Erthal
Orion Project Manager
Business Objects University
aerthal@businessobjects.com
Chicago Office (847) 391-9898
St. Louis Office (314) 209-1994
St. Louis Fax (314) 209-7926

MIKE Text
We have a large table with a unique numeric key which we want to use in our queries. However, these numbers have no meaning to the users. We would like to create a list of values which returns this numeric key, but which only shows the account name, which is meaningful to the user. Is there any way to do this?

Listserv Archives (BOB member since 2002-06-25)