parameters cause duplicate records
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5448
Printed Date: 27 Apr 2024 at 11:57pm
Topic: parameters cause duplicate records
Posted By: jcwco
Subject: parameters cause duplicate records
Date Posted: 06 Feb 2009 at 12:11pm
|
I'm working on a Crystal report for our GoldMine database. I have it set up to display contact information for each record, and then in the details it displays the history records for each contact. it is all working fine, until I set up parameters with the selection expert.
If I use the "state" info from the contact record, it works fine. But if I use "ZIP code" info I get duplicate entries in of the history records.
The Contact records don't duplicate but some of the contacts have several duplicate history records when the report is run. This also happens if I use the age of the history records as a criteria for the select expert.
I have checked the records against our GoldMine database, and there are not actually duplicates there.
Any help appreciated.
matt
GoldMine 8.03, Crystal XI, and SQL Server 2005
|
Replies:
Posted By: hilfy
Date Posted: 06 Feb 2009 at 12:29pm
|
Go to the Database menu. Is "Select Distinct Records" greyed out? If not, turn it on.
If it's greyed out, go to the File menu and select Report Options. Is "Use Indexes or Server for Speed" greyed out? If not, turn it on and then go back to turn on Select Distinct Records.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: jcwco
Date Posted: 06 Feb 2009 at 12:53pm
|
Those options are not greyed out, but when I select them I get this message:
Database Connector Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server] The text data type cannot be selected as DISTINCT because it is not comparable. [Database Vendor Code: 421 ]'
after I click out of the warning, it comes up blank.
edit: Sorry, I forgot to say thanks for the suggestion!
|
Posted By: hilfy
Date Posted: 06 Feb 2009 at 1:15pm
|
In SQL Server, "Text" fields are long strings, which work like BLOBS and can't be used in Distinct queries. Ok, try this....
Is there any field in the History data that you can group on? If so, group on that and put the data in the History group header section(s) instead of in the Detail sections.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: jcwco
Date Posted: 06 Feb 2009 at 1:33pm
|
I'll try that. I'll let you know how it goes on Monday.
Thanks again.
Edit: Didn't take as long as I thought to try it out...worked like a charm. Now my results are correct no matter what I put in the select expert.
 |