Matix data region sorting a column by another column
in [Reports]
|
From: dbuchanan on 27 Jun 2008 01:00 I have a matrix data region report (local mode) with two row groups. Is there a way to sort a row group based on the value in another column? All rows contained within the group have the same column values put in place just for this purpose. (The sorting of the row group, as I discovered, has nothing to do with the sorting of the underlying query and everything to do with the alphabetical group name.) Here is a working model of the data table ================================================= -- ================================================= -- Create table RlfData -- ================================================= use Test2 --Select * from RlfData ---drop table RlfData ---truncate table RlfData Create Table RlfData (Hd varchar(4), Dv varchar(4) null, Re varchar(4) null, Ar varchar(4) null, St varchar(4), Lv varchar(4) null, Assoc varchar(10), Rol varchar(10), Title varchar(10), Attend varchar(15), Typ int null) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','','A04','Joe','Mgr III','Sales','4/18/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','','','R03','Rick','Mgr II','Sales','4/18/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','','','','D02','Wen','Mgr I','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','','','','','H01','Fred','Mgr I','Sales','4/18/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','','A13','Chris','Mgr II','Sales','4/18/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','','','R12','George','Mgr II','Sales','12/11/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','','A13','Sue','Mgr III','Sales','6/23/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','','A17','Sharon','Mgr III','Sales','4/18/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S05','S05','Ralph','Sales I','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S05','S05','Edwin','Tech I','Tires','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S05','S05','James','Tech I','Sales','3/13/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S05','S05','Jason','Tech I','Sales','4/18/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S07','S07','Charles','Sales II','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A08','S09','S09','Nancy','Tech I','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A08','S10','S10','Scott','Sales I','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A08','S11','S11','Chuck','Tech II','Tires','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S14','S14','Stanley','Tech IV','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S14','S14','Gene','Tech III','Tires','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S14','S14','Jim','Tech III','Sales','12/11/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S14','S14','Bernard','Tech III','Sales','3/13/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S16','S16','Daniel','Tech II','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S16','S16','Ann','Tech II','Sales','12/11/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S18','S18','Rene','Sales I','Brakes','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S18','S18','Rodney','Sales II','Brakes','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S20','S20','Wane','Tech I','Brakes','3/13/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S20','S20','Amy','Sales II','Sales','12/11/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','','A23','Tim','Mgr III','Sales','12/11/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','','','R22','Lenard','Mgr I','Sales','11/21/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','','','','D21','Larry','Mgr I','Sales','3/2/2008',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','','A28','William','Mgr III','Sales','11/21/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A40','','A40','John','Mgr III','Brakes','67%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','','','R39','Melvin','Mgr II','Sales','6/23/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S24','S24','Terry','Tech IV','Brakes','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S24','S24','Ronald','Tech I','Sales','6/23/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S24','S24','Andrew','Tech I','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S26','S26','Virgil','Tech III','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S26','S26','Chad','Sales II','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S26','S26','Matt','Sales II','Tires','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S29','S29','Denise','Tech II','Tires','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S29','S29','Mary','Sales I','Brakes','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S29','S29','Rita','Tech I','Brakes','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S32','S32','Kevin','Tech II','Tires','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S32','S32','Tyrus','Sales II','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S34','S34','Bruce','Tech IV','Sales','6/23/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S34','S34','Carol','Tech III','Brakes','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S34','S34','Ricky','Sales I','Brakes','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S37','S37','Robert','Tech I','Tires','67%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S37','S37','Judy','Tech II','Brakes','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A40','S41','S41','Mark','Tech I','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A40','S42','S42','Roger','Tech IV','Brakes','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S44','S44','Alan','Sales I','Brakes','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S44','S44','Strphen','Tech III','Tires','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S44','S44','Dale','Tech IV','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S47','S47','Danny','Sales II','Tires','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','','A04','Joe','Mgr III','Sales II','9/14/207',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','','','R03','Rick','Mgr II','Sales II','8/17/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','','','','D02','Wen','Mgr I','Sales II','8/17/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','','','','','H01','Fred','Mgr I','Sales II','9/14/207',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','','A13','Chris','Mgr II','Sales II','9/14/207',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','','','R12','George','Mgr II','Sales II','9/14/207',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','','A13','Sue','Mgr III','Sales II','9/12/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','','A17','Sharon','Mgr III','Sales II','9/14/207',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S05','S05','Ralph','Sales I','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S06','S06','Edwin','Tech I','Carb','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A04','S07','S07','Charles','Sales II','Carb','67%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A08','S09','S09','Nancy','Tech I','Carb','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A08','S10','S10','Scott','Sales I','Tire Press','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R03','A08','S11','S11','Chuck','Tech II','Carb','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S14','S14','Stanley','Tech IV','Carb','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S15','S15','Gene','Tech III','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A13','S16','S16','Daniel','Tech II','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S18','S18','Rene','Sales I','Carb','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S19','S19','Rodney','Sales II','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D02','R12','A17','S20','S20','Wane','Tech I','Tire Press','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','','A23','Tim','Mgr III','Sales II','1/12/2008',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','','','R22','Lenard','Mgr I','Sales II','1/12/2008',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','','','','D21','Larry','Mgr I','Sales II','9/12/2007',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','','A28','William','Mgr III','Tire Press','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A40','','A40','John','Mgr III','Sales II','6/23/2006',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','','','R39','Melvin','Mgr II','Sales II','1/12/2008',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S24','S24','Terry','Tech IV','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S25','S25','Ronald','Tech I','Carb','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S26','S26','Virgil','Tech III','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A23','S27','S27','Chad','Sales II','Carb','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S29','S29','Denise','Tech II','Carb','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S30','S30','Mary','Sales I','Carb','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S31','S31','Rita','Tech I','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A28','S32','S32','Kevin','Tech II','Carb','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S34','S34','Bruce','Tech IV','Carb','99%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S35','S35','Carol','Tech III','Carb','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S36','S36','Ricky','Sales I','Tire Press','78%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S37','S37','Robert','Tech I','Tire Press','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R22','A33','S38','S38','Judy','Tech II','Tire Press','80%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A40','S41','S41','Mark','Tech I','Tire Press','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A40','S42','S42','Roger','Tech IV','Tire Press','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S44','S44','Alan','Sales I','Tire Press','83%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S45','S45','Strphen','Tech III','Tire Press','67%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S46','S46','Dale','Tech IV','Tire Press','98%',2) insert into RlfData (Hd, Dv, Re, Ar, St, Lv, Assoc, Rol, Title, Attend, Typ) values ('H01','D21','R39','A43','S47','S47','Danny','Sales II','Tire Press','99%',2) ================================================= Here is a query that returns the data for the matrix report ================================================= -- ================================================= -- RlfData - Combined Report - Level 2 (Dv) -- ================================================= use Test2 -- ================================================= -- RlfData - Reprt - Completed data (Parent) -- ================================================= Select distinct r.Lv, 'Percent completed for: ' + r.Lv Assoc, r.Title, convert(char(4),convert(varchar(15), round((case when r.Lv = r.Hd then hd.AtCnt when r.Lv = r.Dv then dv.AtCnt when r.Lv = r.Re then re.AtCnt when r.Lv = r.Ar then ar.AtCnt when r.Lv = r.St then st.AtCnt end +0.0)/(case when r.Lv = r.Hd then (select count(Assoc) TCnt from RlfData group by Hd) when r.Lv = r.Dv then (select count(Assoc) TCnt from RlfData where r.Lv = Dv group by Dv) when r.Lv = r.Re then (select count(Assoc) TCnt from RlfData where r.Lv = Re group by Re) when r.Lv = r.Ar then (select count(Assoc) TCnt from RlfData where r.Lv = Ar group by Ar) when r.Lv = r.St then (select count(Assoc) TCnt from RlfData where r.Lv = St group by St) end)*100,2)))+'%' completed, case when r.Lv = r.Hd then 1 when r.Lv = r.Dv then 2 when r.Lv = r.Re then 3 when r.Lv = r.Ar then 4 when r.Lv = r.St then 5 end Sort, case when r.Lv = r.Hd then null when r.Lv = r.Dv then r.Hd when r.Lv = r.Re then r.Dv when r.Lv = r.Ar then r.Re when r.Lv = r.St then r.Ar end Parent, 1 AssocType from RlfData r join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) hd on hd.Lv = r.Lv and r.Title = hd.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) dv on dv.Lv = r.Lv and r.Title = dv.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) re on re.Lv = r.Lv and r.Title = re.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) ar on ar.Lv = r.Lv and r.Title = ar.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) st on st.Lv = r.Lv and r.Title = st.Title Where r.Title in ('Sales','Tires') and r.Attend is not null and r.Lv like 'D%' --order by Sort, Lv UNION -- ================================================= -- RlfData - Report - Assoc data -- ================================================= Select --r.Hd, --r.Dv, --r.Re, --r.Ar, --r.St, r.Lv, Assoc [Assoc/Entity], r.Title, r.Attend [Attend/Aggregate], case when r.Lv = r.Hd then 1 when r.Lv = r.Dv then 2 when r.Lv = r.Re then 3 when r.Lv = r.Ar then 4 when r.Lv = r.St then 5 end Sort, case when r.Lv = r.Hd then null when r.Lv = r.Dv then r.Hd when r.Lv = r.Re then r.Dv when r.Lv = r.Ar then r.Re when r.Lv = r.St then r.Ar end Parent, 2 AssocType from RlfData r join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) hd on hd.Lv = r.Lv and r.Title = hd.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) dv on dv.Lv = r.Lv and r.Title = dv.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) re on re.Lv = r.Lv and r.Title = re.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) ar on ar.Lv = r.Lv and r.Title = ar.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) st on st.Lv = r.Lv and r.Title = st.Title Where r.Title in ('Sales','Tires') and r.Attend is not null and r.Lv like 'D%' UNION -- ================================================= -- RlfData - Reprt - Completed data (Parent) -- ================================================= Select distinct case when r.Lv = r.Hd then null when r.Lv = r.Dv then r.Hd when r.Lv = r.Re then r.Dv when r.Lv = r.Ar then r.Re when r.Lv = r.St then r.Ar end Parent, --r.Lv, 'Percent completed for: ' + r.Lv Assoc, r.Title, convert(char(4),convert(varchar(15), round((case when r.Lv = r.Hd then hd.AtCnt when r.Lv = r.Dv then dv.AtCnt when r.Lv = r.Re then re.AtCnt when r.Lv = r.Ar then ar.AtCnt when r.Lv = r.St then st.AtCnt end +0.0)/(case when r.Lv = r.Hd then (select count(Assoc) TCnt from RlfData group by Hd) when r.Lv = r.Dv then (select count(Assoc) TCnt from RlfData where r.Lv = Dv group by Dv) when r.Lv = r.Re then (select count(Assoc) TCnt from RlfData where r.Lv = Re group by Re) when r.Lv = r.Ar then (select count(Assoc) TCnt from RlfData where r.Lv = Ar group by Ar) when r.Lv = r.St then (select count(Assoc) TCnt from RlfData where r.Lv = St group by St) end)*100,2)))+'%' completed, case when r.Lv = r.Hd then 1-1 when r.Lv = r.Dv then 2-1 when r.Lv = r.Re then 3-1 when r.Lv = r.Ar then 4-1 when r.Lv = r.St then 5-1 end Sort, --case -- when r.Lv = r.Hd then null -- when r.Lv = r.Dv then r.Hd -- when r.Lv = r.Re then r.Dv -- when r.Lv = r.Ar then r.Re -- when r.Lv = r.St then r.Ar --end Parent, r.Lv, 3 AssocType from RlfData r join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) hd on hd.Lv = r.Lv and r.Title = hd.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) dv on dv.Lv = r.Lv and r.Title = dv.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) re on re.Lv = r.Lv and r.Title = re.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) ar on ar.Lv = r.Lv and r.Title = ar.Title join (select Lv, Title, count(Attend) AtCnt from RlfData Group By Lv,Title,Ar) st on st.Lv = r.Lv and r.Title = st.Title Where r.Title in ('Sales','Tires') and r.Attend is not null and r.Lv like 'R%' --order by Sort, Lv -- =========================================== -- order order by Sort, Lv, AssocType ================================================ Note in the retuned data the column "AssocType". I wish to sort the data in within row-group "Lv" by the column "AssocType" Here are the results returned: Lv, Assoc, Title, completed, Sort, Parent, AssocType D02, Percent completed for: D02, Tires, 2.27%, 2, H01, 1 D02, Wen, Tires, 83%, 2, H01, 2 D02, Percent completed for: R03, Sales, 5.56%, 2, R03, 3 D02, Percent completed for: R12, Sales, 4.17%, 2, R12, 3 D21, Percent completed for: D21, Sales, 1.89%, 2, H01, 1 D21, Larry, Sales, 3/2/2008, 2, H01, 2 D21, Percent completed for: R22, Sales, 2.86%, 2, R22, 3 D21, Percent completed for: R39, Sales, 6.25%, 2, R39, 3 In the matrix report that the groups are "Lv" and "Assoc" The column is "Title" And the data is "completed" The other columns are not displayed in the report The question is: How can I sort column-group "Assoc" by column "AssocType"? And by the way, how can I conditionally color column-group "Assoc" and data column "completed" using column "AssocType" Thank you, Doug
From: "Charles Wang [MSFT]" on 27 Jun 2008 06:12 Hello Doug, Thank you posting! This is a quick note to let you know that I am performing research on this issue and will get back to you as soon as possible. I appreciate your patience. Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg(a)microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================ This posting is provided "AS IS" with no warranties, and confers no rights. =========================================================
From: dbuchanan on 29 Jun 2008 21:58 Charles, Found a solution to my needs. In the Matrix I added the column which I needed to sort by as a row-group in front of the row-group I wanted sorted. Then I give the new column a zero width. It justed happened that this worked in my circumstances - although it may not work in some. Thanks, Doug
From: "Charles Wang [MSFT]" on 29 Jun 2008 22:16 Hi Doug, Thank you for your reply and the detailed additional feedback on how you were successful in resolving this issue. This information has been added to Microsoft's database. Your solution will benefit many other users, and we really value having you as a Microsoft customer. If you have any other questions or concerns, please do not hesitate to contact us. It is always our pleasure to be of assistance. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg(a)microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================ This posting is provided "AS IS" with no warranties, and confers no rights. =========================================================
|
Pages: 1 Prev: Fill Effects in SSRS Next: MOSS Deep Integration - Accessing Report Model |