Print Page | Close Window

Distinct Sum

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=12615
Printed Date: 28 Apr 2024 at 10:04pm


Topic: Distinct Sum
Posted By: marz
Subject: Distinct Sum
Date 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.


Replies:
Posted By: lockwelle
Date Posted: 16 Mar 2011 at 3:30am
so why not put in groups for id1 and id2 then use sum({table.Value}, {table.ID2})?

Posted By: marz
Date 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

Posted By: lockwelle
Date 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

Posted By: marz
Date 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.

Posted By: lockwelle
Date 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

Posted By: marz
Date 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.

Posted By: marz
Date 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.

Posted By: lockwelle
Date 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

Posted By: marz
Date Posted: 22 Mar 2011 at 5:25am
Got it. Thanks for all your help! I think I'm done with this one :-)


Print Page | Close Window