SSRS tablix and matrix hybrid? Show every row of detail but then some columns are like Matrix

Question

I've tried building this with a tablix, then a matrix but haven't been able to get my desired results - The output I'm looking for is like the below format:

                                   |    MONTHS
EMPLOYEE    DEPARTMENT     GROUP   |    JAN        FEB     etc.. (whichever months have data)
-----------------------------------------------------------------------------------------------
Joe          Sales           A     |     5          3
Joe          Sales           B           2          0   
Sam          Sales           A           0          2
Sam          Sales           B           1          1
Sam          Sales           C           0          4
Sam          Sales           D           3          3

Data looks like

EMPLOYEE     Dept     Group     Month     items
Joe          Sales     A         JAN        5
Joe          Sales     B         JAN        2
Joe          Sales     A         FEB        3

Sam          Sales     B         JAN        1
Sam          Sales     D         JAN        3

Sam          Sales     B         FEB        2
Sam          Sales     C         FEB        1
Sam          Sales     D         FEB        4
Sam          Sales     B         FEB        3

---------    row doesn't exist for JOE, GROUP B in FEB so 0 ------------
---------    row doesn't exist for SAM, GROUP A and C in JAN so 0 ------------

I've come very close to creating this with a Tablix, that I created a parent group of MONTH on the ITEMS cell. My output now looks like this:

                                          JAN        FEB
EMPLOYEE     DEPARTMENT      GROUP      ITEMS      ITEMS
Joe          Sales           A     |     5          
Joe          Sales           A     |                3
Joe          Sales           B     |     2

As you can see above, it's not properly putting the group A's on the same line for JAN and FEB, they're on 2 separate lines.

It's like I need to create row groups for EMPLOYEE, DEPARTMENT, GROUP by MONTH?

Any help would be greatly appreciated!

You would need a matrix - it doesn't look like you need a grouping for your rows because it's displaying the details for each record but you do need a COLUMN group and it should group by month. — papermoon88
@papermoon88 thanks for the response. This did help, I actually tried it through the wizard (rather than manually trying to build the matrix) and it's looking much better. Only issue I'm running into now is that, for example EMPLOYEE name shows up once, is there any way to repeat the employee name for every row that shows up as well as DEPARTMENT and GROUP? Thanks so much — Jay
If your employee name rows are being grouped together on the name then it means there is a row grouping on the employee name (or something similar like the ID). If you want each row to have the details displayed, then make sure there aren't any row groupings. — papermoon88
If I remove the row groupings for EMPLOYEE, DEPT, GROUP (I selected "remove group only") then it just returns 1 row of data for the entire report, under the first name that shows up in the dataset I assume. The items are all summed up into that single line as well — Jay
@papermoon88 ok looks like I was able to get this by going to the group properties of the parent row group (in my case employees) then under group expressions, employee was already there, then clicking "add" and putting and on DEPT, adding and on GROUP. I appreciate your help with this! — Jay

Jay Jay · Accepted Answer · 2020-03-03T17:03:17

First off thanks to @papermoon88 in trying to help me through this.

I was able to get my desired output by using a matrix (I couldn't get this through the tablix). I tried doing it manually through the matrix but struggled. When I tried it through the wizard I threw EMPLOYEE, DEPT and GROUP in the Rows, MONTH in the columns and ITEMS in the data section.

This gave me the output I was looking for, however I needed the employee, dept, and group to repeat for every row (rather than just once in the group). So in order to accomplish this I went to the parent row group (in my case EMPLOYEE), clicked the little arrow, went to Group Properties. Under General >> Group Expressions EMPLOYEE already existed. I then added DEPT and GROUP under that so it read: GROUP ON: EMPLOYEE And on: DEPT And on: GROUP This repeated the values for every cell in the output.

Thanks for the help

SSRS tablix and matrix hybrid? Show every row of detail but then some columns are like Matrix

1 Answers