This content has been marked as final. Show 9 replies
I am very surprised that no one had a solution to this, THIS must be a common problem.
Well I have found a solution and here it is, if anyone thinks this is the wrong way then pls let me know.
I created a function where I pass in the code and the name of the code set and the function returns the display valuye of the code. The function is deterministic so it can be used in MV's.
I then register the function within Discoverer.
I then create a new item and base it on the registered function.
and it all works as I want.
I'm not sure if our way of doing this is the easy way but we have a similar situation. We've tried it a couple of different ways. One way is we join the main table to the code table. We then create a custom folder that combines the values from the code tables we want and the values from the main table that don't have code tables. We then create a LOV based on the code table and select all of the items in the custom folder that would use that same code table.
We actually don't use this solution any more because we needed to use outer joins for this to work and that had a serious impact on performance. Instead what we did was we created a custom folder where we decode the fields we want to have a LOV attached to them. This makes them have the same names that are contained in the code table. We then create a LOV from the code table and attach it to the items in custom folders. The initial set up of this is rather lengthy but it provided the results we want. And actually in the end we found it best to create a view that has all the decodes done for us and we just attached the LOVs to the view. This ultimately has given us our best performance results.
What I've done is create an item class in the business area for the column.
Create a new item in the folder and then create a function to decode the column (M,F,U) to the description (Male, Female, Unknown). Create an item class that will list the values based on the new item you created. The item you created in the folder should have a + sign next to it that lists the values.
In the desktop or Viewer, that column is now an LOV with the full description whenever you want to create a condition on it or a parameter.
I am new to Discoverer and I am trying to duplicate the solutions presented here.
As I understand, a function has to be created and then registered within Discoverer. This function will accept a code parameter and then will return a display value. A new item is created in Administrator using the function
and then an item class is created that uses the new item to create a LOV of the display values.
If this is correct, how should the function be constructed? Specifically, what do you put in the function to return the display value? For example,
I have a table of state codes and state names and in the LOV I want to display state name but use state code.
CREATE OR REPLACE FUNCTION state_nm_from_code_sf
(st_code IN NUMBER)
st_name := whatgoeshere;
Thanks for your help.
To duplicate the solutions proposed here, specifically, Shannon's solution, do the following:
1) Create a view (or a custom folder) which contains the fields you are interested in. Obviously, if you create the view at the database level, you'll need to create a folder for it anyway in Discoverer.
2) Write a DECODE calculation either at the view level, or as a calculated item on the folder.
Following on from the Gender example, if your code set contains the values M, F, U (for Male, Female, Unknown), your decode calculation will look like this:
DECODE(gender, 'M', 'Male', 'F', 'Female', 'Unknown')
I've missed out the case for 'U', so that anything that isn't 'M' or 'F' = 'Unknown' - this includes null values
3) Create a custom folder is Discoverer, call it something like 'LOV Genders', and write the following SQL to create the LOV:
SELECT 'Female' gender from sys.dual
SELECT 'Male' gender from sys.dual
SELECT 'Unknown' gender from sys.dual
3) Create an Item Class based on this custom folder, and retrieve the values for the item class by clicking on the (+) next to it. You can call it LOV Genders too.
4) Attach the Item Class to your Gender field in the original folder.
The user will be presented with the (list 'Female', 'Male', 'Unknown') when you create parameters based on this field, or when selecting from the folder.
Hope this helps.
Like make this thread a "sticky" at the top? Or improve the LOV feature in future Discoverer upgrades? For my users I've created views that bring in the description (Male,Female, etc) as part of the folder and let Discoverer create the LOV via item classes. They don't like to click and retrieve the desciption in the related detail folder. Idealy they would like everything denormalized as one table with a billion columns they can scroll through but that's not going to happen. =)