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

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

I have then created a formula;

formula = Sum ({SO_InvoiceWrk.QuantitySh

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.QuantityShip

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

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.Quantit

''

In the group header

WhilePrintingRecords;

Global NumberVar PageCount;

PageCount := 0;

''

In the page footer to display

WhilePrintingRecords;

Global NumberVar PageCount;

PageCount

mlmcc

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

formula = Sum ({SO_InvoiceWrk.QuantitySh

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

WhilePrintingRecords;

Global NumberVar PageCount;

PageCount := Sum({SO_InvoiceWrk.Quantit

''

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

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?

I used the line; formula = Sum ({SO_InvoiceWrk.QuantitySh

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

WhilePrintingRecords;

Global NumberVar PageCount;

PageCount := Sum({SO_InvoiceWrk.Quantit

''

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

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

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.QuantitySh

> 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

mlmcc

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.Hea

In Page Footer;

WhilePrintingRecords;

Global NumberVar PageCount;

PageCount'

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

-jokert

I assumed that PageCount := Sum({@Total Line Weight},{SO_InvoiceWrk.Hea

Do you know why it isnt?

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

> 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.HeaderSeqN

> 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({@InvoiceNoL

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({@InvoiceNoL

0

else

{SO_InvoiceWrk.ItemWeight}

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.Head

James

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({@InvoiceNoL

PageCount := PageCount + {@Total Line Weight};

""

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

James

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

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

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({@InvoiceNoL

PageCount := PageCount + {@Total Line Weight};

""

James

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

Sum({SO_InvoiceWrk.Quantit

Or should I just focus on the new suppression formula?

Thank you!

-jokert

error.jpg

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({@InvoiceNoL

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

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

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

In the details section there is a formula;

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

PreviousValue({@InvoiceNoL

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

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

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

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({@InvoiceNoL

James

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?

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

The formula;

WhilePrintingRecords;

Global NumberVar PageCount;

if not ({SO_InvoiceWrk.ItemType} in [ "", "4" ] or

(not OnFirstRecord and Previous({@InvoiceNoLineNo

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

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

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!

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.

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