Print Page | Close Window

Formula HELP

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7111
Printed Date: 18 May 2024 at 3:10am


Topic: Formula HELP
Posted By: 4milligans
Subject: Formula HELP
Date Posted: 21 Jul 2009 at 11:39am
I think this should be pretty straight forward and yet can't seem to figure it out. 
 
My report is designed pull time clock info into an understandable report for our payroll department.  I have to separate vacation, personal, sick, regular and overtime from one another. 
 
All hours are calculated per punch pair (2 punch pairs per day) in the punch.depthours column.  There is a punch.nonworked column that indicates either "vac", "per" or "sic" and those hours are also stored in the punch.depthours column.
 
I have the following formulas set up:
 
//VACATION-extracts only vacation hours
 
IF {PUNCH.NONWORKED} = "Vac"
THEN
{PUNCH.DEPT HOURS}
 
I have the same formula set up for "Per" and "Sic"
 
The Regular hours CANNOT include any vacation, personal or sick time.  I have the following formula:
 
//Regular-extracts all hours that are worked
if isnull ({PUNCH.NONWORKED})
then {PUNCH.DEPT HOURS}
 
Anything over 80 hours is considered overtime.  So for my OT formula:
 
if sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID})> 80.00
then sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID}) - 80.00
else 0
 
 
PROBLEM:
 
My Regular hours formula is including time that should be paid as OT, because of how it reads it's not taking out what's been extracted as OT....confusing?
 
example:  Mary works a total of 82.50 hours.  8 of those hours were vacation hours and 2.5 hours of OT.  So my report should read: 
 
REG              OT             Vac                     Total Hours
72.00           2.50           8.00                       82.50
 
the way my formulas are written produces:
 
REG              OT             Vac                      Total Hours
74.50           2.50           8.00                        85.00
 
Anyway of extracting the OT out of the REG - Any help would be greatly appreciated!!


Replies:
Posted By: DBlank
Date Posted: 21 Jul 2009 at 1:16pm
Sum(@Regular,punch.employeeid) -
(if sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID})> 80.00
then sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID}) - 80.00
else 0)

Posted By: 4milligans
Date Posted: 22 Jul 2009 at 8:56am

This causes it to stop extracting the NONWORKED hours out of the total....

Posted By: DBlank
Date Posted: 22 Jul 2009 at 10:54am

Sorry but it is hard to track all of your items here...

Basically I was showing that you can add in another part to a formula to conditionally subtract the OT. If you need to also subtract the nonworked then it would be something like:
(Sum(@Regular,punch.employeeid) - SUM(nonworked,punch.employeeid))
-
(if sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID})> 80.00
then sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID}) - 80.00
else 0)

Posted By: 4milligans
Date Posted: 23 Jul 2009 at 11:55am

Thanks for your help, but still can't seem to get this to work - so frustrating because it seems like a simple equation @REG - @OT - back to the drawing board.

 
 

Posted By: DBlank
Date Posted: 23 Jul 2009 at 12:04pm
Did you take the existing formula and then tack on the equivalent of - OT...
- (if sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID})> 80.00
then sum({PUNCH.DEPT HOURS},{PUNCH.EMPLOYEE ID}) - 80.00
else 0)
 
The only thing that you have to be careful of is that you cannot use the same formula to determine the OT and then change the results of that same formuls for reg hours.
If this is what is happening just create another formula that you don't display to make the OT calculation and the Reg hours.

Posted By: 4milligans
Date Posted: 23 Jul 2009 at 12:41pm

I understand (in theory) exactly what your saying and it makes total sense.  I think part of my problem is the way this db is set up:

EMPLOYEE ID IN DATE IN TIME OUT TIME DEPT HOURS NONWORKED
-2119716584 6/22/2009     8.00 Sic
-2119716584 6/23/2009 1:33p 5:00p 3.45  
-2119716584 6/23/2009 7:52a 1:03p 5.05  
-2119716584 6/24/2009 1:53p 5:00p 3.12  
-2119716584 6/24/2009 7:52a 1:01p 5.02  
-2119716584 6/25/2009 7:53a 1:00p 5.00  
-2119716584 6/25/2009 1:30p 5:01p 3.52  
-2119716584 6/26/2009 7:50a 1:00p 5.00  
-2119716584 6/26/2009 2:00p 5:00p 3.00  
-2119716584 6/29/2009 7:53a 1:02p 5.03  
-2119716584 6/29/2009 1:32p 5:00p 3.47  
-2119716584 6/30/2009 1:32p 6:00p 4.47  
-2119716584 6/30/2009 8:53a 1:02p 4.03  
-2119716584 7/1/2009 1:30p 5:00p 3.50  
-2119716584 7/1/2009 7:52a 1:00p 5.00  
-2119716584 7/2/2009     8.00 Vac
-2119716584 7/3/2009     8.00 HOL

I need to be able to differ between worked and non worked hours.  unfortunately the NONWORKED column just states what type of hours were NONWORKED but they are still included in the total hours column.  So those hours need to be extracted as well as any time that is over 80 hours (worked and nonworked).....

Posted By: DBlank
Date Posted: 23 Jul 2009 at 1:48pm
The below should work per week but not at any higher level...
Group on EmployeeID
Group on InDate (per week)
Create a Running Total (RT) as "Vac":
set it as a SUM of DeptHours
Conditional Evaluate as a formula
{PUNCH.NONWORKED}="VAC"
Reset on GRoup2
Repeat for "Personal" and "Sick" (if needed)
Create a RT as "Worked":
same set up but evaluate formula is:
isnull ({PUNCH.NONWORKED})
Create a formula field as "Regular worked": if {#Worked}<80 then {#Worked} else {#Worked} - ({#Worked} -80)
Create another formula field as "OT": if {#Worked} <80 then 0 else {#Worked} -80
Place all on GFooter 2
Will this do the the trick?


Print Page | Close Window