I am designing a SSRS matrix report that shows a list of people and people on their team in categories. For example
|
CAT1 |CAT2 |CAT3
JOE |SAM |BILL | MIKE
ALAN |SAM |ADAM |
DOUG | |BILL |STEVE
This much works fine. But sometimes there are two people in a category for one person. So if there are 2 cat2 people for ALAN I need:
| |CAT1 |CAT2 |CAT3 |
|JOE |SAM |BILL | MIKE |
|ALAN |SAM |
ADAM | |
| | |MARK | |
|DOUG | |BILL |STEVE |
How do I get that textbox to expand so that it displays both values?
I am using Visual Studio 2015 and SQL Server 2016.
Here are some details that may make the question clearer. There are four tables involved. Here are the table and the relevant fields:
ManagerTable
ManagerID
ManagerName
WorkerTable
WorkerID
WorkerName
WorkerCategoryID
Category Table
CategoryID
CategoryName
ManagerWorker Table
ManagerID
Worker ID
The query results look like this:
ManagerID, ManagerName, WorkerID, WorkerName, CategoryID, CategoryName
1, JOE, 1, SAM, 1, Cat1
1. JOE, 2, BILL, 2, Cat2
1, JOE, 3, MIKE, 3, Cat3
2, ALAN, 1, SAM, 1, Cat1
2, ALAN, 4, ADAM, 2, Cat2
2, ALAN, 5, MARK, 2, Cat2
3, DOUG, 2, BILL, 2, Cat2
3, DOUG, 5, STEVE, 3, Cat3
In my matrix report, I have the mangers in rows, the Categories in columns and the workers in the details.
The only issue I have is when there are 2 workers in the the same category for a manager. The first record appears, but not the second. So the
2, ALAN, 4, ADAM, 2, Cat2 record appears but not the
2, ALAN, 5, MARK, 2, Cat2 record.
Thanks for any direction you can give me.