Variable rows and columns in SSRS Matrix

Question

(SSRS 2008)

I have a dataset with results looking like this:

FUNCTION | EMP-NMB
------------------
    A    | 100
    A    | 101
    A    | 103
    B    | 102

I want to display this data in my report in this way:

  A  |  B  
------------
100  | 102
101  |
103  |

I am managed to display it this way:

  A  |  B  
------------
100  | 
101  |
103  |
     | 102

But that table becomes very large with more data. The number of employees and functions can vary. For now I am using a Matrix, but I don't know how to configure it to work the way I want.

Answer 1

I think the problem is that you are probably using EMP-NMB as you Row Group grouping.

Since you want the report to display different ones on the same line, you need to something else. Unfortunately, there isn't anything is the data you list but you can add a ROWNUMBER() to the query.

SELECT FUNCTION, EMP-NMB, ROW_NUMBER() OVER(PARTITION BY FUNCTION ORDER BY EMP-NMB) AS ROW_NUM 
FROM ... 

Then change the tablix Row Group Group On to use the new ROW_NUM field.