Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Need help with annual salary formula for hourly employees

Received Response
12
Views
2
Comments
JHaden3
JHaden3 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:17PM in Oracle Analytics Cloud and Server

I am using Subject Area Compensation - Salary History Detail Real Time to build an Oracle report.  I'm a formula novice, and have read several Community discussions, but I still have something wrong.

Would someone please help fix my formula to come up with an annual salary for hourly employees.  Description of need:  If salary basis is USHOURLY, then pay rate  X  normal working hours  X  26 pay periods.  The formula I have tried is:

CASE WHEN "Salary Basis"."Salary Basis Name" ="Salary History"."Salary Amount" THEN "Salary History"."Salary Amount" * "Worker"."Normal Working Hours" * 26

Oracle returns this error:

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. (HY000)

[nQSError: 43113] Message returned from OBIS. (HY000)

[nQSError: 27002] Near : Syntax error  (HY000)
[nQSError: 26012] . (HY000)
SQL Issued: SELECT CASE WHEN "Salary Basis"."Salary Basis Name" ="Salary History"."Salary Amount" THEN "Salary History"."Salary Amount" * "Worker"."Normal Working Hours" * 26  FROM "Compensation - Salary History Details Real Time"
OK (Ignore Error)

Thank you!

Answers

  • Regis Peregrin
    Regis Peregrin Rank 2 - Community Beginner

    Formula writing is very logical.  I read your formula to say, "When the Salary Basis Name is equal to the Salary Amount, then..."  But the Salary Basis name is text,  'USHOURLY', right?  And the Salary Amount would be the hourly rate of pay, or a number, correct?  I don't see where text could equal a number.

    Try reading up on Filter Using.  Basically you want to filter your formula to only apply to workers with a salary basis = 'USHOURLY'

    I'm pretty new to this forum, so I hope that I'm not speaking out turn, or giving poor advice.

    Regis

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Hi Regis,

    By naming the subject area, I assume you're using OTBI?  We don't know your data models.  But your formula looks like it should be:

    CASE WHEN "Salary Basis"."Salary Basis Name" ='USHOURLY' THEN "Salary History"."Salary Amount" * "Worker"."Normal Working Hours" * 26

    (of course, you also need ELSE and END clauses).

    Jerry