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.
July 17, 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 17, 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 18, 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 18, 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 18, 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?