SSRS: Table of group aggregates with grand total

Question

I am using SSRS 2014. My end goal is to get a table that looks like the following:

Project Status     # of Projects    Oldest Project    Avg Project Age
Status 1                 27               82                16
Status 2                  9               29                 6
Status 3                 13              112                25
Status 4                 33               68                16
Status 5                  1               63                63
Status 6                  1               27                27
Grand Total              84              112                17

A table like this, including the grand totals, can be done in Excel using pivot tables (this is how the data was originally retrieved...manually...)

If I have the same source data in a table where my columns include projectStatus and projectAge, I could generate a query to get all but the grand totals in SQL:

SELECT projectStatus, COUNT(projectStatus), MAX(projectAge), AVG(projectAge)
FROM projects
GROUP BY projectStatus

If I wanted to add grand totals to that, I could do a UNION with the following query:

SELECT 'Grand Total', COUNT(projectStatus), MAX(projectAge), AVG(projectAge)
FROM projects

Note that the grand totals are calculated off of the entire dataset and not just the grouped data - this mostly applies to the average, because it just happens to work out for the COUNT and MAX columns.

Now, if I am trying to generate this above table in SSRS, it is easy to generate the above table sans grand totals using the former query. However, now how do I add grand totals?

I could instead try a different approach where I use a more generic dataset query like:

SELECT projectStatus AS statusCategory, projectAge AS daysInCurrentStatus FROM projects

And then use aggregate functions in my table and have SSRS do the aggregation itself and provide grand totals on the raw data. I get a new problem now. What is the correct way to set my table up? I am calling grouping by [statusCategory] as "Group1", using scoped aggregate functions like so:

And it correctly generates the grand totals, but now I have a new problem: it is showing the average for each row instead of just one cell per group/scope. Is this something SSRS is meant to do, and if so, how can I do it?

...

JamieD77 JamieD77 · Accepted Answer · 2016-10-17T21:24:19

You need to mess around a little to get right layout, but you basically need a group header or footer to show the Group aggregates.

if you right click on the lines next to [statusCategory] and select Insert Row > Outside Group - Above it will create a group header for you. Then just add your aggregate expressions in the correct columns like =Count(Fields!projectStatus.Value) then you can just set Row Visibilty on the details row to hide if you dont want to see it.

you should end up with a layout similar to this.

with the middle row (detail) visibility set to false

SSRS: Table of group aggregates with grand total

2 Answers