Need help with annual salary formula for hourly employees

Question

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!

Jerry Casey · Answer

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

Regis Peregrin · Answer

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