Hi Everyone,
I have a report to build based on employee statuses in the HR system. An indicative I have attached herewith. I am able to get the desired result with plain SQL. I am presently using this SQL script in a direct database request and getting desired results in a dashboard with a prompt on a particular month.
I am however not sure if this is the best way to do it. I am wondering if only I could do this by creating objects (variables) in the RPD. I am using OBIEE 11.1.1.7
Look forward to your expert advise and suggestions.
An excerpt of the code is as follows:
/*Total Count of employees*/
select 'Grand Total' as Head,T.PREVIOUSMONTH as Mon, count(STD.Emp_No) as Counts
FROM STD_RATES STD
INNER JOIN Time_Dim T on STD.DATE_ID = T.PREV_date_id where T.MON = '@{month}{Apr}'
group by T.PREVIOUSMONTH
UNION
select 'Grand Total' as Head,T.Mon as Mon, count(STD.Emp_No) as Counts
FROM STD_RATES STD
INNER JOIN Time_Dim T on STD.DATE_ID = T.date_id where T.MON = '@{month}{Apr}'
group by T.Mon
UNION
/* New Joinees*/
/* No employee in M1, joined in M2 */
select 'New Joinees' as Head,T.PREVIOUSMONTH as Mon, NULL as Counts
FROM STD_RATES STD
INNER JOIN Time_Dim T on STD.DATE_ID = T.PREV_date_id
WHERE T.MON = '@{month}{Apr}'
UNION
select 'New Joinees' as Head, T.Mon as Mon, count(Emp_No) as Counts
FROM STD_RATES STD
INNER JOIN Time_Dim T on STD.DATE_ID = T.date_id
--and ACTIVE = 'A'
and T.DATE_ID = to_number(to_char(trunc(STD.DOJ,'mm'),'yyyymm')) and T.MON = '@{month}{Apr}'
group by T.MON
UNION
/*Exits*/
/* Final settlement in M1, and does not feature in M2 */
select 'Exits' as Head, T.PREVIOUSMONTH as Mon, count(Emp_No) as Counts
FROM STD_RATES STD
INNER JOIN Time_Dim T on STD.DATE_ID = T.PREV_date_id
AND ACTIVE = 'X'
AND T.MON = '@{month}{Apr}'
AND NOT EXISTS (SELECT 1
FROM STD_RATES STD2
INNER JOIN Time_Dim T on STD2.DATE_ID = T.Date_id
WHERE STD.EMP_NO = STD2.EMP_NO
--AND ACTIVE = 'A'
)
group by T.PREVIOUSMONTH
UNION
select 'Exits' as Head, T.Mon as Mon, NULL as Counts
FROM STD_RATES STD
INNER JOIN Time_Dim T on STD.DATE_ID = T.Date_id
WHERE T.MON = '@{month}{Apr}'