SSRS Grouping within Totals

Question

If I have 2 row groups; year and status, is it possible to show totals for all years grouped by status?

Year  Status Value
2014  Open   100
      Closed 200
2015  Open   200
      Closed 300
Total Open   300
      Closed 500

Until now I have always achieved this with a "workaround" (creating 2 rows in the total group and using hardcoded formulae =SUM(IIF(Status="Open",Value,Nothing) etc but I would like to be able to do this "cleverly".

Try to specify outer scope for your SUM function (better to point "Year" or "Body" scope, don't remember exact name). — grafgenerator, Nov 09 '16 at 11:06

score 1 · Answer 1 · answered Nov 09 '16 at 13:28

If you don't want to hardcode the status you can embed another tablix into your current one. Just drag and drop a tablix item to the cell where you want to get the total per status group.

Add the total row outside the Year and Status groups. Drag and drop the tablix to that row in the cell you want to show the total.

In the Total tablix add Status as group and use =SUM(Fields!Value.Value) to produce the total.

It should produce:

Let me know if this helps.

SSRS Grouping within Totals

1 Answers1