Saturday, August 7, 2010

Discoverer Info


How to query/find discoverer reports in Oracle using APPS Schema

The table to store discoverer is stored in the EUL Schema. For e.g. if the name of EUL is EUL4_US then a schema with that name will be created in oracle database. Run following query to know the name of table
SELECT owner,table_name
 FROM   all_tables
 WHERE  owner = 'EUL4_US' AND table_name LIKE '%DOCUMENT%'

The above query in this case will list table_name EUL4_DOCUMENTS

All the discoverer report names are stored in this table, so
should display name, owner etc of discoverer reports


Unable to Retrieve All Rows Message in Discoverer

Problem: Trying to export report data into excel or when clicking on Tools--> Retrieve all Rows, Not all Rows have been Retrieved. Data may be inaccurate. error message is displayed.

Resolution: By Default in discoverer only 10000 records are set to be retrieved. This needs to be changed to a higher number in order to avoid this problem.
Click on Tools-->Options. Select Query Governer Tab and change the value in Limit retrieved query to99999 to allow maximum number of records to be fetched.

Keywords: Discoverer 10g

MONDAY, MAY 12, 2008

Create List of Values (LOV) in Discoverer

The Item classes in Discoverer are treated as List of Values (LOVs). A List of value can be created and referenced by several fields of the folder.
Following are the steps to create LOV in discoverer. In the example below I am creating LOV on US States.
1) Connect to Discoverer using Admin and select the business area where folder/LOV needs to be created.
2) Create a custom folder for US States using following query
select * from ar_lookups
where lookup_type like 'STATE'

3) Select the field for which Item class is to be created and right click on that to select New Item Class. Click next on the wizard and finally click finish button to complete creation of Item class. In our example, the LOV is created on the lookup code field.

4) Now if any parameter is created based on Lookup Code column then the List of value will be attached to it.
5) This LOV can also be referenced by another column. For example there is another folder which has column Bill to State. Select that column and right click to go to the properties. Click on Item Class and select the Item class created in step 3.


Dependent Value Parameter in Discoverer

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.


Not Required Parameter in Discoverer

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

No comments:

Post a Comment