Workers Comp Report - Field for Exclude OT / DT not calculating as expected

[jessimica602]

Hey Everyone!

I've tried asking the UltiPro community but can't seem to get help there. I need to pull the regular rate from a given check to multiply by the OT and DT hours. My struggle here, I can't use the field [Hourly Pay Rate] since we import the pay rates when process payroll and I can't guarantee they'll be the same.
I have three expressions:

1 [OT and DT Hours]:

IF([Earnings Code] in ('DT','DT2','OT','OT2','OTC','OTFLT'))
THEN([Current Hours])
Else (0)

2 [Regular Rate of Pay]:

IF([Earnings Code] in ('REG')) THEN([Pay Rate]) ELSE(0)

3 [OT Excess Wage]:

[OT and DT Hours]*[Regular Rate of Pay]

but the OT Excess Wage pulls zeros. I understand why it's not working, it's looking at the earnings code in the [OT and DT Hours] and seeing that it's not REG (i think).
I've tried a CASE statement
CASE [Earnings Code]
WHEN 'OT' THEN [Pay Rate] for [Earnings Code] in ('REG')
ELSE (0)
END

Shot in the dark and of course it didn't do what I hoped. I just can't think of another way to make this work. Any ideas?

[MichaelB]

The way that you have your two expressions set up, one or the other will always be zero. So 0 x whatever will always be zero. If I understand what you are attempting to get at correctly, you may just need one data item with this expression in it:

Code Select Expand

CASE
WHEN [Earnings Code] in ('DT','DT2','OT','OT2','OTC','OTFLT') THEN [Current Hours] * [OT Pay Rate]
WHEN [Earnings Code] = 'REG' THEN [Regular Rate of Pay]
ELSE 'Error'
END

I included [OT Pay Rate] above but you didn't outline what that was. Might just be [Regular Rate of Pay]*1.5. If so, you can just substitute that formula for that data item.

[jessimica602]

Close, so close. The end result should be the OT / DT hours * regular pay rate. We can only report "Base wages", so I can't use the elevated rate of [Regular Rate of Pay] * 1.5. Example: Worked 1 OT paid at OT rate of $15.00, 40 regular hours worked were paid at $10.00,  the report should show $10.00 (1 OT Hour * $10.00 Regular Pay Rate). I'm having a hard time separating the Regular Rate from the Regular Hours so I can use it on the OT hours.
I tried using your case statement but made the OT Pay Rate Field x1 instead of 1.5, that didn't work either, it's just pulling the regular rate of pay.

[MichaelB]

I guess maybe show a sample of the data and what you want the outcome to be? I think I am having a hard time visualizing the problem you are facing.

[jessimica602]

I attached a screen shot that shows an example laid out in excel, top part is the calculation and the bottom is what is needed on the report. Not sure if that will help.

..this report is the bane of my existence now   :)

[seb24c]

Is the issue that your data is not laid out like your Excel example? More like:

Name	Earnings Code	Hours	Rate
Person A	REG	40	10.00
Person A	OT	1	15

So then your calcs are currently:

Name	Earnings Code	Hours	Rate	OT/DT Hours	Reg Rate of Pay	OT Excess Wage
Person A	REG	40	10.00	0	10.00	0
Person A	OT	1	15.00	1	0	0

If this is what you're working with, then you should just need to change the Reg Rate of Pay calculation to:

maximum ( if ( [Earnings Code] = 'REG' then ( [Pay Rate] ) else ( 0 ) for [Name] )

(or replace Name with a unique ID, if there is one)

That will put the 10.00 on both rows, and fix the 0 problem.

If this is NOT what your data looks like, it would help to see what it looks like.

[MichaelB]

To echo what seb24c wrote, it would be helpful to see how the data is laid out to help you get the final calculation you need.

[jessimica602]

Sorry for the delayed reply

"maximum ( if ( [Earnings Code] = 'REG') then ( [Pay Rate] ) else ( 0 ) for [Name] )"


This worked beautifully!!!!! This added the reg pay rate to all rows, which let me multiply OT hours by the regular rate!!

Thank you so so much!