xTuple.com xTupleU Blog & News Customer Support

Summing two values for display

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!


The way I do this in many different reports is to add a sum of the detail calculation to the header or footer query so the value is available elsewhere in the report.

Thus, in your existing header query, you could just add:


CASE WHEN (coitem_price=0) THEN coitem_unitcost
        ELSE (coitem_price / 0.80)

       ) AS total_co_item_unitcomvalue

as a new column and use that anywhere you needed to include the calculated subtotal.

If the header query doesn't lend itself well to adding that column, if it doesn't already include coitem for instance, you could create a separate source query to just calculate that total.