Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSRS Totals / Matrix Totals

Posted on 2009-12-24
6
Medium Priority
?
593 Views
Last Modified: 2012-05-08
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 ')

Open in new window

SSRS.jpg
0
Comment
Question by:Daftwillie
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 26121238
with matrix reports to avoid a lot of problems I do all the work inside SQL
 
All the totals and especially percents I do as unions and add columns field to place it in the proper place.

So try to add a union to sql that will give you the results you need.

You will have to add a 3 union to get the totals since you will have to shut off the matirx total fields

 
0
 
LVL 1

Author Comment

by:Daftwillie
ID: 26339200
I have not closed this question because I have not yet had a response I feel answers my original query.
0
 
LVL 14

Accepted Solution

by:
Emes earned 1000 total points
ID: 26351970
Here is a sample of what may work.

First of all take just get the data without totals first

just take the data you need in a simple sql statement.
The one above seems like you are grabbing to much data
epically for a matrix report.

simplify the sql so the data will look like


your data would look like
school,reception,'F',50.2
school,reception,'M',59.8
school,year1,'F',51.4
school,year1,'M',48.6
school,year2,'F',50
school,year2,'M',50


hope this will get you going in the right direction.



0
 
LVL 1

Author Comment

by:Daftwillie
ID: 26358208
This is how I got the results I was looking for. It ended up being a rather monsterous SQL statement to ensure I got all the headings and to get the all figures as well as the overall figures for the lea (I used the averages for these). The parameter is in there because I want to be able to select by school year and only be shown the counts for those years and the schools that have pupils in those years.

If anyone can shorten this SQL that would be great, but so far it works. Thanks you Emes, you did get me in the right direction :)
SELECT	s.LEANo
	,	lea.LEAName
	,	s.SchoolID
	,	s.SchoolName
	,	ch.CensusYear
	,	ch.CensusTerm
	,	p.PupilGender
	,	pnr.NCYearActual
	,	y.NCYearGroupName
	,	y.SortOrder AS NCYearGroupSort
	,	SUM(1) AS PupilCount
FROM	tb_School s
			INNER JOIN
		tb_CensusSchool cs
			ON s.LEANo = cs.LEANo
			AND s.SchoolID = cs.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
			INNER JOIN
		tb_CensusHeader ch
			ON cs.CensusHeaderID = ch.CensusHeaderID
			AND pnr.CensusHeaderID = ch.CensusHeaderID
			INNER JOIN
		tb_NCYearGroup_lkp y
			ON pnr.NCYearActual = y.NCYearGroupID
			INNER JOIN
		tb_LocalEducationAuthority_lkp lea
			ON s.LEANo = lea.LEANo
WHERE	(@prmYearGroup IS NULL OR 
		 y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
GROUP BY	
		s.LEANo
	,	lea.LEAName
	,	s.SchoolID
	,	s.SchoolName
	,	ch.CensusYear
	,	ch.CensusTerm
	,	p.PupilGender
	,	pnr.NCYearActual
	,	y.NCYearGroupName
	,	y.SortOrder
UNION ALL
SELECT	DISTINCT
		s.LEANo
	,	lea.LEAName
	,	s.SchoolID
	,	s.SchoolName
	,	ch.CensusYear
	,	ch.CensusTerm
	,	'F'
	,	pnr.NCYearActual
	,	y.NCYearGroupName
	,	y.SortOrder
	,	0
FROM	tb_School s
			INNER JOIN
		tb_CensusSchool cs
			ON s.LEANo = cs.LEANo
			AND s.SchoolID = cs.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
			INNER JOIN
		tb_CensusHeader ch
			ON cs.CensusHeaderID = ch.CensusHeaderID
			AND pnr.CensusHeaderID = ch.CensusHeaderID
			INNER JOIN
		tb_NCYearGroup_lkp y
			ON pnr.NCYearActual = y.NCYearGroupID
			INNER JOIN
		tb_LocalEducationAuthority_lkp lea
			ON s.LEANo = lea.LEANo
WHERE	(@prmYearGroup IS NULL OR 
		 y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
UNION ALL
SELECT	DISTINCT
		s.LEANo
	,	lea.LEAName
	,	s.SchoolID
	,	s.SchoolName
	,	ch.CensusYear
	,	ch.CensusTerm
	,	'M'
	,	pnr.NCYearActual
	,	y.NCYearGroupName
	,	y.SortOrder
	,	0
FROM	tb_School s
			INNER JOIN
		tb_CensusSchool cs
			ON s.LEANo = cs.LEANo
			AND s.SchoolID = cs.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
			INNER JOIN
		tb_CensusHeader ch
			ON cs.CensusHeaderID = ch.CensusHeaderID
			AND pnr.CensusHeaderID = ch.CensusHeaderID
			INNER JOIN
		tb_NCYearGroup_lkp y
			ON pnr.NCYearActual = y.NCYearGroupID
			INNER JOIN
		tb_LocalEducationAuthority_lkp lea
			ON s.LEANo = lea.LEANo
WHERE	(@prmYearGroup IS NULL OR 
		 y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
UNION ALL
SELECT	a.LEANo
	,	lea.LEAName
	,	sch.SchoolID
	,	'zzz' + lea.LEAName AS SchoolName
	,	a.CensusYear
	,	a.CensusTerm
	,	a.PupilGender
	,	sch.NCYearActual
	,	y.NCYearGroupName
	,	y.SortOrder AS NCYearGroupSort
	,	a.PupilCount
FROM
	(
	SELECT	AVG(PupilCount) AS PupilCount
		,	LEANo
		,	CensusYear
		,	CensusTerm
		,	PupilGender
		,	NCYearActual
	FROM	
			(
			SELECT	s.LEANo
				,	s.SchoolID
				,	s.SchoolName
				,	ch.CensusYear
				,	ch.CensusTerm
				,	p.PupilGender
				,	pnr.NCYearActual
				,	SUM(1) AS PupilCount
			FROM	tb_School s
						INNER JOIN
					tb_CensusSchool cs
						ON s.LEANo = cs.LEANo
						AND s.SchoolID = cs.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
						INNER JOIN
					tb_CensusHeader ch
						ON cs.CensusHeaderID = ch.CensusHeaderID
						AND pnr.CensusHeaderID = ch.CensusHeaderID
			GROUP BY	
					s.LEANo
				,	s.SchoolID
				,	s.SchoolName
				,	ch.CensusYear
				,	ch.CensusTerm
				,	p.PupilGender
				,	pnr.NCYearActual
			) cnt
	GROUP BY
			LEANo
		,	CensusYear
		,	CensusTerm
		,	PupilGender
		,	NCYearActual
	) a
		INNER JOIN
	(
	SELECT	DISTINCT
			s.SchoolID
		,	pnr.NCYearActual
	FROM	tb_School s
				INNER JOIN
			tb_CensusSchool cs
				ON s.LEANo = cs.LEANo
				AND s.SchoolID = cs.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
				INNER JOIN
			tb_CensusHeader ch
				ON cs.CensusHeaderID = ch.CensusHeaderID
				AND pnr.CensusHeaderID = ch.CensusHeaderID
				INNER JOIN
			tb_NCYearGroup_lkp y
				ON pnr.NCYearActual = y.NCYearGroupID
				INNER JOIN
			tb_LocalEducationAuthority_lkp lea
				ON s.LEANo = lea.LEANo
	WHERE	(@prmYearGroup IS NULL OR 
			 y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
	) sch
		ON a.NCYearActual = sch.NCYearActual
		INNER JOIN
	tb_NCYearGroup_lkp y
		ON a.NCYearActual = y.NCYearGroupID
		INNER JOIN
	tb_LocalEducationAuthority_lkp lea
		ON a.LEANo = lea.LEANo
UNION ALL
SELECT	s.LEANo
	,	lea.LEAName
	,	s.SchoolID
	,	s.SchoolName
	,	ch.CensusYear
	,	ch.CensusTerm
	,	p.PupilGender
	,	'All' AS NCYearActual
	,	'All' AS NCYearGroupName
	,	0 AS NCYearGroupSort
	,	SUM(1) AS PupilCount
FROM	tb_School s
			INNER JOIN
		tb_CensusSchool cs
			ON s.LEANo = cs.LEANo
			AND s.SchoolID = cs.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
			INNER JOIN
		tb_CensusHeader ch
			ON cs.CensusHeaderID = ch.CensusHeaderID
			AND pnr.CensusHeaderID = ch.CensusHeaderID
			INNER JOIN
		tb_NCYearGroup_lkp y
			ON pnr.NCYearActual = y.NCYearGroupID
			INNER JOIN
		tb_LocalEducationAuthority_lkp lea
			ON s.LEANo = lea.LEANo
WHERE	(@prmYearGroup IS NULL OR 
		 y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
GROUP BY	
		s.LEANo
	,	lea.LEAName
	,	s.SchoolID
	,	s.SchoolName
	,	ch.CensusYear
	,	ch.CensusTerm
	,	p.PupilGender
UNION ALL
SELECT	av.LEANo
	,	av.LEAName
	,	sch.SchoolID
	,	'zzz' + av.LEAName AS SchoolName
	,	av.CensusYear
	,	av.CensusTerm
	,	av.PupilGender
	,	'ALL'
	,	'ALL'
	,	0
	,	av.PupilCount
FROM	(
		SELECT	AVG(PupilCount) As PupilCount
			,	a.LEANo
			,	a.LEAName
			,	a.CensusYear
			,	a.CensusTerm
			,	a.PupilGender
		FROM	(
				SELECT	s.LEANo
					,	lea.LEAName
					,	s.SchoolID
					,	s.SchoolName
					,	ch.CensusYear
					,	ch.CensusTerm
					,	p.PupilGender
					,	'All' AS NCYearActual
					,	'All' AS NCYearGroupName
					,	0 AS NCYearGroupSort
					,	SUM(1) AS PupilCount
				FROM	tb_School s
							INNER JOIN
						tb_CensusSchool cs
							ON s.LEANo = cs.LEANo
							AND s.SchoolID = cs.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
							INNER JOIN
						tb_CensusHeader ch
							ON cs.CensusHeaderID = ch.CensusHeaderID
							AND pnr.CensusHeaderID = ch.CensusHeaderID
							INNER JOIN
						tb_NCYearGroup_lkp y
							ON pnr.NCYearActual = y.NCYearGroupID
							INNER JOIN
						tb_LocalEducationAuthority_lkp lea
							ON s.LEANo = lea.LEANo
				WHERE	(@prmYearGroup IS NULL OR 
						y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
				GROUP BY	
						s.LEANo
					,	lea.LEAName
					,	s.SchoolID
					,	s.SchoolName
					,	ch.CensusYear
					,	ch.CensusTerm
					,	p.PupilGender
				) a
		GROUP BY
				a.LEANo
			,	a.LEAName
			,	a.CensusYear
			,	a.CensusTerm
			,	a.PupilGender
		) av
			INNER JOIN
		(
		SELECT	DISTINCT
				ch.CensusYear
			,	ch.CensusTerm
			,	s.SchoolID
		FROM	tb_School s
					INNER JOIN
				tb_CensusSchool cs
					ON s.LEANo = cs.LEANo
					AND s.SchoolID = cs.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
					INNER JOIN
				tb_CensusHeader ch
					ON cs.CensusHeaderID = ch.CensusHeaderID
					AND pnr.CensusHeaderID = ch.CensusHeaderID
					INNER JOIN
				tb_NCYearGroup_lkp y
					ON pnr.NCYearActual = y.NCYearGroupID
					INNER JOIN
				tb_LocalEducationAuthority_lkp lea
					ON s.LEANo = lea.LEANo
		WHERE	(@prmYearGroup IS NULL OR 
				y.NCYearGroupName IN (SELECT * FROM dbo.SplitParameterValues(@prmYearGroup, ',')))
		) sch
			ON av.CensusYear = sch.CensusYear
			AND av.CensusTerm = sch.CensusTerm

Open in new window

0
 
LVL 1

Author Closing Comment

by:Daftwillie
ID: 31669768
Question answered myself. This solution helped point me in that general direction
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question