sum of a sum

[Ofina]

I’m working on an invoice log of sorts. Our invoice tables are header information and line item information.

On our invoices, we have an extra freight amount that usually shows up. Most customers are fine with it being in the totals section of the invoice, thus in the header table. But there is one customer who prefers it as a line item, thus in the line item table.

I have been able to add these two together to report the amount in one column of my invoice log, using the following formulae:
line item "door del": if {invoice_line.item_desc}='Door Delivery Charges' then invoice_line.unit_price}
Total: {invoice_hdr.freight}+Sum ({@door del}, {invoice_hdr.invoice_no})

And that's working just fine. But, now I am trying to get a grand total of this column down at the bottom. I would have thought that
Sum ({invoice_hdr.freight}) + Sum ({@door del})
would do the trick, but it is way inflated. Because of the join of the header with the lines, the freight field is showing up for every line in the result set. So when it sums up the whole thing, each freight amount is multiplied by the number of items on the invoice and then added together.

How can I get this grand total to be right?

 

[lbass]

Use a variable to accumulate the group summaries. Create two formulas:

//{@accum} to be placed in the group section where the correct group summary displays:
whileprintingrecords;
currencyvar x := x + {invoice_hdr.freight}+Sum ({@door del}, {invoice_hdr.invoice_no});

//{@display} to be placed in the report footer:
whileprintingrecords;
currencyvar x;

If your fields are numbers instead of currencies, replace "currencyvar" in both places with "numbervar".

-LB

 

[Ofina]

Thank you so much! That worked like a charm.

I can see I still have a lot to learn about CR formulae.

 

[Ofina]

I just tried to create another one on the same report. This is to replace something I was using a subreport for. But, creating this second one caused an error to appear in the first one. I tried changing the variable to y, I tried z. But, they both caused an error in the sum part of the first one.

whileprintingrecords;
currencyvar y := y + {invoice_hdr.total_amount}

What's wrong with that one?

 

[lbass]

A formula like this would have no impact on another formula, unless the same variables were referenced. What was the error message? What was the subreport being used for? How does the subreport relate to the first formula?

-LB