Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Formula HELP Post Reply Post New Topic
Author Message
4milligans
Newbie
Newbie


Joined: 21 Jul 2009
Online Status: Offline
Posts: 8
Quote 4milligans Replybullet Topic: Formula HELP
    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!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
4milligans
Newbie
Newbie


Joined: 21 Jul 2009
Online Status: Offline
Posts: 8
Quote 4milligans Replybullet Posted: 22 Jul 2009 at 8:56am

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



Edited by 4milligans - 22 Jul 2009 at 9:22am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)


Edited by DBlank - 22 Jul 2009 at 10:54am
IP IP Logged
4milligans
Newbie
Newbie


Joined: 21 Jul 2009
Online Status: Offline
Posts: 8
Quote 4milligans Replybullet 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.

 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
4milligans
Newbie
Newbie


Joined: 21 Jul 2009
Online Status: Offline
Posts: 8
Quote 4milligans Replybullet 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).....
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.