Summary off a formula

[tcimis]

I have a formula:

IF {SR_UNION_REPORT.ERNCD} in ['HUN', 'HOL', 'VAU', 'VAC', 'VPO','JUR','BON', 'LAP']
THEN
0
ELSE
{SR_UNION_REPORT.OTH_HRS}

The formula works fine at the employee level. When I go to get a sum of all the employees at the union level, the total includes the hours from earning codes I said set to 0. The same thing happens for the Grand Total. Any suggestions.

Thank you!

 

[lbass]

If you insert a sum on this formula, it will only sum for the codes not excluded. If you are seeing a higher number than expected, it could be that you have row inflation instead. Take a look at your data at the detail level to see if you have repeating values. It might help if you show some sample data here at the detail level.

-LB

 

[tcimis]

See detail below. Empl A has 8 hours of VAU which doesn't appear in the details. It is not added in at the employee level but is added in at the union level.

NAME DATE REG HRS CODE OTH_HRS TOT HOURS
5/3/2009 40.00 0.00 40.00
5/3/2009 0.00 OT1 12.50 12.50
5/3/2009 0.00 OT2 8.00 8.00
5/3/2009 0.00 0.00 0.00
5/10/2009 0.00 OT1 4.00 4.00
5/10/2009 40.00 0.00 40.00
5/17/2009 40.00 0.00 40.00
5/17/2009 0.00 OT1 9.00 9.00
5/24/2009 32.00 0.00 32.00
5/24/2009 0.00 OT1 3.00 3.00
Emp A 152.00 36.50 188.50
5/3/2009 32.00 0.00 32.00
5/10/2009 0.00 OT1 9.00 9.00
5/10/2009 32.00 0.00 32.00
5/10/2009 0.00 OT2 -4.00 -4.00
5/10/2009 0.00 REG 2.00 2.00
5/17/2009 0.00 OT1 1.00 1.00
5/17/2009 0.00 OT1 8.50 8.50
5/17/2009 40.00 0.00 40.00
5/24/2009 32.00 0.00 32.00
5/24/2009 0.00 OT1 8.00 8.00
Emp B 136.00 24.50 160.50
5/3/2009 6.50 0.00 6.50
5/3/2009 0.00 OT1 5.00 5.00
5/3/2009 32.00 0.00 32.00
5/3/2009 0.00 OT1 4.00 4.00
5/10/2009 40.00 0.00 40.00
5/10/2009 0.00 OT1 1.50 1.50
5/17/2009 40.00 0.00 40.00
5/17/2009 0.00 OT1 8.00 8.00
5/24/2009 0.00 OT1 11.00 11.00
5/24/2009 40.00 0.00 40.00
Emp C 158.50 29.50 188.00
UnitTotal 446.50 98.50 537.00

Thank you for your assistance.

 

[Madawc]

Do running totals using your test as a formula test. The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Your sample does not show the codes you mentioned in your formula at all. Nor does it show your formula results. Please provide a sample that includes these and show the summary results you are getting on the formula.

It looks like you might have some null codes, so you should use a formula like this:

IF isnull({SR_UNION_REPORT.ERNCD}) or
trim({SR_UNION_REPORT.ERNCD}) = "" then 0 else
if {SR_UNION_REPORT.ERNCD} in ['HUN', 'HOL', 'VAU', 'VAC', 'VPO','JUR','BON', 'LAP'] THEN
0 ELSE
{SR_UNION_REPORT.OTH_HRS}

-LB

 

[tcimis]

I was able to correct the issue using running totals. Thank you for your assistance.