SSRS usw two matrix for one dataset

2
votes

I'm trying to build an SSRS (2017) report that would look like it's implementing two matrices. I have one dataset with fields:

VendorName Project BillYear Amount

I want to show one line per vender and pivot on both Project and BillYear so my report would look like

Vendor Project1 Project2 Project3 2017 2018 2019 -- with sum(amount) for the data.

Can this even be done?

Thank you in advance.

Matthew Wells matthew.wells@firstbyte.net

1
reporting-services

1 Answers

2
votes

You can create a table with adjacent column groups to get the desired layout. An easy way to start off is by using the Table/Matrix wizard. This will help create the initial row and column groups for you. Of course you can do this without the wizard as well.

enter image description here

Next, add an adjacent column group to the right. This will be grouped by BillYear. The resulting table should look something like this:

enter image description here

Now you will get one row per vendor along with columns for each project and bill year. However many there happen to be. You can add rows/columns outside these groups to get subtotals as needed.