trs28

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 -

ASKER

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

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