Thursday, December 14, 2006

Creating indexed items

I saw this recent posting on the OTN forums:

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.

Example table:

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,
Trunc(SYSDATE) BIRTH_DATE,
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY') CHAR_DATE,
123456 TAX_ID,
'Smith, Michael' FULL_NAME
FROM dual);


INSERT INTO TST_TABLE VALUES (
2, TRUNC(SYSDATE),
TO_CHAR(TRUNC(SYSDATE),
'DD-MON-YYYY'), 234456,
'Jones, Edward');

INSERT INTO TST_TABLE VALUES (
3, TRUNC(SYSDATE),
TO_CHAR(TRUNC(SYSDATE),
'DD-MON-YYYY'),
434456, 'Evans, Bob');

COMMIT;

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 what is returned when I searched by value and selected the same person.

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.

I hope this helps show how indexed items works.

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.

13 comments:

Anonymous said...

Could you please provide me solution for LOV validation in discoverer viewer. I have one operating unit LOV and warehouse LOV. Now I want to validation for above two LOV. Means if user select US operating unit than warehouse LOV should give only US warehouses.

Please help me to sorting this issue.

Thanks in advance.

DJRathod

Michael said...

Hi
You need to use what are termed Cascading Parameters.
To use these both items need to be in the same folder and both need to have lists of values. These work best when there is a hierarchy between the items.
Cascading parameters can only be created in Discoverer Plus 10.1.2.
Best wishes
Michael

Unknown said...

Hi Michael

Your example with the index item works great.

I picked up a funny bug with the version of Discoverer I've been using. When switching on "Display Search Dialog" for a LOV with an indexed item, the placeholder that returns the text or index item actually concatenates both index and text values rendering the LOV useless. E.g. "(2) Jones, Edward".

According to Oracle Support, we need to upgrade to later patchset.

I have another problem. If I create a parameter based on LOV with indexed item, how do I pass and index value to it from another workbook e.g. from a hyperdrill link. The called workbook always reverts to the text LOV. What can I do to resolve this?

Any help will be greatly appreciated.

Thanks

Kind regards

Greg

Anonymous said...

Being new to discoverer, I have the following question about indexed items.

I have a two table join inwhich an LOV is created from the 2nd table. What do I need to do in Discoverer to use the key returned in the LOV as part of the predicate when filtering records in the first table?

Let me know if more information is needed.

Thanks,

Michael Chaikowski
Nashville,TN

Michael said...

Hi Michael Chaikowski
Did you try creating a cascading parameter using one of the items to filter the other?
I think the issue you have is that the items are in different folders and I have a strong suspicion that this is a no-no in Discoverer. From what I have seen, I believe the items need to be in the same folder.
Best wishes
Michael

Michael said...

Hi Greg
Which patchset did Oracle suggest you upgrade to?

With regards to passing an indexed item across a hyper link I think you're going to be unlucky. I'm pretty sure this won't work for the very reason you hinted at in your example. The parameter in the second workbook is looking for an actual value to filter on.

Best wishes
Michael

Anonymous said...

Hi
I want to do the LOV but in a customize folder, how can i do that?
thanks...

Manuel Bermejo

Unknown said...

Could you please elaborate on how to use cascading parameters and how to define them in Discoverer Desktop 10g?

Anonymous said...

Hi!

I want to restrict the no of values to be shown yo the user to some values corresponding to him.

eg. Suppose their are 100 department.
In first case, User A is a simple employee, then he should be shown the department corresponding to him say Dept 30 in the LOV to select.

In second, User B is a Zonal Director and has access to the department under the Zone say 10 Departments, Then User B should be able to see all the department under his responsibility in the LOV and not others.

In third case a Country Director should be able to see all the department.

I tried to create the custom folder like

Select deptno from dept where deptno in (select deptno from user_dept_mapping where lower(ename) = lower(user))

user_dept_mapping --> is the table where each user has been mapped to the department they can see.

I get the values as per the query in discoverer sheet, but while in LOV it shows every value.

I am helpless ...kindly help.

Michael said...

Hi Soumya
You cannot created cascading parameters in Desktop. This feature is restricted to Discoverer Plus versions 10.1.2.0.2 and higher.

You can execute cascading parameters in Desktop 10.1.2.0.2 and higher but you cannot create or maintain them in there.

Michael

Kamran said...

Hi,
Above is a beautiful explanation of Indexed Item.
what I want is that whatever the user selects i.e. Index or value, the numeric value should ONLY be pass to the database
because If description or value is changded in the database for example '10:John Michael' to 'John-Michael' in the database then in no data comes in the reports. as the user for example has viewed report based on this selection and saved the report,next time when he/she will open the report nothing will come because the Name is changed in the database.
Please suggest the solution
Thanks in Advance

Unknown said...

Hi,
Would you please show me the way of creating a LOV with hard coded values.
the LOV i need should display same values all the time. so instead of creating a LOV that reads from a table (Query) i need a LOV in which values are alredy stored (Hard coded).
Thanks in advanec

Michael said...

Hi Ashraf
I will be covering this in my upcoming webinar on 14th January. If you would like to sign up follow this link:

http://ascbi.com/question.htm

Followers