Report Design: Duplicate Records on Detail section

Print Page | Close Window

Duplicate Records on Detail section

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12986
Printed Date: 27 Apr 2024 at 7:43am

Topic: Duplicate Records on Detail section

Posted By: Yuval
Subject: Duplicate Records on Detail section
Date Posted: 21 Apr 2011 at 3:49am

I'm having some simple but annoying problem

The detail section shows me duplicate records

My Query returns 3 records (i'm using command) like

A 1 2

A 3 4

B 5 6

The grouping is done by the first field ('A' or 'B')

In the detail section there is:

GH A

A 1 2

A 3 4

GH B

B 5 6

Can somebody explain how to fix this issue ?

Thanks

Yuval.

Replies:

Posted By: zach18
Date Posted: 21 Apr 2011 at 4:51am

in CR 2008, you can go to the database tab on the top and select the option "select distinct records"

-------------
Using Crystal Report 2008
www.zachtech.us

Posted By: Yuval
Date Posted: 21 Apr 2011 at 5:39am

Thanks

I didn't find this option "select distinct records"

I'm using Crystal Report Designer 2008 version 12.0.0.683

There is another option to control this ?

Posted By: Yuval
Date Posted: 21 Apr 2011 at 5:57am

Hi Again

I found the "select distinct records" , and it was checked already

So it didn't solve the problem

Posted By: hilfy
Date Posted: 21 Apr 2011 at 6:47am

Try adding another group on your second field, putting your data in the group header for that section and suppressing your details.

You're seeing duplicate data due to joins to multiple tables and somewhere in that there is more than one record which is causing your duplicates.

-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: Yuval
Date Posted: 21 Apr 2011 at 7:50am

I've added another group and still the detail section shows me duplicate data

How can i check my scripts ? i run my script on some sql tool and he doesn't return me duplicate records

Can it be connected to the link of the commands?

Posted By: hilfy
Date Posted: 21 Apr 2011 at 7:58am

Put the data in the group header, NOT the details! Suppress the details.

-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: Yuval
Date Posted: 24 Apr 2011 at 1:27am

Thanks a lot!

it works

i still didn't find out why i have duplicate records in the details section in the first place

Posted By: Yuval
Date Posted: 24 Apr 2011 at 7:31pm

Well , Another small issue that i couldn't resolve

I need to summarize one of the field in the details section ( now in the GH)

Do you idea how to do that ?(when i summarize the detail field it duplicate the result)

Thanks

Posted By: hilfy
Date Posted: 25 Apr 2011 at 5:37am

Create a formula something like this:

If PreviousIsNull({table.groupfield}) or {table.groupfield} <> Previous({table.groupfield}) the 1 else 0

You can sum this field to get a distinct count. If you need to sum a field, replace the "1" with the field name.

-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: radfahrer
Date Posted: 23 May 2011 at 9:28am

I found another solution that prevents the repeating issue that Yuval was seeing. You need to check the "Perform Grouping on Server" in the Database Expert, Database Options, Advanced Options section. I was getting my data from a fairly complex stored procedure, it returned a very nice and clean table in SQL Query Analyzer, but Crystal was doing some very strange stuff and seemed to be rerunning the SP for each record it returned. It produced an exponential growth of records for each record I referenced in my report.

Just for Google searches to hit on this post. My Google query was Crystal Reports Details section repeating. Took a while to get to this forum eventually. I hope this helps other banging their heads with this issue.

Print Page | Close Window