Add sum of columns to chart SSRS

Question

I have searched all over and cannot seem to find a definitive answer for this issue! I have a simple chat here grouped on the 5 categories below detailing the Sums of their SqFt.

I want to add a Total Column to the graph ~(Total = 11M sqft). Can this only be done in SQL? It is a bit puzzling for me to do this because the query already sums the sqft for each row (as a nested query). I would need to Sum(sum(sqft)) in order to produce what I want, however, I dont believe this will work on the group level.

Sample Data set:

ID| Type| Sqft|

12| OF| 500

14| IN| 1294

99| OF| 12042

24| ME| 92043

15| IN| 13945

16| OW| 2650

Can this be done in the report builder?

Thanks!

Answer 1

You can add a Total row in your query by using GROUPING SETS operator. Once the total is in the dataset it is trivial to show the column in the chart.

Based on the data sample you posted you can use a similar query to the below:

SELECT
  CASE
    WHEN GROUPING_ID(Type) = 1 THEN 'TOTAL'
    ELSE Type
  END [Type],
  SUM(Sqft) Sqft,
  GROUPING_ID(Type) [Grouping]
FROM your_table
GROUP BY GROUPING SETS ((Type), ())

Check this Live Demo

If you are confused by the above query you can simply use the union operator to add a row to the end of your current dataset.

SELECT
  ID,
  [Type],
  Sqft
FROM your_table
UNION ALL
SELECT
  NULL,
  'Total',
  SUM(Sqft)
FROM your_table

Now just create your chart using the produced dataset.

Let me know if this helps.