SSRS - Manager with employees utilized by other managers

Question

I am trying to create an Employee Utilization report in SSRS (Visual Studio 2010, SQL Server 2012)

We have employees who work for multiple program managers. I need a report that shows program managers, the employees who work for them, and the utilization of each employee by each manager they work for. This will allow us to identify which employees do not have their utilization distributed correctly.

The dataset query returns data such as:

EmpID   Emp          Mgr          Util  Total Util
1234    Doe, John    Lundy, Sal   100    100
2345    Ward, Joe    Lundy, Sal   40     110
3456    Kline, Rob   Smith, Bob   100    100
4567    Abbott, Fred Smith Bob    100    100    
2345    Ward, Joe    Smith, Bob   70     110

The results, when grouped by manager, should look like this (with the plus indicating the expand toggle):

Mgr           Emp         UtilMgr   Util    Total Util
Lundy, Sal              
              +Doe, John                    100
              +Ward, Joe                    110
Smith, Bob              
              +Kline, Rob                   100
              +Abbott, Fred                 100
              +Ward, Joe                    110

When an employee is expanded, the detail will show the employee utilization broken down by the different program managers they do work for:

Mgr         Emp            UtilMgr        Util      Total Util
Lundy, Sal              
            -Doe, John                              100
                           Lundy, Sal      100      
            -Ward, Joe                              110
                           Lundy, Sal       40  
                           Smith, Bob       70  
Smith, Bob              
            +Kline, Rob                             100
            +Abbott, Fred                           100
            +Ward, Joe                              110

I have tried all kinds of grouping and property combinations (parents, children, display detail) in the report, but cannot get the results to be grouped the way I want it. I tried adding another manager field (aliased as UtilMgr) to the query, but still couldn't get the report correct.

I'm looking for the best way to handle this. It seems like the solution is to have the Program Manager as a parent to the Employee, and then have a second group where the Employee is a parent to the Manager, but I'm not sure how to implement that.
Do I need to do something different with the query? Do I need to use a subreport or a second matrix, nested in the first? Is there some way to create overlapping parent/child groups?

I have only been working with SSRS for about 2 weeks, so I hope all the terminology I've used is clear and correct. Thanks in advance for any help!

Answer 1

Can you change your query?

In order to do what you want, your dataset needs to look like this:

EmpID   Emp          Mgr          UtilMgr     Util  Total Util
1234    Doe, John    Lundy, Sal   Lundy, Sal  100    100
2345    Ward, Joe    Lundy, Sal   Lundy, Sal  40     110
2345    Ward, Joe    Lundy, Sal   Smith, Bob  70     110
3456    Kline, Rob   Smith, Bob   Smith, Bob  100    100
4567    Abbott, Fred Smith, Bob   Smith, Bob  100    100    
2345    Ward, Joe    Smith, Bob   Smith, Bob  70     110
2345    Ward, Joe    Smith, Bob   Lundy, Sal  40     110