CrossTab Query Column Total

Question

I am running a report from a crosstab query. Is there a way to total the column amounts in the report?

Example:
FY2020 FY2021 FY2022
Location 1 5 4 3
Location 2 6 7 8
Location 3 5 3 9
Total 16 14 20

Answer 1

Just as you would with a conventional report, you can place unbound text boxes in the report footer, each with an expression calling the SUM operator as its ControlSource property, e.g. =SUM([FY2020]) for the first column, and so on for the other columns. Alternatively you can aggregate the values by means of another crosstab query grouped by TRUE (or any constant for that matter) to return the grand totals. The following is an example:

TRANSFORM NZ(SUM(Amount),0)
SELECT SUM(Amount*IIF(salestype IN ("Cash","Credit"),1,0)) AS TotalSales
FROM Sales
GROUP BY TRUE
PIVOT salestype IN ("Cash","Complimentary","Credit");

You can then include a subreport based on the query in the original report.