Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Group percentage error Post Reply Post New Topic
Author Message
nigel
Newbie
Newbie


Joined: 11 Mar 2010
Online Status: Offline
Posts: 4
Quote nigel Replybullet Topic: Group percentage error
     Posted: 11 Mar 2010 at 9:29am

This may be simple for you guys but it's driving me crazy

I need to display, in a group, total percentage margin for a list of products.

It's accurate at detail level, but errors at group level.

Here's the list:


Desc:      Units:     Unit Cost:    Total Cost:  Total Selling: Margin: Margin %

Product 1   8.00       .413             3.30            6.72             3.42     50.83%

Product 2   6.00       .413             2.48            5.10             2.62     51.41%

Product 3 12.00       .413             4.96            7.32             2.36     32.30%  

Product 4   6.00       .413             2.48            4.92             2.44     49.63%

Fine so far, but when I group these items , summarize the totals and add formula for margin % as

sum({@frmMargin}) % sum({@frmSales})

this is what I get:

  Total Selling:   Margin:   Margin %
    
       24.06          10.84      50.83% 

It should be 45.11%

I've tried using the summarize button with ave. of margin %, but this is not accurate either.

What am I doing wrong?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Mar 2010 at 10:55am
What are your @frmMargin and @frmSales formulas? It appears that they must be altering your data in some way  or you would just be using fields from the table...
Do you have any duplicate data rows in your set?


Edited by DBlank - 11 Mar 2010 at 10:55am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Mar 2010 at 1:07pm
Also, did you suppress any groups?
IP IP Logged
nigel
Newbie
Newbie


Joined: 11 Mar 2010
Online Status: Offline
Posts: 4
Quote nigel Replybullet Posted: 11 Mar 2010 at 2:24pm
Thanks for the reply,
 
@frmsales is unit sales * unit price, and @frmmargin is @frmsales - total cost. There's nothing between the 'details' and this first grouping, and no duplicates.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Mar 2010 at 2:53pm
having a hard time teaisng this out so nothing is looking off to me but ...
A few things that come to mind here.
1. When I have a known error I always break my formulas into each piece and display them seperately to make sure I am getting what I expect from each piece first. So does the sum({@frmMargin}) display the correct amount and then does sum({@frmSales}) display the correct amount?
2. I see that one value has 3 decimal places. Could some rounding be causing you problems?
IP IP Logged
nigel
Newbie
Newbie


Joined: 11 Mar 2010
Online Status: Offline
Posts: 4
Quote nigel Replybullet Posted: 12 Mar 2010 at 2:24am
Ah ha -
 sum({@frmMargin}) and sum({@frmSales}) are totalling the whole report and not just the group. Should I be using running totals?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Mar 2010 at 4:49am
That would explain it.
I assumed you were looking for report totals.
You can use the SUM(field, groupfield) to get SUMS at a group level.
IP IP Logged
nigel
Newbie
Newbie


Joined: 11 Mar 2010
Online Status: Offline
Posts: 4
Quote nigel Replybullet Posted: 12 Mar 2010 at 5:33am
of course!
 
easy when you know how,
 
many thanks for that
IP IP Logged
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