Is it possible to create Discoverer LOV's to display a lookup description e.g. Department Name and return the primary key code namely, Department Id when using an LOV for parameters for example?
The answer is Yes but the solution needs a bit of explaining so I thought I would do so here so that it is available to everyone. In order to make this happen we need to have a table that has an item and a code, and make sure that there is a one to one relationship between the item and the code. It's better to do this against a dimension table that contains unique codes and items. Then, you also need to make sure that there are indexes on both items.
Let's start with creating the new table, inserting some values into it, and creating a couple of indexes. Here's the code:
CREATE TABLE TST_TABLE AS (
SELECT 1 PERSON_UID,
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY') CHAR_DATE,
'Smith, Michael' FULL_NAME
INSERT INTO TST_TABLE VALUES (
INSERT INTO TST_TABLE VALUES (
434456, 'Evans, Bob');
GRANT SELECT ON TST_TABLE TO PUBLIC;
CREATE INDEX TST_TABLE_UK1 ON TST_TABLE (PERSON_UID);
CREATE INDEX TST_TABLE_UK2 ON TST_TABLE (FULL_NAME);
Discoverer Administrator steps
Next, I went into Discoverer Administrator and imported the table as a new folder from the database. I then went to the Full Name and created a standard list of values on it. With that done, I right-clicked on the item and from the pop-up list selected Properties. Scrolling down to the bottom of the list is a property called Indexed Item. Clicking this brings up a small dialog box in where I picked the indexed item called PERSON_UID.
Note: Indexed Item only becomes accessible when there is a) a list of values on the item, b) that there is another item in the same underlying table that is also indexed, and c) that there is a one to one relationship between the two items.
When I clicked OK, Discoverer displayed the following message:
There is nothing else to do except to click OK and allow Discoverer to create the other list of values. With this done, I am finished with Discoverer Administrator.
Working with Discoverer Plus
I created a new worksheet that includes the original item, FULL NAME, that we worked on above. Next, I created a new condition for the item but opted to use a parameter for the operand. On the right-hand side of the New Parameter dialog box, as shown below, is a checkbox called Enable users to select either indexes or values. I checked this box, completed the parameter and condition.
Note: the Enable users to select either indexes or values will be grayed out if the administrator has not completed the Discoverer Administrator steps outlines earlier.
When I execute this worksheet, it pops up the following parameter. As you can see this parameter is different to those that are normally seen inside Discoverer because this one has an additional drop-down box.
Clicking on the flashlight for the list of values, Discoverer displayed the following list. As you can see, it has both the value and the index, with the index being displayed in brackets.
Note: the above list is displayed no matter whether the user selects Index or Value in the first drop-down. The difference is only seen when the user completes the selection.
The following illustration shows what is returned when I searched by index and selected (2) Jones, Edward.
The following illustration shows the output of my worksheet when I complete the parameter selection. It makes no difference whether I select by index or by value because the worksheet still displays the value as originally requested.
Note: Do not attempt any of this inside Discoverer Desktop because it will not work. However, if you create a worksheet that has a parameter that uses Index and Value that parameter will be available inside Discoverer Viewer.