Tablix Report : Column Grouping

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 2008 Forums

Analysis Server and Reporting Services (2008)

Tablix Report : Column Grouping

Author

Topic

sureshmanian
Starting Member

26 Posts

Posted - 2014-02-13 : 09:27:55

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

52326 Posts

Posted - 2014-02-13 : 12:38:29

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sureshmanian
Starting Member

26 Posts

Posted - 2014-02-17 : 02:54:38

Thank you Visakh16 its worked with minor corrections.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-17 : 05:24:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sureshmanian
Starting Member

26 Posts

Posted - 2014-02-19 : 04:45:36

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources