Matrix report: Link column query to row query

Question

Matrix report: Link column query to row query

Magy

SSCarpal Tunnel

Points: 4217
More actions
January 21, 2016 at 1:48 pm

#399602

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63434 More actions · Answer 1

Magy,

Thanks for the sample data... would be easier to consume if you did it like this or with create table/insert scripts. Just makes it easier to reproduce your problem without doing a lot of work. Here's how I did it...

SELECT 'Jimmy' AS Person,'Sword' As Weapon,3 AS Qty

UNION ALL SELECT 'Jimmy','Axe',0

UNION ALL SELECT 'Jimmy','Bow',7

UNION ALL SELECT 'Alex','Sword',1

UNION ALL SELECT 'Alex','Axe',11

UNION ALL SELECT 'Alex','Bow',0

UNION ALL SELECT 'Kate','Sword',4

UNION ALL SELECT 'Kate','Axe',6

UNION ALL SELECT 'Kate','Bow',1;

Once you have that, you add a Matrix to the design grid, and put 'Person' in Rows, and 'Weapon' in Columns (you can switch them if you want), and then use SUM([Weapon]) as your aggregate expression, and you're off to the races. You do not need to create the totals in your query/stored procedure that you base the report on. SSRS will do the totals for you.

Sorry, I just noticed the fact that you're doing a count and not a sum. The method is still the same, but you would use COUNT() as your aggregate. If you do an aggregate on a non-numeric field, I think it defaults to COUNT. If the field you're aggregating is numeric, it will default to SUM. The rest still applies - you could just use a dataset that had (Person, Weapon, [other columns]) as your dataset, and then do the counting inside your matrix.

Hope this helps,

Pieter

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 2

Thanks for the tip on creating a query to show data. Much better than what I had. 🙂

I'm afraid I'm still a little lost though.

So I have my query:

SELECT

WorkerName, PartName,

Count(PartName) AS 'Total Made' FROM Parts_List

WHERE userID IN(select userID from warehouse1)

GROUP BY PartName, WorkerName

I guess I'm unsure of how to break it apart so that the rows are the workers, and the columns are the part names. Like, how does that work? I'm sorry, I'm new when it comes to SSRS.

Thanks!

pietlinden SSC Guru Points: 63434 More actions · Answer 3

Sorry, I should have clarified. You don't need the totals query at all. You can do the counting/totals whatever in SSRS. If you use a simple stored procedure like

SELECT Person, Weapon, [other column]

FROM MyTable

WHERE [filter]...

then you can add a matrix to your design layout and use Person and Weapon as rows/columns and then a COUNT of [other column] to do your totals. If you had to, you could create a column in SSRS and make it equal to a constant, like "A" and then just count that.