Stored Procedure / Reporting Services

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure / Reporting Services

Author

Topic

rush2hotguy
Starting Member

3 Posts

Posted - 2005-09-12 : 00:42:44

I have 3 tables T1,T2,T3 where all 4 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

rush2hotguy
Starting Member

3 Posts

Posted - 2005-09-12 : 17:18:27

I found the solution.

Here is the solution (In case if it might be of help to someone)
-----------------------------------------------
However , i could do it with a Stored Procedure.
I am posting it here (in case if any one needs a possible solution)

Thanks to Phills Carter (who hleped with this reply)
--------------------------------------------------------------
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
Hope this helps
Phill Carter

--------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources