| Create a function in Discoverer [message #307019] |
Mon, 17 March 2008 09:49 |
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
|
Member |
 
|
|
Hi all
I wish you can help me in this inquiry
I have to complete a report in Oracle discoverer as follows :
requirment : get the company employees counts distributed on there origin countries (nationalities) in one report
but I must exclude from this count any employee who is absconded
Details
1- the master table of employee is (emp_key) which contain those fields :
Emp_code Varchar2()
Emp_Name Varchar2()
Emp_position Varchar2()
Emp_Join_DT Date
...
...
...
Emp_Origin_contry Varchar2()
and my problem is here that the master table (emp_key) doesn't contain any field to indicate the employee abosconding status and this particular information is maintained in another table called (EVNT_EMP)(employees Events)
which contain this fields
EVNT_EMP_CODE - employee code
EVNT_DT - Event date
EVNT_TRX_CODE - Event transaction Code ( when
... EVNT_TRX_CODE ='ABSC' then I have to consider
... this Employee as absconded)
now, I cannot manipulate the structure of those two tables because they are masters and I have to produce a report by discoverer that will take the count from first table with excluding those employees existing in second table with TRX='ABSC'
I thought about it and I would prefer to create a custom ITEM in EUL attached to first table (employee master) in which I will name it EMP_STATUS as (case when EVNT_EMP.EVNT_TRX_CODE ='ABSC' then '1' else '2' ) but this again not working in EUL since EUL maintain Custom created ITEMs on table level and I cannot get any peice from another table.
I need to solve this in EUL level not on each report because this calculation will be used in creating more than 35 reports in the future and I don't need to duplicate the condition in each sheet.
any one can help me in creating a function that will check and return either 1 or 2 then I can create Custom ITEM based on this function results
please support.
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
