Sum formula displaying wrong results in Crystal 10

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
jokertAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
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
 
mlmccCommented:
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
 
James0628Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
jokertAuthor Commented:
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
 
mlmccCommented:
Is this done or do you still have a problem?

mlmcc
0
 
jokertAuthor Commented:
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
 
jokertAuthor Commented:
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
 
mlmccCommented:
GroupField needs to be the field you are grouping the report on

mlmcc
0
 
jokertAuthor Commented:
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
 
James0628Commented:
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
 
mlmccCommented:
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
 
jokertAuthor Commented:
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
 
mlmccCommented:
The sum is showing for the entire recordset or you are getting all the data on the report?

mlmcc
0
 
jokertAuthor Commented:
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
 
mlmccCommented:
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
 
James0628Commented:
> 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
 
James0628Commented:
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
 
jokertAuthor Commented:
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
 
jokertAuthor Commented:
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
 
James0628Commented:
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
 
jokertAuthor Commented:
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
 
James0628Commented:
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
 
jokertAuthor Commented:
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
 
mlmccConnect With a Mentor Commented:
How is it suppressed?

Formulas in a suppressed section don't get evaluated.

mlmcc
0
 
jokertAuthor Commented:
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
 
James0628Commented:
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
 
jokertAuthor Commented:
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
 
James0628Commented:
> 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
 
jokertAuthor Commented:
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
 
James0628Commented:
> 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
 
jokertAuthor Commented:
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
 
jokertAuthor Commented:
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
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.