Crystal Reports (Display Duplicated Rows)

building reports crystal reports
1

Hi,
I got a requirement where I should have the report displays ONLY the duplicated rows

Fields are:
Patient_ID, Patient_Name, Service_Code, Date_Of_Service, Provider_Name, Guarantor_Code, Total_Charge_Amount

  • So the manager wants to look at ONLY the duplicated rows in order to find out patients whom claimed the service more than once*

In SQL I can use Having clause to achieve this, but since I’m working on a huge existing report, I really need to find a solution within Crystal

Any inputs would be appreciated
Darren

Darren… :us: (BOB member since 2013-02-22)

2

One way to do it is:

Create a section on PatientID, sort the section on it. Create a formula vCountPatient to count the patient id and reset the count for each patient id(group level). Based on the formula vCountPatient suppress the section if the count is <=1. You should get all the duplicates.

BO_Chief :us: (BOB member since 2004-06-06)

3

thanks…cHief

Darren… :us: (BOB member since 2013-02-22)

4

I have found a much more easy way to include ONLY rows having a duplicate field. Go Insert … --> Section --> Details --> Check on “Suppress (no drill down)”

Insert this formula:

previous({FIELD}) <> {FIELD} AND next({FIELD}) <> {FIELD}

This basically suppress any row having a field value that differs from the previous one AND the subsequent one, achieving the same result without even grouping or counting.

Hope this helps,

Paolo Marani, Italy

marantz (BOB member since 2014-01-14)

5

Liked your formula. I inserted it into a couple of reports, and it worked perfectly.

One caveat I found, however, the values in the field must be sorted. After adding the field into the Record Sort Expert, the formula you suggested worked perfectly!

Thanks for the tip,

Rx

rx2587 (BOB member since 2014-02-24)

6

Thanks! This helped me figure out my issue. I figured I’d share my modification since this thread came up so readily on Google.

In my case, I was looking for orders in which the same material code was used more than once, so I had to extend the above formula, but it worked:

(toText({eWRServiceOrders.Order Number}) + {eWRServiceOrderChemicals.Material Code}) <> (toText(Previous ({eWRServiceOrders.Order Number})) + Previous ({eWRServiceOrderChemicals.Material Code}))

AND
(toText({eWRServiceOrders.Order Number}) + {eWRServiceOrderChemicals.Material Code}) <> (toText(next ({eWRServiceOrders.Order Number})) + next ({eWRServiceOrderChemicals.Material Code}))

This essentially converts the order number to a string then concatenates it with the material code, then compares the result to the rows above/below. What I end up with is two entries of the same line.

Thank

splattedone (BOB member since 2016-11-18)