Sum totals consistently off
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=906
Printed Date: 28 Apr 2024 at 3:27pm
Topic: Sum totals consistently off
Posted By: swoops
Subject: Sum totals consistently off
Date 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!
|
Replies:
Posted By: BrianBischof
Date 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> |
Posted By: swoops
Date 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! |
Posted By: BrianBischof
Date 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> |
Posted By: swoops
Date Posted: 25 Jun 2007 at 3:29pm
|
Thanks for your help Brian! I was able to fix the error. |
Posted By: BrianBischof
Date 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> |