Adding Group Grand Totals in SSRS

Question

I have a matrix that shows the figures for new business and renewals of gym membership for different locations.

The rows are grouped by the following: Location, Trainer, Membership Type, Business Type (New Business/Renewal).

I would like to show a grand total for the groups Membership Type and Business Type but I can't find a way to get it done.

I have tried clicking on the Row Groups and clicking on Add Total, but it is only creating SubTotals inside the other Groups.

My report looks like below

And this is what I want to achieve:

Alan Schofield Alan Schofield · Accepted Answer · 2019-07-24T13:43:09

By far the easiest way to do this is to add it to your dataset. Set the Location (and probably Trainer) to 'Total', you could also include a sort column to help make sure 'Total' is last, and then append it to your data something like.

SELECT 1, AS SortBy, Location, Trainer, MembershipType, BusinessType, Month, Amount FROM myTable
UNION ALL
SELECT 2, AS SortBy, 'Total', 'Total', MembershipType, BusinessType, Month, SUM(Amount) AS Amount FROM myTable
GROUP BY MembershipType, BusinessType, Month

Your matrix should work as normal but for the Location group, sort it by SortBy and Location

Adding Group Grand Totals in SSRS

1 Answers