Skip to Content

Computing sums with one to many relationship, Insurance Denials

Mar 14, 2017 at 05:50 PM


avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abhilash Kumar
Mar 15, 2017 at 07:56 AM

Hi Cheryl,

Try this please:

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.


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks, Abhilash.

This worked; however, I would also like to be able to use this formula field for record selection.

I would like the report to only include denials.

Record Selection in Crystal is not allowing me to use this formula for filtering.