| Author |
Message |
bigburb
Newbie
Joined: 07 Oct 2009
Location: United States
Online Status: Offline
Posts: 26
|
   Topic: Subtracting a field based another field's value
Posted: 07 Oct 2009 at 11:45am |
|
Hello All, I'm a newbie so bear with me, running CR9. I'm trying to calculate a sum of employee hours and have it automatically subtract out the hours associated with lunch. The wall I'm hitting is that I don't know how to tell crystal that if "ind_code" = LUN then subtract the "act_hrs" associated with that record from the total.
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Oct 2009 at 1:08pm |
|
can you list your row level data and an example of how you need it calculated?
|
|
IP Logged |
|
bigburb
Newbie
Joined: 07 Oct 2009
Location: United States
Online Status: Offline
Posts: 26
|
Posted: 07 Oct 2009 at 1:33pm |
|
Yes. Lunch is considered indirect labor for purposes of clocking in and out, but I don't want it included in the group summary. This is how I'd like it to look.
Name Total Direct Ind. Code Total Indirect Ttl Hrs
Joe 1 0 1
Joe 0 SHIP 1 1
Joe 0 LUN .5 .5
Total 2
I've tried using the Select Expert to choose only records that are <>LUN but it filters out all the direct labor too for some reason. I was hoping maybe there was a formula I could use to total all labor and subtract any indirect labor that used the indirect code of "LUN" for that employee.
|
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 07 Oct 2009 at 1:38pm |
Group by Employee Name and create a running total:
global numbervar nTotalHrs:= nTotalHrs + (if {table.indcode}='LUN'
then 0 else {table.ttlhrs});
nTotalHrs;
//This is assuming that you want to include total hours into the final total for ALL ind code entries EXCEPT LUN.
Reset on the group header:
global numbervar nTotalHrs:=0;
Edited by FrnhtGLI - 07 Oct 2009 at 1:44pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Oct 2009 at 1:49pm |
FrnhtGLI's variable formula should work.
as another option, likely the total direct rows have a NULL in the Ind. Code field and this was making them drop when you did your select statement (<>'Lun'). If so you can use the select statment to filter out your Lunch records without dropping the other records by including that condition:
isnull({table.ind_code_field}) or {table.ind_code_field}<>'LUN'
|
|
IP Logged |
|
bigburb
Newbie
Joined: 07 Oct 2009
Location: United States
Online Status: Offline
Posts: 26
|
Posted: 07 Oct 2009 at 2:48pm |
|
DBlank, we tried that already, for some reason its not working. FrnhtFHI's code worked! The only problem is that it is only showing up for the first employee on my list?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Oct 2009 at 2:53pm |
You need 3 formulas for FrnhtFHI option. 1 for group header to reset to 0, 1 to calcualte and 1 to display in the group footer.
Or make one formula called 'NoLunch' (or whatever):
if {table.indcode}='LUN' then 0 else {table.ttlhrs}
Then just sum that formula at any group level.
Edited by DBlank - 07 Oct 2009 at 2:53pm
|
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 08 Oct 2009 at 6:54am |
Yeah, forgot to mention the 'Display' field.
The first field I gave is the 'Calculate' field. You can put this in the repeating detail section so that it evaluates the repeating records. This formula should be suppressed.
The second field is to 'Initialize' the running total for each group (set it to 0 and start fresh). This field should also be suppressed.
The third field is just a 'Display' field. This field displays the amount of the running total:
global numbervar nTotalhrs;
This field should be in the group footer.
My formula may work, and I only use manual running totals out of habit, but DBlank's one formula should get the same result and be easier to execute.
|
|
IP Logged |
|
bigburb
Newbie
Joined: 07 Oct 2009
Location: United States
Online Status: Offline
Posts: 26
|
Posted: 08 Oct 2009 at 7:42am |
|
OK its adding up all the indirect hours minus LUN just fine, but its not adding the direct hours in there?
What DBlank said was right, direct hours do have a value of "NULL", however making it part of the selection criteria isn't working for some reason. I don't mind have LUN on the report, just don't want it in the total.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Oct 2009 at 8:00am |
Start testing out the formula by placing it on the detail row and seeing when you can get it to display the altered total correctly on each line.
If it is not doing the IF-Then correctly for each row then the sum will always be off.
Try changing the FOrmula to use 'Default Values for Nulls' in stead of 'Exceptions for NUlls'.
or try changing the formula to handle the NULLS issue via code. Make sure to use the NULL condition first. I have found that for some reason the if you use the NULL second it is ignored (this would be the same in your select statement that was not working).
if isnull({table.indcode}) then {table.ttlhrs} else if {table.indcode}='LUN' then 0 else {table.ttlhrs}
Are the NULLS null becasue the one table leaves them null or becasue it is join of tables that are NULL values?
|
|
IP Logged |
|
|
|
