Solved

Crystal 2008 Remove duplicate records from Report

Posted on 2014-02-27
7
5,111 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

11 Experts available now in Live!

Get 1:1 Help Now