asked on # using a summary field in a formula calculation

I have a report listing customers and their payments (and other various info of course)...

Here is a simple example with their field names to show where the data is located->

achreport.studentname | achreport.payment

-------------------------------------------------------

George Worley | 100.00

Neil Heverly | 25.00

Galen Reigh | 100.00

Paul Stolz | (80.00)

--------------------------------------------------------

TOTAL: 145.00 <-------- 'Sum of achreport.payment'

My problem is that I need the "TOTAL" field to NOT reflect any negative values in its summary but still list it with the positive payments. I know you might be thinking, "Now how could that ever be correct?" Don't worry, for the information the report will give to the client, this is the exact logic I need.

I created a formula (called negativeTracker) to track any negative values and keep a running total:

shared currencyvar negAmnts := 0.00;

shared numbervar negCnt := 0;

if {AchReport.Payment} < 0.00 then

(

negAmnts := negAmnts + abs({AchReport.Payment});

negCnt := negCnt + 1

)

The part I'm having trouble with is using the result of this formula to adjust the "TOTAL" information accordingly! In other words, I can't seem to figure out how to accomplish this functionality in a formula -> Sum of AchReport.Payment + negAmnts

The 'negAmnts' part I've got, but I just can't seem to figure out how to reference "Sum of AchReport.Payment"

Thanks!

- Travis -

Here is a simple example with their field names to show where the data is located->

achreport.studentname | achreport.payment

--------------------------

George Worley | 100.00

Neil Heverly | 25.00

Galen Reigh | 100.00

Paul Stolz | (80.00)

--------------------------

TOTAL: 145.00 <-------- 'Sum of achreport.payment'

My problem is that I need the "TOTAL" field to NOT reflect any negative values in its summary but still list it with the positive payments. I know you might be thinking, "Now how could that ever be correct?" Don't worry, for the information the report will give to the client, this is the exact logic I need.

I created a formula (called negativeTracker) to track any negative values and keep a running total:

shared currencyvar negAmnts := 0.00;

shared numbervar negCnt := 0;

if {AchReport.Payment} < 0.00 then

(

negAmnts := negAmnts + abs({AchReport.Payment});

negCnt := negCnt + 1

)

The part I'm having trouble with is using the result of this formula to adjust the "TOTAL" information accordingly! In other words, I can't seem to figure out how to accomplish this functionality in a formula -> Sum of AchReport.Payment + negAmnts

The 'negAmnts' part I've got, but I just can't seem to figure out how to reference "Sum of AchReport.Payment"

Thanks!

- Travis -

Crystal Reports

Here's why I originally avoided that, but maybe I can go that route with a little help...

This is a report of the payments that were entered into a website by parents that can pay online into their son/daughter's cafeteria account for their school lunches, etc. When the report displays the data, it breaks the payment information up by the school their son or daughter goes to. A charge for a check payment is $1.50, but it's a per transaction charge. In other words, if Jane Doe makes a payment into little Jake Doe's school lunch account, she gets charged that amount + $1.50. On the same token, if Jane Doe makes a payment into little Jake Doe's account (who goes to the middle school), little Janelle Doe's account (who goes to the elementary school), and Johnny Doe's account (who is at the high school) all in one transaction, she also gets charged the total plus + $1.50.

Because the report gives school administrators a breakdown of payments into accounts on a per school basis, a running total on the latter example would charge $4.50 as the fee, rather than the $1.50 that was supposed to be charged. Why would it charge $4.50? Because each payment for each student, regardless of the fact it ties to one transaction id number, would be listed independantly (and thus calculated) in the schools' reported amount.

What would be optimal? If there was a way I could conditionally sum a field to ignore negative amounts without having to exclude it from the report in the record selection itself. ....or if I could do a running total on the achreport.payment field but somehow encode "on change of group" into an evaluation formula in addition to the logic to omit calculations on a negative achreport.payment value.

It looks like the easy solution would be to use evaluate a running total based on a formula, but I'd need to know how to also encode in the "on change of group" part into the formula, since Crystal (XI specifically) only lets one choose the evaluate method using a radio group.

Thanks again!

- Travis -

This is a report of the payments that were entered into a website by parents that can pay online into their son/daughter's cafeteria account for their school lunches, etc. When the report displays the data, it breaks the payment information up by the school their son or daughter goes to. A charge for a check payment is $1.50, but it's a per transaction charge. In other words, if Jane Doe makes a payment into little Jake Doe's school lunch account, she gets charged that amount + $1.50. On the same token, if Jane Doe makes a payment into little Jake Doe's account (who goes to the middle school), little Janelle Doe's account (who goes to the elementary school), and Johnny Doe's account (who is at the high school) all in one transaction, she also gets charged the total plus + $1.50.

Because the report gives school administrators a breakdown of payments into accounts on a per school basis, a running total on the latter example would charge $4.50 as the fee, rather than the $1.50 that was supposed to be charged. Why would it charge $4.50? Because each payment for each student, regardless of the fact it ties to one transaction id number, would be listed independantly (and thus calculated) in the schools' reported amount.

What would be optimal? If there was a way I could conditionally sum a field to ignore negative amounts without having to exclude it from the report in the record selection itself. ....or if I could do a running total on the achreport.payment field but somehow encode "on change of group" into an evaluation formula in addition to the logic to omit calculations on a negative achreport.payment value.

It looks like the easy solution would be to use evaluate a running total based on a formula, but I'd need to know how to also encode in the "on change of group" part into the formula, since Crystal (XI specifically) only lets one choose the evaluate method using a radio group.

Thanks again!

- Travis -

Hi Travis,

I think the formula you need would be this:

shared currencyvar negAmnts;

sum({AchReport.Payment}) - negAmnts;

If you're doing this in a group footer and only want the total w/in a group then qualify it like this:

shared currencyvar negAmnts;

sum({AchReport.Payment},{YourGroupByField}) - negAmnts;

Hope that helps,

frodoman

I think the formula you need would be this:

shared currencyvar negAmnts;

sum({AchReport.Payment}) - negAmnts;

If you're doing this in a group footer and only want the total w/in a group then qualify it like this:

shared currencyvar negAmnts;

sum({AchReport.Payment},{Y

Hope that helps,

frodoman

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

When I tried using (whether doing a running total evaluate formula or in an autonomous formula itself) ...

shared currencyvar negAmnts;

sum({AchReport.Payment},GroupName ({AchReport.TransID})) - negAmnts;

... I get the error: "This field cannot be used as a group condition field." while it highlights "GroupName({AchReport.TransID})"

shared currencyvar negAmnts;

sum({AchReport.Payment},Gr

... I get the error: "This field cannot be used as a group condition field." while it highlights "GroupName({AchReport.Tran

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
well, the equation you wrote was off ... but you got me to the solution & that's all I was looking for! thanks again! much appreciated!

- travis -

- travis -

Glad I could help

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Based on what you have said here I can't see why you don't just use a standard running totals field and only include the positive amounts.(Use the Evaluate When rule to limit it to positive amounts).

Pete