### Computing sums with one to many relationship, Insurance Denials

I need to create a Crystal Report showing how much money has been denied by insurance companies. "Denied" means \$0 paid at any time.

My report currently contains Vouchers, Charges, Payments and a "Denial Formula Field" which I created stating that "If the Charge - Payment = Charge amount, then mark this as a "Denial". If untrue, it is not a denial."

This works correctly if denied and never paid later. However, it does not work if they do, in fact, "Pay at a later time". In that case, the claim is truly "NOT a Denial".

It seems like I will need to do something with grouping and/or change my formula field to get this to work.

Any help appreciated!

Voucher# Charge Payment Denial Date Paid

100 500 0 Yes 2/1/2017 - Should be "NOT" denied

100 500 250 No 3/1/2017

100 500 250 No 3/10/2017

210 100 100 No 3/1/2017

301 100 0 Yes 12/1/2016

10|10000 characters needed characters exceeded

• Mar 15, 2017 at 07:56 AM

Hi Cheryl,

1. Insert a Group on the Voucher#.

2. Move all fields except the 'Payment' field to Group Header.

3. Suppress the Details Section.

4. Create a formula (@Denial) with this code:

```local numbervar den := Sum({Payment}, {Voucher#});
If den = 0 then 'Yes' else 'No'```

5. Place this formula on the Group Header.

-Abhilash