Skip to Content

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • 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.


    Add comment
    10|10000 characters needed 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.