Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Sum totals consistently off Post Reply Post New Topic
Author Message
swoops
Newbie
Newbie


Joined: 06 Jun 2007
Online Status: Offline
Posts: 15
Quote swoops Replybullet Topic: Sum totals consistently off
    Posted: 22 Jun 2007 at 6:36am

Hi there,

I am trying to sum payment amounts on my crystal report, and the output is consistently the sum of 20x each payment amount.

How do I fix this?

The report has several group bys, and I'm pretty sure the links between tables are correct. The payments are placed in a header section, as they won't appear in the details section for some other reason.
 
ahh! Your help will be much appreciated, I need to get this done!
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 22 Jun 2007 at 10:19am
A lot of times when I see my data being off by a certain multiple, then I look at the underlying data in the tables. It is often the case where you are thinking it is a one to one relationship and it is really a one to many relationship. In fact, this just happened to a co-worker yesterday in her report and she couldn't figure it out. So I had her do a SELECT DISTINCT from the child table and this cleared up the problem.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
swoops
Newbie
Newbie


Joined: 06 Jun 2007
Online Status: Offline
Posts: 15
Quote swoops Replybullet Posted: 22 Jun 2007 at 11:49am

Thanks Brian. I eliminated a few tables being used by creating a subreport; this reduced the sum in the main report to the sum of 2x each payment amount (as opposed to 20x before). I also clicked on Select Distinct under the Database dropdown, so that the SQl reads SELECT DISTINCT (list of fields) FROM (bunch of tables) ORDER BY (my 10 groupings).

This didn't seem to affect the output.

Is there something else I should try?

Thanks again!

IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 22 Jun 2007 at 12:45pm
You need to put the SQL directly into your database and see what the results are. Since you were able to reduce it from 20 down to 2, then that implies that this is still a join issue. I would plug the SQL into the database and get it working there before doing anything else. Also, I see that you have 10 grouping fields. That's a lot of fields to group on and I can see where it would be easy to have a dup in there causing problems and you totally miss it b/c of all the grouping. I see this problem a lot and its almost always an issue of fixing the SQL statement to get clean data. Do it in the database with the raw SQL statement so you don't have CR mucking things up.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
swoops
Newbie
Newbie


Joined: 06 Jun 2007
Online Status: Offline
Posts: 15
Quote swoops Replybullet Posted: 25 Jun 2007 at 3:29pm

Thanks for your help Brian! I was able to fix the error.

IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 25 Jun 2007 at 3:39pm
Glad to help! Links are always appreciated. 
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
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.031 seconds.