Solved

Crystal 2008 Remove duplicate records from Report

Posted on 2014-02-27
7
5,224 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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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. …
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 …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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