Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Summarize Duplicate records Totals Post Reply Post New Topic
Author Message
NaasMarais
Newbie
Newbie


Joined: 01 Aug 2011
Online Status: Offline
Posts: 4
Quote NaasMarais Replybullet Topic: Summarize Duplicate records Totals
    Posted: 01 Aug 2011 at 7:23am
Hi All,

I am new to this whole Crystal report thing and have it the wall. I have a join issue in mt report with produced duplicate records but that has been sorted by the code:

{OpenItem.Ref}=previous({OpenItem.Ref})

Which has removed all the duplicate for one client. no i have to sum the total of the different invoices but the sum includes all the duplicate records as well.

Is there a way to sum the total but not including the duplicate records?

Thanks in advance for you help
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 01 Aug 2011 at 7:38am
you can use the running total feature to calculate the sum of the field and evaluate on change of {OpenItem.Ref}
|< /\ '][' ( )
IP IP Logged
NaasMarais
Newbie
Newbie


Joined: 01 Aug 2011
Online Status: Offline
Posts: 4
Quote NaasMarais Replybullet Posted: 01 Aug 2011 at 8:35am
Hi,

Tried your suggestion but picked up if i hide duplicate recordes it hides so invoices & Credit note it is not suppose to and with the sum of total is excludes these values

How can I determine why the DB is duplicating records for one clients?
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 01 Aug 2011 at 10:29am
You need to create groups, one by client
2nd by invoice number
place your information per invoice on the group header
create manual running totals to calculate the summaries this will avoid the duplicates.

MANUAL RUNNING TOTALS

 

RESET

The reset formula is placed in a group header report header to reset the summary to zero for each unique record it groups by.

 

whileprintingrecords;

                                                            Numbervar  X := 0;

 

CALCULATION

The calculation is placed adjacent to the field or formula that is being calculated.

(if there are duplicate values; create a group on the field that is being calculated on. If there are not duplicate records, the detail section is used.

 

whileprintingrecords;

                                                            Numbervar  X := x + {field}; ( or formula)

 

DISPLAY

The display is the sum of what is being calculated. This is placed in a group, page or report footer. (generally placed in the group footer of the group header where the reset is placed.)

whileprintingrecords;

                                                            Numbervar  X;

                                                            X

 
sharona
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 01 Aug 2011 at 10:30am
you will have an issue with duplicates based upon your joins.
what is your datasource?
sharona
IP IP Logged
NaasMarais
Newbie
Newbie


Joined: 01 Aug 2011
Online Status: Offline
Posts: 4
Quote NaasMarais Replybullet Posted: 02 Aug 2011 at 3:16am
hi
 
i use an odbc conncetion to the database. you see the report i am creating is a age analysis report and all the invoice has to be under the respective client. they dont want a page just contain one invoice number.
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 02 Aug 2011 at 11:49am
your connecting to tables then?
what are your joins on the tables?
what data is being duplicated?
if you group by client then invoice and dont page break on invoice number
it should display all your invoices for that client, unless you have a date range which will limit the invoices by date
sharona
IP IP Logged
NaasMarais
Newbie
Newbie


Joined: 01 Aug 2011
Online Status: Offline
Posts: 4
Quote NaasMarais Replybullet Posted: 03 Aug 2011 at 2:52am

What I Can tell you off hand is that there are 4 databases and they have a inner join to them. There are fields in all of them that match up with the same info working on the customercode field in the tables.

The one client in the delivery database has multiple delivery addresses which it seems to me is causing the duplication of the records.
 
If i group by client and then by invoice number with a line break will it still show as
 
ACT001 - Being the client
INV001
INV002
INV003
 
ADG001 etc?
 
Will get all the info for you tonight as it is on my home computer.
 
 
 
 
 
 
 


Edited by NaasMarais - 03 Aug 2011 at 3:00am
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 03 Aug 2011 at 5:04am
the multiple addresses can cause you the dup issue.
dont you want your report to be grouped by client then invoice number.
do you need the muptiple addresses?
sharona
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.027 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.