Solved

Crystal 2008 Remove duplicate records from Report

Posted on 2014-02-27
7
5,188 Views
Last Modified: 2014-02-27
I have a report that I need to show a field where there may be more than one of the same only once.  To give a better example the report has 2 items -
Invoice Number and Part - I have the report grouped so that it breaks after each invoice number and gives the number of items for each invoice.  But there can be the same type of item for each invoice but only want it to show one time.  I have tried using the Suppress if Duplicated option in formula Editor for this field but it does not work - still showing the duplicate items.  Can someone please tell me how I can suppress duplicates.

Thank You
0
Comment
Question by:jajen
  • 4
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Lee Ingalls
ID: 39892026
Before discussing JOIN type and direction... have you tried using Database menu item - Use Distinct Records?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39892034
Apparently they aren't duplicate records.  A duplicate records means ALL fields/columns have the same value.

Do you need all the fields the query is pulling in?
If not delete the fields you don't need and see if that eliminates the "duplicate" records.

You can also add a group on the item number and put the information in the group header or footer rather than the detail section.

mlmcc
0
 
LVL 7

Expert Comment

by:Lee Ingalls
ID: 39892049
mlmcc,
That's where I was leading... if after trying Select Distinct Records, the results were the same then they are not "duplicate" records.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:jajen
ID: 39892166
I had not thought about using distinct record to change the selection criteria into a sql query.  What I have is pretty simple a report that list the Invoice Number and all the items in that invoice number - the user has a parameter field to select a date and then the report returns all the information for that date. I then have the report grouped by invoice # so that it gives a total item count for each invoice,  but we cannot count the same items need to only show one in the count for invoice. Could someone provide the proper way to create a sql query with Distinct Records?
0
 
LVL 7

Accepted Solution

by:
Lee Ingalls earned 500 total points
ID: 39892273
On the Crystal Menu -> Database -> Select Distinct Records
See the attached screenshot.
Use-Distinct.jpg
0
 

Author Closing Comment

by:jajen
ID: 39892417
Thank you for giving me all the detail necessary to answer my question.  Sometimes it is the simple ones that can cause the brain to overthink the obvious.
0
 
LVL 7

Expert Comment

by:Lee Ingalls
ID: 39892457
You're welcome. EE is a great resource... mlmcc and others are Crystal masters who've helped me immensely.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now