Solved

Crystal 2008 Remove duplicate records from Report

Posted on 2014-02-27
7
5,255 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 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 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