Display XML Hierarchies in Reporting Services

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author
To all,
I have read several posts that say SQL 2005 Reporting Services does not
display hierarchical XML data very well. It must first be flattened.
Is this true?
In my scenario, I capture hierarchical data and store it in an XML
column in a SQL2005 table. Now I need to display it in a Server Side
Report using SQL 2005 Reporting Services. This data will have any
number of descendants that I need to ensure is intact and visible on
the report.
What are my options to get it done?
And if it really can't be done in Reporting Services what other
approach would be worth looking into?
Thanks in advance,
RobI think Asif Sayed has an example on Code Project that may help you. The
dataset is not based on xml data but demonstrates a parent/child
relationship.
http://www.codeproject.com/useritems/RecursiveData.asp
Steve MunLeeuw
"Rob" <rclassen@.IDSTechNet.com> wrote in message
news:1161266421.322891.145750@.i3g2000cwc.googlegroups.com...
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse | Find messages by this author
>
> To all,
> I have read several posts that say SQL 2005 Reporting Services does not
>
> display hierarchical XML data very well. It must first be flattened.
> Is this true?
>
> In my scenario, I capture hierarchical data and store it in an XML
> column in a SQL2005 table. Now I need to display it in a Server Side
> Report using SQL 2005 Reporting Services. This data will have any
> number of descendants that I need to ensure is intact and visible on
> the report.
>
> What are my options to get it done?
>
> And if it really can't be done in Reporting Services what other
> approach would be worth looking into?
>
> Thanks in advance,
>
> Rob
>

display summary week total rows from sql database

(I moved this thread from datagrid area)

I have a sql database that has individual records consisting of name, date, hours worked among other fields.

Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date.

My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.

TABLE:

dan 12/13/2012 12:00:00 AM9.123dan 12/14/2012 12:00:00 AM3.123123cara 12/12/2012 12:00:00 AM4.222cara 12/16/2012 12:00:00 AM3.3333cara 12/17/2012 12:00:00 AM2

CODE:

Select distinct(name),
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Wednesday')as Wednesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Thursday')as Thursday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Saturday')as Saturday,
(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Total
from dbo.testtime YT
group by date, name, hours

RESULTS:

cara NULL NULL NULL 4.222 NULL NULL NULL 4.222
cara NULL 2 NULL NULL NULL NULL NULL 2
cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333
dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123
dan NULL NULL NULL NULL 9.123 NULL NULL 9.123

Like I said, I am SO close, I just need it to look like;

NAME SUN MIN TU WED TH FR SA TOTAL

cara 3.333 2 4.222 9.555
dan 9.125 3.125 12.5

TIA

dan

You need to create a table variable with columns of name, Sun-Sat columns to accumulate the data into. You can then select from that table the required summary.

|||

OK, sounds good.

Um, what is a table variable?

Is that like a view?

can you give me psudo-code to work with?

thanks MUCH!

Dan

|||

With table create script of SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TimeData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[Hours] [decimal](8, 6) NOT NULL,
CONSTRAINT [PK_TimeData] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Table insert script of

INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'13/Dec/2012 12:00:00 AM'), 9.123) -- I am in the UK, hence the change of date format
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'14/Dec/2012 12:00:00 AM'), 3.123123)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'12/Dec/2012 12:00:00 AM'), 4.222)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'16/Dec/2012 12:00:00 AM'), 3.3333)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'17/Dec/2012 12:00:00 AM'), 2)

|||

The TSQL

DECLARE @.MyTableVar table(
[Name] VARCHAR(5) NOT NULL,
Sun [decimal](8, 6) NOT NULL DEFAULT ((0)),
Mon [decimal](8, 6) NOT NULL DEFAULT ((0)),
Tue [decimal](8, 6) NOT NULL DEFAULT ((0)),
Wed [decimal](8, 6) NOT NULL DEFAULT ((0)),
Thu [decimal](8, 6) NOT NULL DEFAULT ((0)),
Fri [decimal](8, 6) NOT NULL DEFAULT ((0)),
Sat [decimal](8, 6) NOT NULL DEFAULT ((0)),
Total [decimal](8, 6) NOT NULL DEFAULT ((0))
);
DECLARE @.NAME VARCHAR(50)
DECLARE @.DATE DATETIME
DECLARE @.HOURS decimal(8, 6)
DECLARE xCURSOR CURSOR FOR
SELECT Name, Date, Hours FROM TimeData
OPEN xCURSOR
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM @.MyTableVar WHERE [Name] = @.NAME)
INSERT INTO @.MyTableVar([Name]) VALUES (@.NAME)
IF datename(dw, @.DATE)='Sunday'
UPDATE @.MyTableVar SET Sun = Sun + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Monday'
UPDATE @.MyTableVar SET Mon = Mon + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Tuesday'
UPDATE @.MyTableVar SET Tue = Tue + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Wednesday'
UPDATE @.MyTableVar SET Wed = Wed + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Thursday'
UPDATE @.MyTableVar SET Thu = Thu + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Friday'
UPDATE @.MyTableVar SET Fri = Fri + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Saturday'
UPDATE @.MyTableVar SET Sat = Sat + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
END
SELECT * FROM @.MyTableVar
CLOSE xCURSOR
DEALLOCATE xCURSOR

gives

Name Sun Mon Tue Wed Thu Fri Sat Total
-- --- --- --- --- --- --- --- ---
dan 0.000000 0.000000 0.000000 0.000000 9.123000 3.123123 0.000000 12.246123
cara 3.333300 2.000000 0.000000 4.222000 0.000000 0.000000 0.000000 9.555300

Obviously a Cursor is not particularly efficient and needs to be eliminated. Also the Name column would need to be indexed (if possible) if there are more than 10 rows.

|||

TAT~

THat is awsome!
Thank you SO much for your efforts.

I ventured out on my own and came up with the following (I actually changed it to look at a test/prod table, so name is UserName)

But the code actually WORKEd!

Here it is, if youd care to comment:

--make var tqable

Declare @.tempweek TABLE
(UserName nvarchar(50), Sunday DECIMAL(8,6), Monday DECIMAL(8,6), Tuesday DECIMAL(8,6), Wednesday DECIMAL(8,6), Thursday DECIMAL(8,6), Friday DECIMAL(8,6), Saturday DECIMAL(8,6), Total DECIMAL(8,6))

--fill table

INSERT INTO @.tempweek
SELECT UserName,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Sunday')AS Sunday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Monday')AS Monday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Tuesday')AS Tuesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Wednesday')AS Wednesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Thursday')AS Thursday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Friday')AS Friday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Saturday')AS Saturday,
(SELECT SUM(HoursWorked)from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.HoursWorked = YT.HoursWorked) AS Total
from db_owner.PS_HR_Hrs YT

--select data

select UserName , sum(sunday)as Sunday, sum(monday) as Monday, sum(tuesday)as Tuesday, sum(wednesday)asWednesday, sum(thursday)as Thursday, sum(friday)as Friday, sum(saturday)as Saturday, sum(total) as Total
from @.tempweek
group by UserName

|||

Your solution will probably be faster as you do not use a CURSOR! Both solutions will be gluttons for memory for the few milliseconds they run, so as always, never stint on the RAM for a server hosting SQL Server.