OTBI Analysis to get Working Days count and total hours spent by employee in given date Range

Question

Dear Team,

I was trying create a Dashboard with multiple Analysis and Graphs.

One requirement is to get Working Days count in the given Date Range and Total Work Hours spent by Employee in the same date range.

I am unable to identify any dimention representing working day or not in the Time.Calendar date or any other subject area.

Please advise how to achieve this requirement.

SurendraPB · Answer

Dear Nathan,

Thank you very much for the information..!

There is challenge here is when I am taking count of working days (excluding weekends) in one column and another column with days spent by employee in particular date range. Then working days total is changing with days spent column.

Is there any way to achieve/fix the working days count value..?

Please advise on this as well.

Thank you very much in advance.

Nathan CCC · Answer

Hi, This depends on your definition of a "working day".

Weekends: In your legislative region your definition might be to simply exclude weekends. So that bit is easy. You just add a filter to your aggregate to count days only if the day of the week is Monday to Friday not Saturday, Sunday.

Public Holidays: are more difficult. It is likely that you have configured public holidays in your system as Calendar Events. Your calendar events may be linked to geographic regions. So if the entity you are query has a point in space in that geography then you can lookup what the public holidays are in that location.

I do not think this is built into any of the calendars in any of the subject areas in the metadata repository database. But in the application database tables and views this data is in the following tables.

PER_CALENDAR_EVENTS

https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/percalendarevents-26821.html#percalendarevents-26821

per_calendar_events_pk calendar_event_id

per_calendar_events_pk   unique  fusion_ts_tx_idx    calendar_event_id

per_calendar_events_u2   unique  default short_code, business_group_id

fk per_cal_event_coverage    per_calendar_events calendar_event_id

PER_CAL_EVENT_COVERAGE

https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/percaleventcoverage-24496.html#percaleventcoverage-24496

per_cal_event_coverage_pk cal_event_coverage_id

per_cal_event_coverage_pk    unique  fusion_ts_tx_idx    cal_event_coverage_id

You can setup a reference to a geography in per_geo_tree_node on tree_structure_code, tree_code, tree_version_id
, tree_node_id where coverage_type = upper('G') for Geography and category = upper('PH') for Public Holiday.

https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/pergeotreenode-5649.html#pergeotreenode-5649

Page "Manage Calendar Events"