Holiday Logic

[Former Member]

Hi Gurus,

I have dimension able Called

1. Employee Information,

2. Employee work location

3. Calendar table

I have to take details like How many days how many employee worked in one particular location, basically i have to find daybetween two dates, but my issue is i have only one date field in Employee Work location with flags, Start means the date he started in the working location End flag will indicate the Date he left that location.

When i am finding the day difference i have to consider holidays as well.So i am maintaining separate calendar table which will give me dates with flag information if it is working day flay is 'Y' else it will show 'N'.

so i have created the two Queries in the WEBI two bring the informations one with Employee Work Location Start date with Start flag and other Query with employee Work location End Date with End Flag, so now i can found the day difference between these two Dates .

Now how to implement this Holiday logic since i am using two separate Query to achive my report. It is Really giving lot of headache to find this.

Any idea really helpful!!!!!

[Former Member]

Why are you using two queries? Can't you select in one query both Start date and End Date?

Because it would make it easier this way:

1. In your Universe make sure that the table with employee information is related to calendar table. For example: employeestable.start_date = calendar.date

2. Then in calendar class you have to add this object "Count Days" whose formula is: Count(calendar.date)

3. back to your report your query should look like this:

In the objects pane: "employee_name" "Count Days" etc.

In the filter pane: calendar.date >= employeestable.start_date and

calendar.date <= employeestable.end_date

and calendar.working_day_flag = "Y"

[Former Member]

Thank you for your reply

My Calendar Table is Joined with Employee work location table based on the Location Key since i am maintainng the Location key in both Employee Location and calendar Table.

I need to count How many employee's worked in the particular location for how many days.

Formula looks like this

Count(EmpId where(dayabetween(Startdate;Enddate))

so i think your suggestion will not work out; if i am wrong correct me.

Thanks much,

-B-

[Former Member]

If I'm not wrong there is no way of making daysbetween() function consider holidays.

That's why I suggested making a count of the days in Universe.

Do you have access to your Universe? Can you create a derived table?

Because it could be other approach.

[Former Member]

Yes i have a access to Universe.

Pls tell me what you want me to create in the derived table.

[Former Member]

Well, I don't know which database you are using or your tables and fields names, so you can change them with the ones you use.

Derived Table:

Select count(A.date) as total_days_between , S.employee_id  as total_days, S.date as start_date, E.date as end_date 
from Calendar as A, Employee_work_location as S, Employee_work_location as E

where 

S.Start_flag = "Y"
and
E.End_flag   = "Y"
and
S.employee_id = E.employee_id


and


A.date between S.date and E.date
and
A.working_date_flag = "Y"

group by S.employee_id  , S.date, E.date

You can add other fields you need in the select line, and in the group by line too. Then after you create this derived table create a class containing the fields total_days_between, etc. And use this new class to build a query in a new report.

Edited by: PadawanGirl on Jan 28, 2011 6:20 PM

Edited by: PadawanGirl on Jan 28, 2011 6:23 PM

Edited by: PadawanGirl on Jan 28, 2011 7:06 PM

[Former Member]

Thanks PadawanGirl,

but it is not giving as i expected. since i have to put this in single cell of the report.

[Former Member]

In the single cell you have to use the object named total_days_between from the new class created with the derived table in Universe Designer.

The other fields in the derived table are just if you want to make a report by employee, or start_day, etc.

But if you don't want to do something so detailed then this is the query you need to use in your derived table, only to obtain the total days of everyone.

Select count(A.date) as total_days_between 


from Calendar as A, Employee_work_location as S, Employee_work_location as E
 
where 
 
S.Start_flag = "Y"
and
E.End_flag   = "Y"
and
S.employee_id = E.employee_id
 
 
and
 
 
A.date between S.date and E.date
and
A.working_date_flag = "Y"

Edited by: PadawanGirl on Jan 29, 2011 4:32 PM

[Former Member]

Perhaps you'll need to make some adjustments to this query, adding other conditions, etc. until finding the right query for your needings.

If count(A.date) as total_days_between

doesn't show what you need you could try using a distinct statement:

count(distinct A.date) as total_days_between

[Former Member]

Thanks PadawanGirl for your reply

I believe i have not explained my issue correctly. so let me explain in detail again.

Emp Id is coming from employee table,Work location is coming from work location table ,start date and end is coming from two other table,full date and Is working day field is coming from calendar table. Now I need to find number of woking days between start date and end date if any holiday coming in beween i need to exclude those days in my count. and my business wants to know how many EE worked in one location in based on the number of days and my sample report model is given below. Here the issue is how to place the formula in cell since in the final report i have to display only the final count not the detail information

In my universe calendar table is joined with Work location table based on the EMPid.

My report should display like below format

Employee Work Location : India

<=1 Day | 3 Days| 5 Days

How many EE Worked | 1 | 1 | 0

Employee Work Location : US

<=1 Day | 3 Days| 5 Days

How many EE Worked | 1 | 1 | 0

Using holiday flag i can find number of working days between two given dates, and this is working fine if i placed Empid, Employee work location, Start date, End date date(Calendar table) and Is working day objects in the block , but my business needs how many EE worked in each location for the period of time, like how many EE worked only one day, how many EE worked 2 days..... like that. so i have created the section based on the EE work location and created the block and mentioned statically like in the Row How many EE Worked and in the columns i have divided like <=1 day 3 Days and so on.... So i have to found like =Count(EMp Id where(Number of Days)<=1) which i will place in the first column of my block to know how many EE worked in that location for only one day. Here is the issue i am getting, i believe i have to put correct calculation context to do this.

I hope now i have explained my issue more clear.

Any idea and suggestions pls to handle this.

Thanks much,

-B-

[amrsalem1983]

i had the same problem before

and i couldnt find any solution in webi or BO directly

i've done an oracle function to take the two dates and calculate the different in days execulding the holidays and weekends

and then return it back as a number.

to create this function, you have to have a calendar table to be able to define which days are holidays and weekends

and i was calling this function from a derived table into BO unvierse

good luck

[Former Member]

yes i do have calendar table in my universe with holiday flag and i have brought that in to WEB I query and it is working fine when i put all the objects in the block, if i remove the all the objects from the block and if i write the formula like below it is giving some unknown value and which is not correct.

My formula is ( Count(Empid where(No_Of working days<=1) in(Empid), here No_Of_working days is a webi variable calculated the working days based on the holiday flag.

The same formula is working fine if i put all the objects in the block along with No_Of_working days variable.) so i believe i need to do some calculation context here, i have tried many combination's, but i am not success.

Thanks much,

[Former Member]

Hello, Amr.

Do you have the syntax you used in your derived table?

[amrsalem1983]

i dont have it right now, but i can guide you

i made one database function in oracle database like this

create or replace function CalcDaysHoliday(P_DateStart Date,P_DateEnd Date) return number

is

Begin

.....

write the code here

return No_of_days;

end;

in your derived table

select table.column, CalcDaysHoliday(table.Date1,table.Date2)

from table

i hope it works fine with you