Solved

Crystal 2008 Remove duplicate records from Report

Posted on 2014-02-27
7
5,316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 8

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 8

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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

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 8

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 8

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

737 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