Print Page | Close Window

formula to include duplicates only

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21859
Printed Date: 03 May 2024 at 11:53pm


Topic: formula to include duplicates only
Posted By: Paula J
Subject: formula to include duplicates only
Date Posted: 27 Jan 2016 at 11:03am
I have a report that I only want to include the duplicate records for each matching MRN (reports is grouping on MRN).
Report currently looks like this
 
MRN         Patient Name  Acct No  Adm Date
1159XX     Uncle Sam      111        01/01/2016
1159XX     Uncle Sam      222        01/02/2016
1159XX     Uncle Sam      333        01/01/2016
1169XX     Donald Duck   444        01/02/2016
1169XX     Donald Duck   555        01/04/2016
1179XX     Daisy Duck     666        01/03/2016
1179XX     Daisy Duck     777        01/03/2016
 
I want it to look like this
MRN       Patient Name     AcctNo   Adm Date
1159XX  Uncle Sam         111        01/01/2016
1159XX  Uncle Sam         333        01/01/2016
1179XX  Daisy Duck        666        01/03/2016
1179XX  Daisy Duck        777        01/03/2016
 
 


-------------
Paula J


Replies:
Posted By: DBlank
Date Posted: 28 Jan 2016 at 3:14am
display your fields in the group header and suppress the details.
the group header will display the content from the first row in the group so you need the details to be sorting in the order that gets the first row the data you want to display.
Also note that this is only about how the data is being displayed. All of the 'duplicate' data is still part of the report so any calculations or summation you do include all of the rows.

Posted By: Paula J
Date Posted: 28 Jan 2016 at 3:26am
This is not working.  I do NOT want just one line.  I want duplicate lines if the adm date is the same and I don't want line when there is not a duplicate.
 
I think my example explains what I want.


-------------
Paula J

Posted By: DBlank
Date Posted: 28 Jan 2016 at 3:56am
I misunderstood.
Create group level 2 using a string formula.
I can't tell if some of your fields are strings or numeric so you may need to adjust to deal with that.
//group level formula
totext(table.mrn,0,"")+table.patientname+totext(table.admitdate,"MM/dd/yyyy")
insert a count of the MRN at this group level.
Now whenever that count is>1 you can display else hide

Posted By: Paula J
Date Posted: 28 Jan 2016 at 4:11am
Sorry, I should have said I already have a count on the mrn and set to >1. 
So what you see is a result of that. 
What I need is a way to now say...if mrn match and the admit date on those matching mrn match then print the two records.


-------------
Paula J

Posted By: DBlank
Date Posted: 28 Jan 2016 at 4:17am
I understood that.
reread my response. It is accounting for the admit date duplication.
Although I think you can likely remove your top level grouping completely and replace it with the formula grouping i suggested. Sorting might be an issue though.

Posted By: DBlank
Date Posted: 28 Jan 2016 at 4:19am
also if your MRN is unique per name you can likely exclude the name form the formula I gave you.
Or you can just a second group on the admit date set to the day and do another count exclusion/suppress on that.

Posted By: Paula J
Date Posted: 28 Jan 2016 at 4:42am
Got it thanks so much.

-------------
Paula J


Print Page | Close Window