Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Distinct Sum Post Reply Post New Topic
Author Message
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Topic: Distinct Sum
    Posted: 15 Mar 2011 at 4:29am
Trying to figure out a way to get the "ValueSumFormula" below. Basically it is a sum of the "Value" using distinct ID2 grouped by ID1. Do i make sense? But here's exactly what it needs to look like...
 
ID1       ID2      Value   ValueSumFormula
z          a          5          8
z          b          3          8
z          a          5          8
z          b          3          8

x          e          4          6
x          f           2          6
x          e          4          6
x          f           2          6
 
Thoughts?
Thanks.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 16 Mar 2011 at 3:30am
so why not put in groups for id1 and id2 then use sum({table.Value}, {table.ID2})?
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 16 Mar 2011 at 6:01am
because then I'd get the wrong ValueSumFormula...
 
ID1       ID2      Value   ValueSumFormula
z          a          5          10
z          a          5          10
 
z          b          3          6
z          b          3          6
 
x          e          4          8
x          e          4          8
 
x          f           2          4
x          f           2          4
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 16 Mar 2011 at 7:15am
ok, then you are going to need to write a formula that will do all the summing for you as well as keeping track of which values have already been added to the sum.
 
somethingl like:
shared stringvar seen;
shared numbervar seensum;
local stringvar this :="|"+{table.id2}+"|";
 
if instr(seen, this ) = 0 then(
  seensum := seensum + {table.Value};
  seen := seen + this;
);
 
"" //hid the result
 
 
you will need a reset and display formula as well
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 16 Mar 2011 at 11:59am
Ok I almost understand. Here's what I have:
 
shared stringvar seen;
shared numbervar seensum;
local stringvar this :="|"+ ToText({RENTALQTYADJUSTMENT.RENTALQTYADJUSTMENT_ID})+"|";
 
if instr(seen, this ) = 0 then(
  seensum := seensum + {RENTALQTYADJUSTMENT.QUANTITY};
  seen := seen + this;
);
 
What this returns is the:
 "|"+ ToText({RENTALQTYADJUSTMENT.RENTALQTYADJUSTMENT_ID})+"|";
 
...on exaclty the right fields to summaryize only once. I'm guessing now I need to create a summary field that only summarizes on those? .. but I can't figure out how to do that because a Running Rotal feld doesn't allow me to evaluate on other formulas.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 17 Mar 2011 at 3:06am
you can suppress the return value by adding "" after the snippet...CR returns whatever is the last value it sees.
 
formulas will do whatever you like, and any time...Running totals have their limitations, and their advantages.  Think of when your summary should reset, and you can add another shared variable to keep the summary value until it is time to display/reset.
 
HTH
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 17 Mar 2011 at 7:37am

But how do I make your formula above show a sum of the value field instead of showing the "|"+{table.id2}+"|" ?

I'm a little lost with it.
Thanks.
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 21 Mar 2011 at 9:07am
Ok so the below works but how do I get it to reset at change of ID1?...
 
shared stringvar seen;
shared numbervar seensum;
local stringvar this :="|"+ ToText({RENTALQTYADJUSTMENT.RENTALQTYADJUSTMENT_ID})+"|";
    if instr(seen, this ) = 0
        then(
            seen := seen + this;
            seensum := seensum + {RENTALQTYADJUSTMENT.QUANTITY}
            )
Thank you.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 22 Mar 2011 at 3:10am

make a formula like

shared numbervar seensum :=0;
shared stringvar seen := "";
 
"" //hides the reset
 
place the formula in the group header for ID1...then the variables are reset.
 
HTH
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 22 Mar 2011 at 5:25am
Got it. Thanks for all your help! I think I'm done with this one :-)
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.