SSRS Performances - Matrix control VS Table control
April 28th, 2008 — ¥ong¥s
Online project management, task software, and free collaboration workspace: AJAXWorkspace
AJAXWorkspace is a free online workspace that offers task tracking software, team collaboration, online calendar, document sharing, and file management for project management and teamwork. |
SSRS provide table and matrix control to let user populate data in different scenario. Table control let user to populate simple format of data where fixed columns applied and rows grow dynamically. Matrix provide more powerful features where it columns and rows can be dynamically grow. In that case, writing a store procedure to support a matrix become easier in some scenarios.
E.g. you have a source table Sales with data below:
|
ClientID |
Product |
SalesAmount |
|
Customer A |
X |
1000 |
|
Customer B |
Y |
2000 |
|
Customer A |
Y |
1000 |
|
Customer C |
Z |
3000 |
|
Customer C |
W |
4000 |
|
Customer A |
Y |
2000 |
You need to generate a sales report with the following format:
|
ClientID |
W |
X |
Y |
Z |
|
Customer A |
0 |
1000 |
3000 |
0 |
|
Customer B |
0 |
0 |
2000 |
0 |
|
Customer C |
4000 |
0 |
0 |
3000 |
You can simply write a SP as below and bind the columns to products and rows to ClientID:
SELECT ClientID, Product, Sum(SalesAmount) FROM Sales GROUP BY ClientID, Product
This solution seems pretty simple and straight forward. However, this will only work fines if you have small set of record in your tables. If you have hundred thousand of records with >20 type of products in your table, this solution no longer perfect. You will be facing performance issues. Yes, Matrix control very slow when deal with large amount of data.