How to sum SSRS Matrix Columns on Row Group

Question

I have a report that displays the total hours a person worked for each project they are assigned to, aggregated on month. I would like to sum the total hours for each person, for each month, in SSRS 2008 R2. So, for example, I have the follow data and would like to column totals like so:

                    January          February          March
Worker 1
    Project1           50               80               20
    Project2           30               10               60
    Project3           10               40                5
           Total:      90              130               80
Worker 2
    Project1           20               40               60
    Project2           10               30               10
    Porject3           30               10               50
           Total:      60               80              120

Everything I have tried so far either sums each column for all workers combined (summing by 'Month' column group) or sums all hours for each worker (Summing by 'Person' row group).

For completeness, my row groups are:

Person
  Project
    RowGroup

My Column groups are:

Month
  ColumnGroup

I know that I can create another query/data set to do this, but I was hoping that SSRS can do this fairly easily. It seems like a trivial problem and perhaps I am just missing something.

Any help would be great or perhaps this is something that SSRS can't do.

Disincline

Hi @Disincline - did you ever get this to work? I have the exact same issue driving me insane. I have even tried with multiple datasets but still cant get a total per project – Hone

Answer 1

If you are trying to add a Column containing totals, it is pretty simple just

  right click on cell --> Totals --> Column

As shown here

Abet

This adds a total for the column across all workers. I want to sub total each worker's hours per month, not combine the hours for Worker1 and Worker2 for each month. – Disincline

@Disincline For that you would simply Right click on the Person Group under Row Groups and Add Total After. – Abet

@MAli Summing on the Person row group sums all hours, for all projects, for all months, not for all projects for each month. :-( I appreciate the suggestions though. – Disincline

Answer 2

It looks like you have multiple groups attached to the total which means SSRS will create the total outside of the latest group similar to this (note the important bit being the lines on the left side) total within group

but a solution to this is to create another total of this total which will list itself outside of both groups.

total of both groups

after which you can delete the first total row

Leavenworth