Is there a way to use sums of pledge installments as criteria in formulas?

Robyn Kells:

I'm writing a report that requires stratifying expected total current-FY pledge payments by $1K - $10K and $10K+. I'm not looking for individual payments at this level, but *totals*, and this is where I'm having a problem.

Basically, I need a formula field that does this (field names are purely invented, and this doesn't work in Crystal):

if sum({CurrentFYPlgInstallments}) >= 10000 then {CurrentFYPlgInstallments}

...and also this:

if sum({CurrentFYPlgInstallments}) >= 1000 and sum({CurrentFYPlgInstallments}) <= 9999.99 then {CurrentFYPlgInstallments}

I would then simply create summary fields for both these formula fields.

Is there a way to write a formula that does this that will actually work in Crystal?? I'm getting rather desperate at this point; any suggestions are appreciated. Thanks!!

One way you could do the former is to create a running total of the balance, based on a formula.

First, group on the pledge IDKey. Then in that group's footer you set up a a running total -- the field to sum is {installment balance} evaluate using a formula. The formula is basically {FY of installment date} = {FY you're looking for}. Reset on the change of group.

So now that you have your running total, you should be able to set up the summaries you mention.

The key thing is that you have to work in the group footers, because running totals evaluate for each record as the report loops through the data, and output the result in the footer section.

If what you're trying to do is first sum the FY worth of pledges, and then if that total is in whatever range, show the list of installments, that's going to be a bit more difficult...