Discoverer Query to Display Document Name, Folder, Business Unit etc.

Following query can be used to display Document Name, Document Key, Folder Name, Folder Key, Documents Sheets, Business Unit, Document Created By in discoverer.
Please note that the query is in crude format and not tested, any suggestion to improve this is appreciated.

 SELECT DISTINCT b.doc_developer_key Document_developer_key
              , b.doc_name document_name
              , a.qs_doc_details worksheet_name
              , c.obj_name folder_name
              , c.obj_developer_key folder_developer_key
              , d.ba_name Business_unit
              , fu.user_name created_by
 FROM           eul4_us.eul4_qpp_stats a
              , eul4_us.eul4_documents b
              , eul4_us.eul4_objs c
              , eul4_us.eul4_bas d
              , eul4_us.eul4_eul_users e
              , eul4_us.eul4_ba_obj_links f
              , fnd_user fu
 WHERE          a.qs_doc_name = b.doc_name
 AND            a.qs_doc_owner = e.eu_username
 AND            b.doc_eu_id = e.eu_id
 AND            c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
 AND            c.obj_id = f.bol_obj_id
 AND            d.ba_id = f.bol_ba_id
 AND            TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)

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

SELECT * FROM EUL4_US.EUL4_DOCUMENTS

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 to 99999 to allow maximum number of records to be fetched.


Keywords: Discoverer 10g

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.