How do I report the total contractual working hours for a group of staff over a period of time

Peter Downer

Hello all,
I have been asked to report on the total number of contractual hours (working Hours) for a group of staff (based on Job Role type) in a department over a period between two dates.
Can anyone give me any clues as to;
Is this possible in OTBI? If so which subject area?
Does anyone have any examples of something like this or similar?

Thanks you

Peter Downer

This is now solved, the last piece of the jigsaw was that the TIMESTAMPDIFF part was returning as an integer, this was rectified by casting as a decimal and the issue was resolved

Weeks Between Working Dates

CAST((TIMESTAMPDIFF (SQL_TSI_DAY, {CASE WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date" Then CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective Start Date" END}, {CASE WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date" Then CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective End Date" END})) AS DECIMAL)/7

Total Contracted Hours for period

(CAST((TIMESTAMPDIFF (SQL_TSI_DAY, {CASE WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date" Then CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective Start Date" END}, {CASE WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date" Then CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective End Date" END})) AS DECIMAL)/7) * "Assignment Event Details"."Normal Hours"

Thanks to all who helped.

Riyaz Ali-Oracle

Hey Peter,

OTBI Subject Areas which can he helpful:

1. Workforce Management – Worker Assignment Real Time

Provides details on employee assignments, job roles, and departments.
Includes contractual hours if configured in the assignment attributes.

2. Workforce Management – Worker Time Entry Real Time
Tracks actual working hours recorded in the system.
Useful for comparing contractual vs. actual hours.

3. Payroll – Payroll Time Cards Real Time

If payroll time tracking is enabled, this subject area can provide time card entries.

Check below for more details:

HCM Subject Areas for Transactional Business Intelligence

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/25555/how-do-i-report-the-total-contractual-working-hours-for-a-group-of-staff-over-a-period-of-time

Hi @Peter Downer,

Welcome to the Oracle Analytics Community.

Can you please check the following post which might be useful.

Total current hours worked per employee in report — Cloud Customer Connect

Hope this help.

Thank you.

Peter Downer

Thanks all for your help so far, I have found the appropriate subject area and begun my analytic. I have now hit an issue; Each line in the analytic (catalog attached) is an assignment event with a start date and an end date, however, I wish to report on specific date periods provided by the presentation Variables obtained in the prompts.
For example I want a column (called Working Start Date or similar) to show a date value that is either the recorded effective start date from Fusion, or the F_Date variable (whichever is the greater) as a date. This can them be used both in filtering the data and also in the next step which is a calculation column.
The issue I am having is I keep getting an issue which I beieve is caused by the value brought in via F_Date not being a format compatible with either filtering or comparing with the Effective start date column.
How do I capture the prompt in the correct format? Or how do I convert it so it is compatible?

Thanks in advance

Peter Downer

PCC_Total Contracted Hours for Period (By assignment and Job).catalog

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/67205#Comment_67205

Hi @Peter Downer,

Can you please try the below report to see if there is any betterment?

PCC_Total Contracted Hours for Period (By assignment and Job) - BK.catalog

Hope this help.

Thank you.

Riyaz Ali-Oracle

Presentation variables like F_Date can sometimes be stored as text instead of a proper date format, causing issues when filtering or comparing with the Effective Start Date.

1. Convert the Presentation Variable to a Date Format:

Use the CAST or TO_DATE function to ensure F_Date is treated as a date:

sql
CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE)
or

sql
TO_DATE(@{F_Date}, 'YYYY-MM-DD')

2. Compare F_Date with Effective Start Date:

Use a CASE statement to determine the greater value:

sql
CASE
WHEN TO_DATE(@{F_Date}, 'YYYY-MM-DD') > Effective_Start_Date
THEN TO_DATE(@{F_Date}, 'YYYY-MM-DD')
ELSE Effective_Start_Date
END AS Working_Start_Date

3. Check this for Presentation variables and date formats:

Oracle Fusion Learning Cloud: OTBI: How to Use a Presentation Variable for a Date Range (Doc ID 2863813.1)

OTBI Variable Presentation Date Prompt Format DD-MON-YYYY — Oracle Analytics

Peter Downer

https://community.oracle.com/products/oracleanalytics/discussion/comment/67230#Comment_67230

Thanks All, I am nearly there now with all your help. I have a further issue - I deed to calculate the number of decimal weeks between The Working Start Date and the Working End Date. I am using the TIMESTAMPDIFF function (Days then result divided by 7) for this using the two calculated Working Date columns,

However, if I prompt Start Date 1 Jan 2025 to 31 March 2025, this column only returns 12 Weeks whereas this should be around 12.71 weeks. Something appears to be rounding somewhere but I cannot see where?

Any ideas?

Thanks

HR_All Total Contracted Hours for Period (Social Worker Roles) V2.catalog

In Case it helps here is the SQL for the calculated Columns;

Working Start DATE

CASE WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date" Then CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective Start Date" END

Working End Date

CASE WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date" Then CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective End Date" END

Weeks Between Working Dates

(CASE WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date" Then CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective End Date" END - CASE WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date" Then CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective Start Date" END)/7

Total Contracted Hours for period

(TIMESTAMPDIFF (SQL_TSI_DAY, {CASE WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date" Then CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective Start Date" END}, {CASE WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date" Then CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) Else "Assignment Event Details"."Effective End Date" END}))/7

Thanks

Riyaz Ali-Oracle

Hey Peter:

Try the below options:

1. Change the division operation to explicitly use a floating-point number (7.0 instead of 7):

sql
(CASE
WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date"
THEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE)
ELSE "Assignment Event Details"."Effective End Date"
END - CASE
WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date"
THEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE)
ELSE "Assignment Event Details"."Effective Start Date"
END) / 7.0

2. Fix for Total Contracted Hours for the Period
Ensure the TIMESTAMPDIFF result is cast as a decimal before dividing:
sql
CAST(TIMESTAMPDIFF(SQL_TSI_DAY,
CASE WHEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE) > "Assignment Event Details"."Effective Start Date"
THEN CAST(@{F_Date}{'YYYY-MM-DD'} AS DATE)
ELSE "Assignment Event Details"."Effective Start Date" END,
CASE WHEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE) < "Assignment Event Details"."Effective End Date"
THEN CAST(@{T_Date}{'YYYY-MM-DD'} AS DATE)
ELSE "Assignment Event Details"."Effective End Date" END
) AS DECIMAL(10,2)) / 7.0

3. If further precision is needed, use ROUND():
sql
ROUND(TIMESTAMPDIFF(SQL_TSI_DAY, Start_Date, End_Date) / 7.0, 2)

Peter Downer

https://community.oracle.com/products/oracleanalytics/discussion/comment/67189#Comment_67189

Hello Riyaz,
I am not seeing the second subject area, I guess it only shows if the organisation is using some kind of time recording system…

Riyaz Ali-Oracle

Workforce Management - Worker Assignment Real Time:

This is the primary subject area to report workers current assignment information. A worker can have multiple active or inactive assignments. This subject area provides details of all the worker assignments, including their start and end dates, assignment type, status type, status, and working hours. Important metrics such as the total assignment count, headcount, and FTEs can be reported by Business Units, Department, Location, and Manager. This subject area includes past and future dated assignment information. By default only a workers current assignment information as of todays date is retrieved. You can use a SQL prefix SET VARIABLE PARAM_EFFECTIVE_DATE ='@{P_DATE}{2018-12-31}'; (give the correct date format) to reset the default as-of date to report on assignment information in the past or future. This subject area does not report a history of assignment changes. You can use Workforce Management Work Assignment Event Real Time subject area to report a history of assignment changes.

https://docs.oracle.com/en/cloud/saas/human-resources/24d/faohb/Workforce-Management--Worker-Assignment-Real-Time-SA-3.html