troubleshooting Question

SSRS Totals / Matrix Totals

Avatar of Daftwillie
Daftwillie asked on
Microsoft SQL ServerDB Reporting ToolsSSRS
5 Comments1 Solution628 ViewsLast Modified:
Hi all, this should be a nice meaty one for you.

I am currently writing what I consider to be quite a complex report.

To Start with My datasource is below.

The report is split by CensusYear, LEANo, SchoolID and CensusTerm on each page.

Within that I have little summary tables on various bits of data within this dataset.

It is most likely much easier for me to give you a quick screenshot.
The green parts are the bits I want to add.

The First bit is underneith the total number of students for that School I want to put the Average total number of students in a school accross the LEA.

The next part is where I am really struggling. Where you see I have the percentage breakdown for male and female students, I also want below that the percentages for the whole LEA (ie, school has 48% boys in reception year, the entire LEA has 56% boys). also the total % of students in the school regardless of year.

If I can work this out I should be able to work out how to do the other summary tables the same.


Can anyone help? I appreciate that its Christmas so I'm not expecting a fast response on this one :)

Thanks and Happy Christmas.
SELECT	ch.CensusYear
,	ch.CensusTerm
,	ch.ReferenceDate
,	cs.LEANo
,	lea.LEAName
,	cs.SchoolID
,	s.SchoolName
,	p.PupilID
,	p.PupilGender
,	y.NCYearGroupID
,	y.NCYearGroupName
,	CAST(ISNULL(pnr.GandTIndicator, 0) AS int) AS GandT
,	CAST(CASE pnr.SENProvision WHEN 'Z' THEN 1 ELSE 0 END AS int) AS SENStatement
,	CAST(CASE pnr.SENProvision WHEN 'Q' THEN 0 ELSE 1 END AS int) AS SEN
,	CAST(CASE p.LanguageCode WHEN 'ENG' THEN 0 ELSE 1 END AS int) AS EAL
,	y.SortOrder AS YearOrder
FROM	tb_CensusHeader ch
		INNER JOIN
	tb_CensusSchool cs
		ON ch.CensusHeaderID = cs.CensusHeaderID
		INNER JOIN
	tb_LocalEducationAuthority_lkp lea
		ON cs.LEANo = lea.LEANo
		INNER JOIN
	tb_School s
		ON cs.LEANo = s.LEANo
		AND cs.SchoolID = s.SchoolID
		INNER JOIN
	tb_PupilSchool_lnk psl
		ON s.LEANo = psl.LEANo
		AND s.SchoolID = psl.SchoolID
		INNER JOIN
	tb_Pupil p
		ON psl.PupilID = p.PupilID
		INNER JOIN
	tb_CensusPupilOnRoll pnr
		ON p.PupilID = pnr.PupilID
		AND ch.CensusHeaderID = pnr.CensusHeaderID
		INNER JOIN
	tb_NCYearGroup_lkp y
		ON pnr.NCYearActual = y.NCYearGroupID
WHERE	ch.CensusType = 'P'
AND	y.NCYearGroupID IN ('R ', '1 ', '2 ', '3 ', '4 ', '5 ', '6 ')
SSRS.jpg
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros