Total a value in Reports
I know there will be someone clever who has done this. I have a report that lists Labour Cost, Part Cost and Total cost. These are calculated using a series of custom rules. When I run the report for the month there are more than 100 records but I would like to put a total for each of those columns at the bottom of the report.
Answers (1)
so let's say your original query looks like this:
SELECT
Item, Labour_Cost, Part_Cost, Total_cost
FROM
TABLE1
WHERE
Item LIKE 'ABC%'
in order to add a totaling row we have to build a new (one-entry) table and then "UNION" the two tables together. So you would add the following at the bottom of the query:
UNION
SELECT
'Total' AS Item,
SUM(Labour_Cost) AS Labour_Cost,
SUM(Part_Cost) AS Part_Cost,
SUM(Total_Cost) AS Total_Cost
FROM
TABLE1
WHERE
Item LIKE 'ABC%'
GROUP BY ''
