Crystal Reports 2011 - Suppressing Information Based on Certain Criteria

Question

I'm going to attempt to word this question without being too confusing.

We have a report we want to show each patient and their insurance. Each of the insurance in the patient's record is number by an Order Number. However, we don't only want to show that; I want to put in certain criteria so that if Insurance A has order number 1 under the Patient ID, show all of this patient's insurance. If the patient does not have Insurance A in Order Number 1, do not show this patient nor any of their information on the report.

In the code below, guarantor is referring to insurance. So order number and guarantor name is what we're focusing on. Here's the code I've put into Section Expert for the Suppress option. What I assume is if it meets the criteria, TRUE will suppress the information, else FALSE will allow the information. However, this is not sufficient as it suppresses all of the other information.

if {billing_guar_order_no_ep.guarantor_order_number}  = "1" AND
{billing_guar_order_no_ep.guarantor_name} = "Medicare" then
    false
else
    true

What I'm assuming is it will need to iterate or loop through every patient and if it finds this information, list ALL of the patient's information and move forward, else suppress and move forward. I hope this makes sense.

Example:

|Patient ID|Order Number|Guarantor Name|
| -------- | ---------- | ------------ |
|1         |           1|Medicare      |
|1         |           2|Medicaid      |
|2         |           1|Medicaid      |
|2         |           2|Medicare      |

In the above example, what I want is for the report to show everything from Patient #1 (including all order numbers) and to not even show Patient #2 in the report. However, what's happening is Patient #1 does show up, but only Order Number 1; it suppresses all the other information.

What am I missing?

Answer 1

The query that you want will be an adaptation of this:

select *
from data d
where not exists (
  select 1
  from data
  where pat_id=d.pat_id
  and   order_id=1
  and   guarantor_name='Medicaid'
)

The 'Linking Expert' doesn't support this syntax, so you'll need to use a Command instead.

Process:

• get the current query by selecting Database | Show SQL Query ...
• create a new report
• select 'add command' from within the database expert
• paste the query, then adapt it