Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

I need to programmatically calculate a grand total. I have a PostTax field which contains either a 1 or 0. I also have an amount field.

So, if the PostTax field contains a 1 I need to sum all values and it can result in a negative value so I need to default it to 0 if that happens.

Then I need to add the above result to the sum of amount where PostTax = 0.

I'm trying to write this in a formula. This is in a subform and the only purpose is to return the grandtotal.

Any ideas?

So, if the PostTax field contains a 1 I need to sum all values and it can result in a negative value so I need to default it to 0 if that happens.

Then I need to add the above result to the sum of amount where PostTax = 0.

I'm trying to write this in a formula. This is in a subform and the only purpose is to return the grandtotal.

Any ideas?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialDon't have exact code but it's somehting like this

Dim PostTaxAmt as double

Dim PreTaxAmt as double

Dim GrandTotal as double

if postax = 1 Then

PostTaxAmt = PostTaxAmt + Amount

End If

if pretax = 0 Then

PreTaxAmt = PreTaxAmt + Amount

End If

If posttaxamt < 0 then

posttaxamt = 0

end if

If pretaxamt < 0 then

pretaxamt = 0

end if

grandtotal = postaxamt + retaxamt

The formula is presumably being evaluated for each record (and Amount is a field in the record). When you check your posttaxamt and pretaxamt totals to see if they're negative, you're looking at the total as of that record. The problem with that is that if a total -- at that point -- is negative, you reset it to 0, which then throws off the total from that point on. Say you had records with 5, 10, 15, -50, 20. If you add all of those together, you get 0. But with a formula like the one that you posted, the total will be 30 on the 3rd record, and then -20 on the 4th, which the formula will change to 0. Then it adds 20 for the last record, and you get a total of 20, instead of 0.

Presumably you don't want to check for a negative total until you have the final total (ie. after the last record has been read).

Then again, without seeing your actual formula, and knowing where you're using it, and knowing about anything else that might be manipulating your variables, this is just guesswork.

James

```
dim posttax as double
dim pretax as double
IF {PayrollDeduction.postTax} = true Then
IF SUM({PayrollDeduction.amount}) < 0 Then
posttax = 0
ELSE
posttax = SUM({PayrollDeduction.amount})
END IF
END IF
IF {PayrollDeduction.postTax} = false Then
IF SUM({PayrollDeduction.amount}) < 0 Then
pretax = 0
ELSE
pretax = SUM({PayrollDeduction.amount})
END IF
END IF
formula = posttax + pretax
```

```
dim posttax as double
dim pretax as double
dim grandtotal
IF {PayrollDeduction.postTax} = true Then
posttax = posttax + ToNumber(PayrollDeduction.amount)
END IF
IF {PayrollDeduction.postTax} = false Then
posttax = posttax + ToNumber(PayrollDeduction.amount)
END IF
IF pretax < 0 THEN
pretax = 0
END IF
IF posttax < 0 THEN
posttax = 0
END IF
GrandTotal = posttax + pretax
```

The order of the data should NOT affect the result unless you can control the order.

As you have your code, every time that formula runs it checks if the result of the intermediate sums is < 0, is that what you want or do you want to add ALL the pretax values then test for < 0

Where in the report is this formula?

mlmcc

That aside, as I said before (and mlmcc also said), you're checking for negative totals after each record, which is probably not what you really want (for the reason that I described earlier).

James

Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

You could use a running total with the Execute When set to Post Tax = 1

Another way would be to have a formula like

Open in new window

Then do a summary on that fieldmlmcc