Creating a matrix report and linking the rows to the columns

Question

I have this query below that works quite well generating a report like this:

Jimmy |  Sword |  3
Jimmy |  Axe   |  0
Jimmy |  Bow   |  7
Alex  |  Sword |  1
Alex  |  Axe   |  11
Alex  |  Bow   |  0
Kate  |  Sword |  4
Kate  |  Axe   |  6
Kate  |  Bow   |  1

However, the powers-at-be want the PartName data across the top as columns, and then the worker and 'Total Made' count as rows for each part.

So it would look something like this:

    Sword   |   Axe   | Bow

Jimmy     3          0       7
Alex      1          11      0
Kate      4          6       1

Here is my fairly simple query that produces the first/original report:

SELECT
      WorkerName, PartName, 
      Count(PartName) AS 'Total Made' FROM Parts_List
WHERE userID IN (select userID from warehouse1)

GROUP BY PartName, WorkerName

I figured out how to get the PartName as columns by creating a matrix and then adding a Dataset with a query like this:

select PartName From Parts_List

However, I can't figure out how to get the rows of workers and their Count(PartName) to line up with the columns.

Does SSRS even support this?

Thanks!

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-01-25T15:05:21

Using the query you have it is possible get the required matrix using this data arragement:

Add a matrix to the report surface, in Column Group add PartName column and add Worker Name to Row Groups. To show Total made add it in the below PartName cell.

It will produce this:

Let me know if you need further help.

Creating a matrix report and linking the rows to the columns

1 Answers