Matrix Control in SSRS

Just wondering when is it preferred to use Matrix control as opposed to a table? Also, on a related note, can a table control be used for displaying information from one data set or could information coming from multiple data sets be bound to a single table in such a way that there could be logically different sections within the same table?

Thanks.

I ask that question at times too

I think it's whenever you need to pivot table/data, say in Excel (convert columns to rows)

However, there are reports that I have to manually create a side-by-side comparison table in the database

as front-end matrix isn't exactly what I wanted

in fact, I have only about 2 reports that I used matrix so far

I think you can only return & use 1 dataset in a report (1st dataset if Query/Proc returns multiple)

so if you want to use different datasets, either use sub-report, or union them (vertically/horizontally) in the backend

|||

I think what I need is the (Dynamic Rows x Dynamic Columns), or essentially Dynamically generated Pivot Table. This is because, I do not know in advance how many rows or columns will be returned from the DB. All I have is an ID of a parent table which could be used to join and get rows from the child table, and then for each of those rows I need to get rows from another table and display them in columns of the report (Is this the same as Dynamic Cross Tabs/Privot?). Can I use the matrix control for this scenario and do I need to write some dynamic SQL for this?

Any suggestions will be appreciated.

DNG.

|||

DotNet_Guy wrote:

I think what I need is the (Dynamic Rows x Dynamic Columns), or essentially Dynamically generated Pivot Table. This is because, I do not know in advance how many rows or columns will be returned from the DB. All I have is an ID of a parent table which could be used to join and get rows from the child table, and then for each of those rows I need to get rows from another table and display them in columns of the report (Is this the same as Dynamic Cross Tabs/Privot?). Can I use the matrix control for this scenario and do I need to write some dynamic SQL for this?

Any suggestions will be appreciated.

DNG.

This is exactly where I use the Matrix report item in SSRS. You should not need any sort of dynamic SQL under the hood. Your SQL query should return a well-defined rowset (with a fixed set of fields) and the Matrix report item will turn some of the fields into columns, some into rows, and some into data based on how you set up your report.

It's totally trivial to test this - if you already have your query written, create a few sample reports and try it out with different settings.

The one obvious drawback I see is that the Matrix report item does not readily support a large number of data fields. It can do it, but the resultant report tends to get very large, very quickly.

|||Cool. I will give it a try. BTW, how come you have so few certs against your name? jus kidding |||

DotNet_Guy wrote:

Cool. I will give it a try. BTW, how come you have so few certs against your name? jus kidding

ROFL!

They used to require MCTs pass any exams related to a course before being allowed to teach that course, so I hat to take a lot of exams. The letters just sort of came along naturally.

Let us know how the Matrix works for you, ok?

|||Yes it does. Thanks for the help. I believe I can now add non-pivot rows and columns as well to the dynamic matrix. It should all be in the sql2k5 tutorial documentation, but I haven't had the time to dig in.|||

I am able to display the data in matrix, however I need to place in next to a table report item, so that the rows in table and matrix together would represent the data for same entity. In order to accomplish that, the Matrix rows need to be properly aligned with Table rows. But thats not happening. For some reason, in the Layout view they align properly, but when I Preview the report, the rows are not aligned. I have checked the cell sizes of both in the properties, they are both identical. So, I am very surprised why the Matrix row size is growing less than Tabel row size when the report is generated. Any ideas how to fix this?

|||

Can any body explain why I am having the above mentioned strange behavior upon rendering the matrix and table next to each other?

On a related note, how can I access the Sub Total fields of the Matrix to use them in calculation elsewhere?

Thanks.