Search code examples
ssrs-2008ssrs-2008-r2

SSRS Matrix Grouping Vertically to keep headers in same column? Is it not possible?

using SSRS 2008 R2 here.

I've been able to get a similar layout to work with a regular tablix, where I get each group header to fall on top of each other in the same column by adding a row within that group, however I need to use a Matrix because of a dynamic column (month below). When I try to add another row, it only adds a row where the monthly data starts, not in the headers. So the headers stay in their each column. In trying to keep the example as easy as possible, I'm trying to do something like this (Store theme). 

STORE NAME   |     MONTHS
STATE        |     SALES
TOWN(S)      |     SALES

which woudl look something like this in a Matrix 

WALMART        JAN       FEB      MARCH   etc....
TEXAS        | 3000      2000     6000
HOUSTON      | 1000      500      2500
AUSTIN       | 2000      1500     3500

I've only been able to produce something like this where each group is a seperate column:

STORE   |  STATE |  CITY    |  JAN   |  FEB   |  MAR  |
WALMART |  TEXAS |  HOUSTON |  1000  |  500   |  2500 |
                 |  AUSTIN  |  2000  |  1500  |  3500 |

Again, I've been able to get a regular Tablix formatted like this, but a Matrix I'm struggling with. Can anyone help me on how to do this? Thank you in advance!!

Solution

  • It is possible using a tablix/matrix and adding some special grouping settings.

    1. Add a tablix with the City field in Row Groups pane: enter image description here
    2. Right click the City group, select Add Group / Parent Group. Choose State and set Add a Group Header
      enter image description here
    3. Delete the left most column (State column added in the previous step). enter image description here Note the group is still present.
    4. Right click the STATE group and add a Parent Group as step 2. In this case choose STORENAME enter image description here

    5. Again delete the left most column (Store Name column added in the previous step)

    6. You will get the following tablix.

    enter image description here

    1. Delete the first row

    enter image description here

    1. Set the Fields using the Row Group hierarchy order. STORENAME/STATE/CITY enter image description here
    2. Right click the first cell in the next column and add a group / Column Group / Parent Group. Choose MONTH in group by. enter image description here
    3. Delete the first row.
    4. Set SUM(Fields!Sales.Value) in the next cells in the same column.

    After these steps you will get a tablix like this in design window.

    enter image description here

    It should produce:

    enter image description here

    Let me know if this helps.