A simple problem, but I can't work out the simple solution!
We have a custom report for a commercial shipment invoice. This uses either the item price OR the item cost, depending what value is available for the shipment, within the 'detail' query:
CASE WHEN (coitem_price=0) THEN coitem_unitcost
ELSE (coitem_price / 0.80)
END AS co_item_unitcomvalue,
The report displays the "co_item_unitcomvalue" for each line and the subtotal for all lines at the bottom. This gives us the commercial value of the goods
Freight charges are found from the 'Header' query, from the shiphead and are also displayed
How do I display the sum of the two values though? One is a column from the Head query, one is a internally calculated sub-total.
All I need is a 'grand total' for the invoice (line values + freight), ideally without a complex reworking of the original queries!