SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Tablix Report : Column Grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sureshmanian
Starting Member

India
25 Posts

Posted - 02/13/2014 :  09:27:55  Show Profile  Reply with Quote
Dear all
Please find the table : TestTT, which is used to store the timetable for the students

CREATE TABLE [dbo].[TestTT]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[TimeRange] [nvarchar](50) NULL,
[StaffName] [nvarchar](50) NULL,
[StudentDetail] [nvarchar](50) NULL,
[EventDate] [nvarchar](50) NULL
)

Different values
Insert into TestTT values('10:00-11:30','MAli','PS','1');
insert into TestTT values('11:45-13:15','MAli','SJR','1');
insert into TestTT values('14:15-15:15','MAli','LS','1');
insert into TestTT values('15:30-16:30','MAli','TM','1');
insert into TestTT values('16:30-17:30','LSam','SB','1');
insert into TestTT values('16:30-17:30','MKama','MAlT','1');
insert into TestTT values('10:00-11:30','AGho','ERes','2');
insert into TestTT values('11:45-13:15','MAli','CSR','2');
insert into TestTT values('14:15-15:15','LSam','AOso','2');
insert into TestTT values('15:30-16:30','AGho','SShe','2');
insert into TestTT values('10:00-11:30','AGho','DResFo','3');
insert into TestTT values('11:45-13:15','MKama','WRAr','3');
---

Let me go to Report Builder:
---------------------------
Tablix report

Row Group : By TimeRange
Column Group : Parentgroup -- EventDate, Childgroup -- StaffName
Details : StudentName

Expected output For EventDate-1 and Other dates

10.00-11:30 MAli
PS
11:45-13:15 MAli
SJR
14:15 15:15 MAli
LS
15:30 -16:30 MAli
TM
16:30-17:30 LSam Mkama
SB MAIT



Whereas the remaining the cells are filled with previous values ie) for example in firstrow MAli and PS are repeating for second column also. Similarly other columns are also filled with the previous values, I required to have only once, if the values are same then second column has to be empty.

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/13/2014 :  12:38:29  Show Profile  Reply with Quote

SELECT TimeRange,
STUFF((SELECT ' ' + StaffName FROM TestTT WHERE TimeRange = t.timeRange AND EventDate = t.EventDate ORDER BY ID FOR XML PATH('')),1,1,''),
STUFF((SELECT ' ' + StudentDetail FROM TestTT WHERE TimeRange = t.timeRange AND EventDate = t.EventDate ORDER BY ID FOR XML PATH('')),1,1,'')
FROM (SELECT TimeRange FROM TestTT WHERE EventDate = 1)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sureshmanian
Starting Member

India
25 Posts

Posted - 02/17/2014 :  02:54:38  Show Profile  Reply with Quote
Thank you Visakh16 its worked with minor corrections.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/17/2014 :  05:24:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sureshmanian
Starting Member

India
25 Posts

Posted - 02/19/2014 :  04:45:36  Show Profile  Reply with Quote
Hi Vishakh16
I have notice one issue in the above query:

insert into TestTT values('16:30-17:30','LSam','AA','1');
insert into TestTT values('16:30-17:30','Mkama','BB','1');

now Lsam and Mkama is repeating more than once (according to the number of students at the same timerange and dayid) and students name sequence are getting changed.

under same timerange for the particular eventdate
Is there any way instead of joining staffnames and studentdetails under one column ---> can I have it in two columns based on Staffname

LSAM || MKama
SB, AA || Mait, BB

Thank you.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000