Dashboards, Applications & Business Intelligence

In a previous post, we covered the trick on how to find a column in database, now we will do similar stuff with Discoverer.

Sometimes it becomes cumbersome to look for a certain item in the Discoverer EUL. Most of the APPS EUL have huge folders and lots of items. Some of the items appear across multiple folders and multiple business areas. So if you are assigned to develop a report or asked to modify a Discoverer workbook to add a new column, what are the steps you would perform?

One of the obvious step is to find if all the necessary columns are available in the EUL and this can be visually done through scanning the Business Areas and Folders in Discoverer Desktop. It involves quite a bit of eye-balling and squinting, if you know what I mean

Usually I try to run some queries to expedite my process and avoid heavy eye-balling. One of the queries is as described below.

First find out which EUL you want to query against. There are multiple EUL owners so we need to determine which one. The following query shows us all the available EUL owners

select owner from all_tables where table_name = ‘EUL5_OBJS’;

For the current case, we are going to use the owner as ‘EUL5_US’

select i.exp_name item_name
,f.obj_name folder_name
,b.ba_name
from eul5_us.EUL5_expressions i
, eul5_us.EUL5_objs f
, eul5_us.EUL5_ba_obj_links l
, eul5_us.EUL5_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper(’%') –folder item
and upper(b.ba_name) like upper(’%')                     –business area
and upper(f.obj_name) like upper(’%)                     –folder
order by b.ba_name,f.obj_name,i.exp_name

In the above query replace the “eul5_us” with the appropriate owner value in your case. Now lets say we need to find out where “Invoice Date” is located then we would modify the query as shown below and execute it

As you can see above, it lists all the folders and Business areas where “Invoice Date” exists.

Similarly you can query to find particular folders using the above query.

Note : This query is part of the Discoverer Dashboard

Enjoy!