Summarize data that has already been grouped

Question

I have a data set that looks like this:

  User  |  Task  |  Time
--------|--------|--------
 User A | Task X |  100
 User A | Task Y |  200
 User A | Task Z |  300
 User B | Task X |  400
 User B | Task Y |  500
 User B | Task Z |  600
 User C | Task X |  700
 User C | Task Y |  800
 User C | Task Z |  900
 User D | Task X | 1000
 User D | Task Y | 1100
 user D | Task Z | 1200

When I do my initial grouping, the data looks like this:

       | Avg User | Avg Task X | Avg Task Y | Avg Task Z
  User |   Time   |    Time    |    Time    |    Time   
-------|----------|------------|------------|------------
User A |    200   |    100     |    200     |    300
User B |    500   |    400     |    500     |    600
User C |    800   |    700     |    800     |    900
User D |   1100   |   1000     |   1100     |   1200

I need it to look like this:

      | Avg User | Avg Task X | Avg Task Y | Avg Task Z
 User |   Time   |    Time    |    Time    |    Time   
------|----------|------------|------------|------------
 All  |    650   |    550     |    650     |    750

This is how I got those numbers:

 650 = (200+500+800+1100) / 4
 550 = (100+400+700+1000) / 4
 650 = (200+500+800+1100) / 4
 750 = (300+600+900+1200) / 4

In other words, I have a column group on Task and a row group on User. The problem is that I want the row group to get summarized an extra time.

At first glance I could just return the user's name back as 'All' and it would summarize but this doesn't actually give me the averages that I need. I need to first SUM the times by user, and then find the average per user. If I change the way the original data is shaped, my task groups will no longer work properly.

If I try to use a "Totals" row on my row group, it aggregates the ORIGINAL data and not the summarized/grouped data. That is rather disappointing because it is actually incorrect in my eyes.

Answer 1

The only way I was able to do this type of functionality is to was to use the Code section of the report. I would keep track of the group data I wanted to summarize in a global variable in that I would later output to the field that I wanted.

Here is a microsoft article to describe how to embed code into your report http://msdn.microsoft.com/en-us/library/ms159238.aspx

Here is a much more detailed way to solve your problem. Link