How to resolve layout of report in Reporting Services or SQL Server 2005 query

WITH base AS (
SELECT     
dbo.DimCxsClients.LeadLEA, 
dbo.DimPostcodes.[District Name] AS District, 
dbo.DimCxsClients.Gender, 
dbo.DimCxsClientsIndCircs.TeenageMother, 
dbo.DimCxsClients.SENStatus, 
dbo.DimCxsClientsIndCircs.CareLeaver, 
dbo.DimCxsClientsIndCircs.[SupervisedYOT-Ever], 
COUNT(dbo.DimCxsClients.AspireID) AS TotalCohort, 
SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS TotalNEETAvail, 
SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS TotalEET, 
SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNEETAvail, 
SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS TotalCurrencyEET, 
SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS TotalNEETNA, 
SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS TotalCurrencyNEETNA, 
SUM(CASE [EET/NEETCategory] WHEN 'Not EET or NEET' THEN 1 ELSE 0 END) AS TotalNotEETorNEET, 
SUM(CASE [EET/NEETCategory] WHEN 'Currency Not EET or NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNotEETorNEET, 
SUM(CASE [EET/NEETCategory] WHEN 'Not Known (NI)' THEN 1 ELSE 0 END) AS [TotalNotKnown(NI)], 
SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS TotalInLearning
FROM          
dbo.DimCxsClients INNER JOIN
dbo.DimPostcodes ON dbo.DimCxsClients.StatsPostcodeKey = dbo.DimPostcodes.PostcodeKey LEFT OUTER JOIN
dbo.DimCxsClientsIndCircs ON dbo.DimCxsClients.IndCircKey = dbo.DimCxsClientsIndCircs.IndCircKey
WHERE      
(dbo.DimCxsClients.IsCurrentRow = 'true') AND 
(dbo.DimCxsClients.Cohort = 'postcomped') AND 
(dbo.DimCxsClients.LeadLEA IN ('staffordshire', 'stoke')) AND 
(dbo.DimCxsClients.Age BETWEEN 16 AND 18)
GROUP BY 
dbo.DimCxsClients.LeadLEA, 
dbo.DimPostcodes.[District Name], 
dbo.DimCxsClients.Gender, 
dbo.DimCxsClientsIndCircs.TeenageMother, 
dbo.DimCxsClients.SENStatus, 
dbo.DimCxsClientsIndCircs.CareLeaver, 
dbo.DimCxsClientsIndCircs.[SupervisedYOT-Ever]) 

, basecalculate AS (
SELECT     
LeadLEA, 
District, 
Gender, 
TeenageMother, 
SENStatus, 
CareLeaver, 
[SupervisedYOT-Ever], 
SUM(TotalCohort) AS [Cohort16-18], 
SUM(TotalEET) AS [EET16-18], 
SUM(TotalCurrencyEET) AS [EET$16-18], 
SUM((TotalEET + 0.92 * TotalCurrencyEET) + 0.42 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)) AS [EETAdj16-18], 
SUM(TotalNEETAvail + TotalNEETNA) AS [NEET16-18], 
SUM(TotalCurrencyNEETAvail + TotalCurrencyNEETNA) AS [NEET$16-18], 
SUM(((TotalNEETAvail + TotalNEETNA) + 0.08 * TotalCurrencyEET) + 0.58 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)) AS [NEETAdj16-18], 
SUM([TotalNotKnown(NI)]) AS [OSNK16-18], 
SUM(TotalCurrencyNEETAvail + TotalCurrencyNEETNA + TotalCurrencyEET + TotalCurrencyNotEETorNEET) AS [TotalCurrency16-18], 
SUM(TotalNEETNA) AS [NEETNA16-18], 
SUM(TotalInLearning) AS [InLearning16-18]    
FROM base
GROUP BY 
LeadLEA, 
District, 
Gender, 
TeenageMother, 
SENStatus, 
CareLeaver, 
[SupervisedYOT-Ever])

, calculatetotals AS(
SELECT     
LeadLEA, 
District, 
Gender, 
TeenageMother, 
SENStatus, 
CareLeaver, 
[SupervisedYOT-Ever], 
[Cohort16-18], 
[EET16-18], 
[EETAdj16-18], 
[NEET16-18], 
[NEETAdj16-18], 100.0 * [NEET16-18] / [Cohort16-18] AS [NEET%16-18], 
CASE WHEN [NEETAdj16-18] = 0 THEN 0 ELSE (100.0 * [NEETAdj16-18]) / ([EETAdj16-18] + [NEETAdj16-18]) END AS [NEETAdj%16-18], 
[NEETNA16-18], 
CASE WHEN [NEETNA16-18] = 0 THEN 0 ELSE (100.0 * [NEETNA16-18] / [NEET16-18]) END AS [NEETNA%16-18], 
[EET$16-18], 
[NEET$16-18], 
[OSNK16-18], 
[TotalCurrency16-18] + [OSNK16-18] AS [NK16-18], 
100.0 * ([TotalCurrency16-18] + [OSNK16-18]) / [Cohort16-18] AS [NK%16-18], 
[InLearning16-18], 100.0 * [InLearning16-18] / [Cohort16-18] AS [InLearning%16-18]
FROM          
basecalculate 
GROUP BY 
LeadLEA, 
District, 
Gender, 
TeenageMother, 
SENStatus, 
CareLeaver, 
[SupervisedYOT-Ever], 
[Cohort16-18], 
[EET16-18], 
[EETAdj16-18], 
[NEET16-18], 
[NEETAdj16-18], 
[NEETNA16-18], 
[EET$16-18], 
[NEET$16-18], 
[OSNK16-18], 
[TotalCurrency16-18], 
[InLearning16-18]
)

SELECT     
LeadLEA, 
District, 
Gender, 
TeenageMother, 
SENStatus, 
CareLeaver, 
[SupervisedYOT-Ever], 
[NEETAdj16-18] 
FROM         
calculatetotals
GROUP BY 
LeadLEA, District, Gender, TeenageMother, SENStatus, CareLeaver, [SupervisedYOT-Ever], [NEETAdj16-18]
ORDER BY 
LeadLEA, District

Select allOpen in new window