Hello all--

Thanks in advance to anyone who can help me. 

My issue is this - I have two tables containing financial information, Reserve and Payment.  Both tables contain an amount field and a pay code field.  I need to find some way to summarize data in these two reports, but only when the pay codes match.  For example, I need this (but in formula form)

When Reserve.pay_cd =1 and Payment.pay_cd =1, (sum(Reserve.amt)-sum(Payment.amt))

I have to do this for multiple pay codes within the same report. 

I've tried several variations on a formula for this, but none of them return the correct results so I thought I'd try to see if the real experts (all of you) knew something else I could try.

Thank you!




Edited by sl@ja - 11 May 2009 at 1:45pm

What sort of data would be helpful?  The incorrect results I receive or exactly what I'm looking for?

RESERVE TABLE
claim_id	pay_cd	reserve_amt
1	1	10
1	1	25
1	2	15
1	2	30
1	3	45
1	3	65
1	3	5
1	4	80
1	4	5
PAYMENT TABLE
claim_id	pay_cd	payment_amt
1	1	5
1	1	10
1	2	15
1	2	20
1	3	25
1	3	30
1	3	35
1	4	40
1	4	45

This is representative of what the data looks like in the database.  I need a way to take the reserve amounts with a pay code of 1 (10 & 25), sum them, and then subtract from them the payment amounts with the same pay code (5 & 10) so that my result is, basically, (10+25)-(5+10).   And then I need to do that with each subsequent paycode.

Right now, if I get any result at all, it's typically that I'm getting the sum of multiple pay codes (result sums pay codes 1 & 2, for example, but doesn't include 3) or I get some number that ends up being a multiple of the actual reserve amount.  I've also tried using running totals to total the payments and reserves separately (using the formula (table,field).pay_cd = 1 in the "evaluate" section) and then using a formula to find the difference between the two running totals, but I end up with the same results.

Does this help?  I'm sorry - I've never really used Crystal before in this capacity.  Most of the reporting I've done has been quite simple.  I used to work with a developer, but he's since moved on and I haven't had the time to advertise for someone new.

Actually, I'm using a subreport, linked to the main report by claim_id.  Now that you mention it, maybe I should just go ahead and work towards passing the values I get from the formulas within the subreports back to the main rather than trying to get the formulas within the subreport to work.  I've gotten the totals of the payments by paycode within one subreport and the totals of the reserves by paycode within another subreport to work.  Maybe I should just try to use formulas within the main report to find the difference between variables I've assigned values to in the subreports?

I'm sure I can either figure out how to do that or find the steps to do so since it's such a specific (and probably common) thing to do.  Thank you so much for your help.  You've saved me yet more hours trying to figure out how to do this in a way that I don't think was ever really going to work.