Stored Procedure / SQL Reporting Services
-
I have 3 tables T1,T2,T3 where all 3 have the same
table structure (Date,Name) fields
Using SQL reporting services,for a given date range (say Oct 1 2005 to Oct 5 2005),
I need to generate a report as follows
Table1 Table2 Table3 Total
Oct 1 X1 Y1 Z1 X1+X2+X3
Oct 2 X2 Y2 Z2
Oct 3 X3 Y3 Z3
Oct 4 X4 Y4 Z4
Oct 5 X5 Y5 Z5
Total X1+... Y1+... Z1+..
where X1 - total count of records from Table1 where Creation date=Oct1 2005
Y1 - total count of records from Table2 where Creation date=Oct1 2005
Z1 - total count of records from Table3 where Creation date=Oct1 2005
Similary X2,Y2....
1) How should i proceed. DO i need to create a stored procedure which returns this result
and use this in reporting services.
I am new to TSQL, so any help regarding stored procedure would be appreciated. Thanks,
Sarang
-
Is this related to http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=217491 ?
I'm thinking it would be best done in a stored procedure. Can you provide a table structure for us to work with?
--------------------
Colt 45 - the original point and click interface
-
Hi Phill,
yes. this post is related to the one which you pointed to (for which you have answered :-))
The table structure is:
The table has EventID (int),Source(nvarchar),Message(ntext) and EventTime(datetime) fields.
All tables have the same structure.
Thanks,
Sarang
-
Ok, borrowing substantially from Learn2Live's post on the other message thread, see if this works. You can copy the whole lot into Query Analyzer as is and test it out. The last half you can change to reflect your actual table names and put it into a stored procedure much the same as was shown in the other thread.
For the totals along the bottom, just drop the fields from the dataset into the footer of the table and they'll automatically become a sum(). I've included a total for each date, but you can remove this and add the totals using the ReportItems collection if you want.
-- populate sample data DECLARE @Table1 TABLE (EventID int, Source nvarchar(30), Message ntext, EventTime datetime) DECLARE @Table2 TABLE (EventID int, Source nvarchar(30), Message ntext, EventTime datetime) DECLARE @Table3 TABLE (EventID int, Source nvarchar(30), Message ntext, EventTime datetime)
INSERT INTO @Table1 VALUES(1,'Table1Source1','Table1Message1','20050908 12:11:00') INSERT INTO @Table1 VALUES(2,'Table1Source2','Table1Message2','20050909 13:12:00') INSERT INTO @Table1 VALUES(3,'Table1Source3','Table1Message3','20050910 14:13:00') INSERT INTO @Table1 VALUES(4,'Table1Source4','Table1Message4','20050911 15:14:00') INSERT INTO @Table2 VALUES(1,'Table2Source1','Table2Message1','20050908 16:15:00') INSERT INTO @Table2 VALUES(2,'Table2Source2','Table2Message2','20050909 17:16:00') INSERT INTO @Table2 VALUES(3,'Table2Source3','Table2Message3','20050911 18:17:00') INSERT INTO @Table2 VALUES(4,'Table2Source4','Table2Message4','20050912 19:18:00') INSERT INTO @Table3 VALUES(1,'Table3Source1','Table3Message1','20050908 20:19:00') INSERT INTO @Table3 VALUES(2,'Table3Source2','Table3Message2','20050910 21:20:00') INSERT INTO @Table3 VALUES(3,'Table3Source3','Table3Message3','20050912 22:21:00') INSERT INTO @Table3 VALUES(4,'Table3Source4','Table3Message4','20050912 23:22:00')
SELECT * FROM @Table1 SELECT * FROM @Table1 SELECT * FROM @Table1
-- possible stored procedure code DECLARE @Start_Date DATETIME, @End_Date DATETIME SET @Start_Date = '20050908 00:00' SET @End_Date = '20050912 23:59'
SELECT CONVERT(varchar(6), Evnts.EventDate, 100) as EventDate , SUM(ISNULL(Evnts.Table1Count, 0)) as Table1Count , SUM(ISNULL(Evnts.Table2Count, 0)) as Table2Count , SUM(ISNULL(Evnts.Table3Count, 0)) as Table3Count , SUM(ISNULL(Evnts.Table1Count, 0) + ISNULL(Evnts.Table2Count, 0) + ISNULL(Evnts.Table3Count, 0) ) as TotalCount FROM (
SELECT CAST(CONVERT(varchar(12), EventTime, 112) as datetime) as EventDate , COUNT(EventID) as Table1Count , CAST(NULL as int) as Table2Count , CAST(NULL as int) as Table3Count FROM @Table1 WHERE EventTime BETWEEN @Start_Date AND @End_Date GROUP BY CAST(CONVERT(varchar(12), EventTime, 112) as datetime) UNION SELECT CAST(CONVERT(varchar(12), EventTime, 112) as datetime) as EventDate , NULL as Table1Count , COUNT(EventID) as Table2Count , NULL as Table3Count FROM @Table2 WHERE EventTime BETWEEN @Start_Date AND @End_Date GROUP BY CAST(CONVERT(varchar(12), EventTime, 112) as datetime) UNION SELECT CAST(CONVERT(varchar(12), EventTime, 112) as datetime) as EventDate , NULL as Table1Count , NULL as Table2Count , COUNT(EventID) as Table3Count FROM @Table3 WHERE EventTime BETWEEN @Start_Date AND @End_Date GROUP BY CAST(CONVERT(varchar(12), EventTime, 112) as datetime)
) as Evnts GROUP BY Evnts.EventDate
--------------------
Colt 45 - the original point and click interface -
Hi Phills,
Thank you for your reply. I modified the SP and was able to generate Report. Never tought that the SP's are so easy. Your post was helpfull.
I did some googling on MS-SQL Stored Procedure tutorials , but could not find any satisfacatory searches. In case if you are aware of any such tutorials, could you let me know.
Thank you for your time.
-Sarang
-
There is nothing that special about writing stored procedures. When it comes to the crunch they are basically just a bunch of T-SQL commands that are executed together.
From Books Online, the best source for SQL Server
"Stored Procedure
A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system stored procedures."
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply