?
Solved

Sum formula displaying wrong results in Crystal 10

Posted on 2009-04-21
35
Medium Priority
?
3,137 Views
Last Modified: 2012-06-27
I am creating a comercial invoice that has a "details a" area that lists the parts ordered along with the quantity.

I have then created a formula;
formula = Sum ({SO_InvoiceWrk.QuantityShipped})

in order to calculate the total number of items shipped in an invoice.  I have placed this field in the Page footer.

The SO_InvoiceWrk.QuantityShipped is a Database Field.  The numbers in the Quantity field are accurate.  On 3 reports the qty is showing;
1
1,1
1,2,1

Those numbers are accurate based on the quantity shipped.  However the sum field which I have created is respondig with;

1
3
10

Can anyone tell me why this is not adding correctly?

I have changed the extension of the attached report from .rpt to .jpg to allow it to be uploaded.  In the attachment the field in question is located at the bottem under NET WEIGHT IN LBS.  I have changed the forumla to sum the quantity instead of what I am really after which is Net Weight because the quantity under quantity shipped is smaller.
SO-Invoice2.jpg
0
Comment
Question by:jokert
  • 15
  • 11
  • 7
33 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 24200496
Sum as you are using it calculates the sum for the entire recordset.

I assume you have this grouped by order number or some field.
Use formula in the group footer

WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := Sum({SO_InvoiceWrk.QuantityShipped},{GroupField});
''

In the group header
WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := 0;
''

In the page footer to display
WhilePrintingRecords;
Global NumberVar PageCount;
PageCount

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 24201915
As mlmcc said, what you're doing there is getting the total for the whole report.

 If you want the total for each HeaderSeqNo, change your formula to:

formula = Sum ({SO_InvoiceWrk.QuantityShipped}, {SO_InvoiceWrk.HeaderSeqNo})


 If a HeaderSeqNo could be more than one page long and you want to get the total for each page, that would be more complicated.

 James
0
 

Author Comment

by:jokert
ID: 24208844
Okay so I am getting an error using the;

WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := Sum({SO_InvoiceWrk.QuantityShipped},{GroupField});
''

It is saying that everything after records; is not part of teh formula.  I am using Crystal REports 10.

I had assumed that using the condition in the formula such as the invoice number would limiit the sum to only the records for which this applied.

I think I was wrong because after more invoices were created the sum started adding wrong again.

:)

-jokert
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
LVL 101

Expert Comment

by:mlmcc
ID: 24209653
Is this done or do you still have a problem?

mlmcc
0
 

Author Comment

by:jokert
ID: 24210517
Well I am getting an error using that formula.

I assumed I would be able to elliminate the problem by using the condition based on the invoice but that did not work out.
Should I start a new question?
0
 

Author Comment

by:jokert
ID: 24210587
Okay as this isnt working for me I think we need to reopen it!

I used the line; formula = Sum ({SO_InvoiceWrk.QuantityShipped}, {SO_InvoiceWrk.HeaderSeqNo}) and it worked....until other records were created.

I realize that I am pulling from other records.  So when I attempted to use;

WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := Sum({SO_InvoiceWrk.QuantityShipped},{GroupField});
''

I get an error that the "everything after records; is not part of the formula.  I suspect this is the path I need to take but need some additional guidance on how to get there.

-jokert
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24210628
GroupField needs to be the field you are grouping the report on

mlmcc
0
 

Author Comment

by:jokert
ID: 24214847
I guess I am operating on some assumtions which I shouldnt be doing.

I assume that I am printing a report as determined by our accounting software.  Since I am printing an invoice I assume that the group would be the invoice number.  Is this incorrect?

I am recieving the attached error when I attempt to make the formula described in mlmcc's first responce.

-jokert
error.jpg
0
 
LVL 35

Expert Comment

by:James0628
ID: 24215201
CR has two formula syntaxes, "Basic" and "Crystal".  They are _not_ compatible.  The formula you originally posted (formula = so-and-so) is Basic syntax.  The formulas that mlmcc posted are Crystal syntax.

 In the formula editor, there should be a dropdown box at the top that lets you choose the formula syntax.  Makes sure it's set to Crystal.  Since you were using Basic syntax before, my guess is that CR is still set to use Basic, and that's why you're getting the error.


 > I had assumed that using the condition in the formula such as the invoice
 > number would limiit the sum to only the records for which this applied.

 Sum ({SO_InvoiceWrk.QuantityShipped}, {SO_InvoiceWrk.HeaderSeqNo}) should give you the total of QuantityShipped for the current HeaderSeqNo.  It sounds reasonable, but it depends on your data, where you use the formula, etc.


 > Since I am printing an invoice I assume that the group
 > would be the invoice number.  Is this incorrect?

 Well, of course, there's no way for us to know for sure, but the report that you posted is grouped on HeaderSeqNo, so I assumed that it was either an invoice number or some other kind of counter that uniquely identified the invoices.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24215455
It probably makes sense for the group to be the invoice number.  There may be other groups or you may be running this in a way that filters to a single invoice so there is no need to group on invoice number

mlmcc
0
 

Author Comment

by:jokert
ID: 24217624
Okay.

Yes one of the problems was the syntax I was using.  Changing that does allow me to use these formulas;

In Group Header;
WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := 0;

In Group Footer;
WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := Sum({@Total Line Weight},{SO_InvoiceWrk.HeaderSeqNo});

In Page Footer;
WhilePrintingRecords;
Global NumberVar PageCount;
PageCount'

However I am still pulling data from the entire record set.  Any ideas why?

-jokert
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24219155
The sum is showing for the entire recordset or you are getting all the data on the report?

mlmcc
0
 

Author Comment

by:jokert
ID: 24220240
The sum is showing for the entire recordset instead of showing the total for the specific report I am running.

I assumed that PageCount := Sum({@Total Line Weight},{SO_InvoiceWrk.HeaderSeqNo}); would only pull the data provided for the totals on the specific invoice which I am printing.

Do you know why it isnt?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24221113
It will show the total for each group.

If you want to limit the data you need to setup a filter through the SELECT EXPERT
Open the report
Click REPORT --> SELECT EXPERT
Choose the Invoice field
Choose =
Select the one you want or if you have a parameter use that to get prompted when you run

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 24221186
> The sum is showing for the entire recordset instead of
 > showing the total for the specific report I am running.

 By "entire recordset" do you mean every invoice in your tables, or that what should be the total for one invoice is actually the total from every invoice on the report, or ???

 > I assumed that PageCount := Sum({@Total Line Weight},
 >{SO_InvoiceWrk.HeaderSeqNo}); would only pull the data
 > provided for the totals on the specific invoice which I
 > am printing.

 Generally, yes, but, more precisely, it is giving you the total for every record that the report reads in the current HeaderSeqNo group.

 In the report that you posted, you're using the following suppression formula on the main detail section:

formula = {SO_InvoiceWrk.ItemType} in array("", "4") or _
PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}


 If there are some records that are being suppressed, they will still be included by the CR summary functions, like Sum.

 If that's the problem, you could create a new formula like the following (Crystal syntax):

if {SO_InvoiceWrk.ItemType} in array("", "4") or
 PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo} then
  0
else
  {SO_InvoiceWrk.ItemWeight} * {SO_InvoiceWrk.QuantityShipped}


 That should only produce a value on the non-suppressed records.

 Then change the setting of PageCount in the formula in the group footer to:

PageCount := Sum({@new formula created above},{SO_InvoiceWrk.HeaderSeqNo});


 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 24221220
Oops.  Thought of this just after I posted.

 Re: My last suggestion about creating a new formula and using Sum () on that.

 It looks like you won't be able to use Sum on that formula, because it uses Previous ().  Instead, you'd have to use a formula at the detail level and add the value from each record to PageCount separately, rather than using Sum to get a group total.

 If you want to try this, move the formula that's in the group footer into the detail section instead, and change it to:

WhilePrintingRecords;
Global NumberVar PageCount;
if not ({SO_InvoiceWrk.ItemType} in array("", "4") or
 PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}) then
  PageCount := PageCount + {@Total Line Weight};

""


 The "" at the end is so that the formula does not produce any output on the report.

 James
0
 

Author Comment

by:jokert
ID: 24226234
I think maybe I am not expressing myself clearly :)

Or maybe I am and what I am after is more complicated than I think it should be.

I have attached an image of my completed document.  The topic in dicsussion here is the NET WEIGHT IN LBS box in the lower right..  I want this to be the sum of the Net Weight listed on the specific invoice which I am printing.  

In this example it should be summing all of the items in the Net Weight columns in the middle towards the top.  Net Weight = Item Weight * Quantity.  In this case it is 200.

So when my calculation at the bottem sums it up, in this instance it should be 200.  However it is pulling data from other invoices which have been created today.

Does this help?
CI.jpg
0
 

Author Comment

by:jokert
ID: 24229831
James,

To answer your question;

"By "entire recordset" do you mean every invoice in your tables, or that what should be the total for one invoice is actually the total from every invoice on the report, or ???"

     I mean it is pulling data from other invoices which have been created without being posted permantly.

I attempted to use the formula which references the array used in the details section.  I am getting an error which I ahve taken a screenshot for your reference.

Thank you both for any additional assistance you may provide!

-jokert
array-error.jpg
0
 
LVL 35

Expert Comment

by:James0628
ID: 24230562
Well, Sum({@Total Line Weight}, {SO_InvoiceWrk.HeaderSeqNo}) will give you the total of {@Total Line Weight} for the current value of HeaderSeqNo.  If HeaderSeqNo is unique for each invoice, then that can't possibly give you a total that includes other invoices.  If it's not unique for each invoice, I'd really expect to see more problems, as the report would be trying to combine multiple invoices in the same HeaderSeqNo group.

 At this point, my best guess is that the suppressed details are the problem.  The summary functions don't care that a record is suppressed, so Sum () would be including any suppressed records.  If that's the problem, hopefully the new formula will fix it.

 Sorry about the error.  It's a syntax thing again.  I copied your detail section suppression formula, which is in Basic syntax, and put it in a Crystal syntax formula.  array("", "4") is Basic syntax.  Replace that with [ "", "4" ]:

WhilePrintingRecords;
Global NumberVar PageCount;
if not ({SO_InvoiceWrk.ItemType} in [ "", "4" ] or
 PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}) then
  PageCount := PageCount + {@Total Line Weight};

""


 James
0
 

Author Comment

by:jokert
ID: 24245612
Thank you for your response!

However I am receiving the attached error in the new formula.

Sorry if I should have been able to figure this out.  I am really new to Crystal and didnt have any problems with any of my additional formulas or queries I made so its a bummer that I got dragged down by the last one.

Regarding Sum({@Total Line Weight}, {SO_InvoiceWrk.HeaderSeqNo})  I changed it to;
Sum({SO_InvoiceWrk.QuantityShipped}, {SO_InvoiceWrk.HeaderSeqNo}) in order to attempt to isolate the problem.  Using this I was able to determine that the quantities werent adding correctly here.  So an invoice for a single item was actually being counted twice by this formula.  Is there any way for me to see what data is being pulled in and where it is coming from??  

Or should I just focus on the new suppression formula?

Thank you!

-jokert
error.jpg
0
 
LVL 35

Expert Comment

by:James0628
ID: 24249413
Sorry.  Obviously I'm just not paying enough attention.  

 Change PreviousValue to Previous.  PreviousValue is Basic syntax.  Previous is Crystal syntax.  I either didn't notice PreviousValue, or did, and just assumed that it was an alternate form that would be accepted in Crystal syntax (it's not).


 As for items being counted twice by Sum:

 Sum will only include each record once.  Period.

 If an amount is being included twice, it must show up in two records.  If you're not seeing the figures twice on the report, something must be hiding them, and the only thing I could find in the report you posted was the detail section suppression formula, which is what we're trying to incorporate into this formula.  So, if we can get this formula working, it will hopefully give you the correct total.


 > Is there any way for me to see what data is being pulled in and where it is coming from??

 There is no way to specifically tell CR to show you what goes into a Sum.  You just have to look at the field in question in a detail section.

 In your case, I suspect the key would be to remove the conditional suppression on the detail section, since I think that's "hiding" some records from you.  Presumably what it's doing is correct and you don't want to see those records on the final report, but, for diagnostic purposes, you could change the report to show them.

 Go to the report design view, right-click in the "Details" area to the left of the detail section and select "Section Expert".  Click on the formula (X+2) button to the right of "Suppress" and you should see:

formula = {SO_InvoiceWrk.ItemType} in array("", "4") or _
PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}


 That says that if the ItemType is "" or "4", or the current value of @InvoiceNoLineNo = the previous value, suppress (don't show) the detail section (that line).

 If you like, you can put a ' (single-quote, below the double-quote) in front of the first line.  Both lines should turn green, indicating that they are comments now, meaning that CR will not evaluate them.  If you then save that formula and go back to the invoice, you'll probably see some lines that weren't there before.  To hide them again, go back to that suppression formula and remove the single-quote.

 James
0
 

Author Comment

by:jokert
ID: 24308043
Okay!

Removing the Suppression formula using the ' in front of the formula shows that it is repeating a line.  I am not sure why but now I understand why the sum calculations are out of sorts.

So Ive changed the Formula in the Group Footer field to;

WhilePrintingRecords;
Global NumberVar PageCount;
if not ({SO_InvoiceWrk.ItemType} in [ "", "4" ] or
 Previous({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}) then
  PageCount := PageCount + {@Total Line Weight};

But that is not counting the line that is being suppressed at all.  I have attached a screen shot of this.  I removed the "" from the formula so it would display what is happening.

So basically now it is counting everything but the line which is being supressed :)  Any idea why?

I really appreciate your time with this!!

-jokert
Supressing-the-Supresed.jpg
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 24308173
How is it suppressed?

Formulas in a suppressed section don't get evaluated.

mlmcc
0
 

Author Comment

by:jokert
ID: 24308543
Thank you for your reply!

In the details section there is a formula;

formula = {SO_InvoiceWrk.ItemType} in array("", "4") or _
PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}

When I remove that formula then it displays a line twice.  This is only applying to certain parts.  This is why the sum is not adding up correctly.  The sum formula I made was adding the supporessed item as well as the item which displays.

So I created the formula per James request, to teh details section;

WhilePrintingRecords;
Global NumberVar PageCount;
if not ({SO_InvoiceWrk.ItemType} in [ "", "4" ] or
 Previous({@InvoiceNoLineNo}) = {@InvoiceNoLineNo}) then
  PageCount := PageCount + {@Total Line Weight};

However as in the attached picture above it does not display on the field which I know has the two lines, one of which is being suppressed.

Does this make sense?

-jokert
0
 
LVL 35

Expert Comment

by:James0628
ID: 24311446
So, the formula is under the $1,985.00, but not producing any output?  That is odd.  It should either add {@Total Line Weight} to PageCount and show the result, or it should default to 0.  It should not be blank.

 I can think of two reasons that it might be blank.  One is that one of the fields being checked is null.  That can cause weird problems in formulas.  However, I think this might be something a bit "simpler".  The test tries to check a value from the previous record.  If this is the first record, there is no previous value to check.  I'm not sure what Previous () gives you in that case (because I normally include a check, as described below, to handle that situation).

 Try changing the formula to:

WhilePrintingRecords;
Global NumberVar PageCount;
if not ({SO_InvoiceWrk.ItemType} in [ "", "4" ] or
 (not OnFirstRecord and Previous({@InvoiceNoLineNo}) = {@InvoiceNoLineNo})) then
  PageCount := PageCount + {@Total Line Weight};


 If that works, I'd suggest changing the record suppression formula in the detail section to also handle the case when there is no previous record:

formula = {SO_InvoiceWrk.ItemType} in array("", "4") or _
(not OnFirstRecord and PreviousValue({@InvoiceNoLineNo}) = {@InvoiceNoLineNo})


 James
0
 

Author Comment

by:jokert
ID: 24317526
Outstanding!

That now displays the correct data!  It is summing the net weight for the specific lines and it is displying it on the first record!

So the last bit;

How do I sum these up?  In Page Footer I have;

WhilePrintingRecords;
Global NumberVar PageCount;
PageCount

But this isnt displaying the total of all of the variables.  What do you think?
0
 
LVL 35

Expert Comment

by:James0628
ID: 24321356
> But this isnt displaying the total of all of the variables.

 OK, but what _is_ it giving you?  0?  Some other figure?  Kind of hard to figure out what might be wrong when we don't know what results you're getting.  :-)


 A couple of observations/questions, which may or may not be relevant, since I don't know what you're getting.


 Going back to mlmcc's original formulas, you were going to reset the variable to 0 in the group header for the invoice and output the variable in the page footer.

 Does each invoice group take just one page?

 If an invoice could take more than one page, should this total be a subtotal for each page of the invoice, or a total for the pages so far, or ???

 If an invoice could take more than one page, do you have the report set to repeat the invoice group header on each page?

 James
0
 

Author Comment

by:jokert
ID: 24326295
All of the reports for this requirement will be on a single page yes :)

The formula;
WhilePrintingRecords;
Global NumberVar PageCount;
if not ({SO_InvoiceWrk.ItemType} in [ "", "4" ] or
 (not OnFirstRecord and Previous({@InvoiceNoLineNo}) = {@InvoiceNoLineNo})) then
  PageCount := PageCount + {@Total Line Weight};

When placed in the decription area is displaying the total line weight for the specific line it is displaying on.  This number is accurate!  It is showing the total line weight for each line item and it is not displaying the data from the suppressed line so this is perfect!

Now I just need to sum all of results that the above formula is giving me.

Does this help?  I think this is really close!  thank you both for your assistance!

-jokert
0
 
LVL 35

Expert Comment

by:James0628
ID: 24332433
> Now I just need to sum all of results that the above formula is giving me.

 That's the thing.  That formula should be doing the summing.  It's adding {@Total Line Weight} from the current line to a variable named PageCount, and without the "" at the end of that formula, the output you get is the result in PageCount, which should be a running total.  For example, if the {@Total Line Weight} on lines 1 and 2 was 10 and 20, the formula should give you 10 for the first line and 30 for the second.  And then in the page footer you have another formula to output PageCount, which should give you the total for the "page" (technically, it's the total for the group, but if there is one group per page, and one page per group, then it's the same thing).


 You supposedly put a formula like the following in the group header:

WhilePrintingRecords;
Global NumberVar PageCount;
PageCount := 0;


 Try putting that same formula in the report header.

 James
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1800 total points
ID: 24332469
Hmm.  Now that I think about it, you didn't say anything about the total that you're getting now.  Back on 04/23, you said that you had put a formula like the following in the page footer:

WhilePrintingRecords;
Global NumberVar PageCount;
PageCount


 Is that still there?  That will output the value in the PageCount variable, which should be the total.

 There's still the question of why the detail formula doesn't seem to be accumulating the total in PageCount, but once that is working, you'll need a formula like the one above to actually output the total.

 James
0
 

Author Comment

by:jokert
ID: 24368189
Bingo!!

That was it.  You are correct I just had to add that back into the report and its working!!

Wow you guys ahve both been great thank you so much for all of your assistance!!!

-jokert!
0
 

Author Closing Comment

by:jokert
ID: 31573064
Wow long road :)  I dont think I knew what I was getting into when I started this.  Both members were very helpful and I appreciate the time they spent on it!!
0
 
LVL 35

Expert Comment

by:James0628
ID: 24370525
You're welcome.  Glad I could help.

 James
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question