May 19th, 2011, 03:43 PM
SQL Server Reporting Services Multiple Linked Matrices
I'm new to Reporting services, so if this is a noob question please forgive me. Also, I'm not sure where else to place this question, so if it belongs somewhere else please forgive me.
I am designing a report that will return several data points, grouped by Validation, Contract, Reporting Year, and Month, returning text for Validation, Contract and Year and a sum for Month. The first matrix looks like this (generally speaking):
As you can see, there can be multiple years for a contract, multiple contracts for a validation, and multiple validations. That's the first matrix.
VALIDATION CONTRACT REPORT YR JAN FEB MAR
Claims 111A 2010 100 200 150
2011 75 80 112
111B 2011 80 80 81
Calls 111A 2010 Etc.
The second matrix is a total of the information listed above, like so:
As you can see in the second matrix, we just have a sum by month by report year. Fairly simple, right?
REPORT YR JAN FEB MAR
2010 100 200 150
2011 155 160 193
I have a page break on the first matrix for the row group VALIDATION so that every new validation is on a new page. Which works fine. What I want is for the first matrix to display the information for the first validation, then have the second matrix display the information for the first validation, then break to a new page, display the 1st matrix for the next validation, and then the 2nd matrix for the next validation, and so on.
My problem is that this is not working. What happens is that the first matrix shows with the first validation, then goes to the next page and shows the second validation, and so on. Then at the end of the report the 2nd matrix finally displays, and gives a sum of ALL data points across all validations. Which is not what I want and/or need.
Does anyone know how to link 2 matrices together to display the information in the way I want to? I know that I could just create multiple matrices to handle each validation and its totals, or that I could just add total rows to the 1st matrix. But the client doesn't want the totals row, and the number of validations could change from month to month. So I'd like to link or group the 2 matrices together to accomplish this.
Click Here to Expand Forum to Full Width