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.
So far, I don’t see any other solutions except convert it to table control. However, converting to table control required rewrite on the source SP. This seems a bit difficult but actually can be done via sample SP below:
SELECT ClientID, SUM(W) AS W, SUM(X) AS X, SUM(Y) AS Y, SUM(Z) AS Z
FROM (
     SELECT ClientID, SalesAmount AS W, 0 AS X, 0 AS Y, 0 AS Z FROM Sales WHERE Product = ‘W’
     UNION ALL
     SELECT ClientID, 0 AS W, SalesAmount AS X, 0 AS Y, 0 AS Z FROM Sales WHERE Product = ‘X’
     UNION ALL
     SELECT ClientID, 0 AS W, 0 AS X, SalesAmount AS Y, 0 AS Z FROM Sales WHERE Product = ‘Y’
     UNION ALL
     SELECT ClientID, 0 AS W, 0 AS X, 0 AS Y, SalesAmount  AS Z FROM Sales WHERE Product = ‘Z’
) T
GROUP BY ClientID
Using Matrix Control, I can’t get my report after waiting for an hour. However, with the new query using table control, I manage to get my result within 5 minutes. So, think carefully before using matrix control.
Â
July 17th, 2008 at 9:48 pm
Thanks, this is good. But, i have the similar type of work which am doing and need your support to help me out.
Am new to SQL Server Reporting Service 2005, and I have a requirement where i need fetch some of the fileds from the table and generate a report using SSRS and then i have to integrate it with MOSS 2007(am not worried abt the intergation which i can able to do)
My Table Structure
HourEnding NodeName Quantity
0 A 100
1 A 200
2 A 300
3 A 500
0 B 750
1 B 450
2 B 550
3 B 50
0 C 120
1 C 100
2 C 200
3 C 300
0 D 100
1 D 200
2 D 300
3 D 400
Output
But while displaying i need to display it horizontally as shown below,the Hourending should not be repeated
HourEnding A B C D (A+B) (C+D)
0 100 750 120 100 850 220
1 200 450 100 200 650 300
2 300 550 200 300 850 500
3 500 50 300 400 550 700
I have used SSRS Matrix Control to display Hourending,A,B,C,D. I dont know how to calculate the sum up two values using Matrix.
Also, if am not wrong matrix control wont allow group/aggregate functions.
Can any one in the group help me to find a solution.
Thanks in Advance
- Shivnair
July 17th, 2008 at 10:01 pm
Note: The node name in my table is say A,B,C,D,E,F but i need only A,B,C,D dont want E & F to get displayed in the report.
please give me some idea to proceed with
-Shivnair
July 18th, 2008 at 12:22 am
Hi Shivnair,
This query should able to solve all your needs:
SELECT
HourEnding,
NodeName,
SUM(Quantity) AS Quantity
FROM TableA
WHERE NodeName IN (’A', ‘B’, ‘C’, ‘D’ )
GROUP BY HourEnding,
NodeName
UNION ALL
SELECT
HourEnding,
‘A + B’ AS NodeName,
SUM(Quantity) AS Quantity
FROM TableA
WHERE NodeName IN (’A', ‘B’ )
GROUP BY HourEnding
UNION ALL
SELECT
HourEnding,
‘C + D’ AS NodeName,
SUM(Quantity) AS Quantity
FROM TableA
WHERE NodeName IN (’C', ‘D’ )
GROUP BY HourEnding
–YS
July 18th, 2008 at 1:56 am
¥ong¥s,
I am very thankful to you for your reply and the above query worked out well and also passed a parameter where i will get the data for the given reading date.
Thanks a ton again!!
-Shiva
July 18th, 2008 at 9:14 pm
Hi Yong,
I need one more help here..as said i have used the date parameter where the database has only 1 date field (ReadingDate) but while displaying the records i have to use daterange where the startdate n the enddate should be fetched frm the reading date field.. i just modified the above solution but not it displays a blank screen.
Please can you help me in this. Also, i there any way i can mail the report automatically?
August 17th, 2008 at 10:16 pm
[...] Read the rest of this entry (moved to http://www.bronios.com) » Posted in SQL, SSRS. Tags: Report Matrix Control, Report Table Control, SSRS. [...]