Use correct class level names and sort order in reports

Lots of very nice work in these reports; however this change will make them a bit more reusable in other contexts:

Looking at "School enrolments by EdLevelGender" we have this data source:

SELECT E.[schNo]
      ,[schName]
      ,[surveyYear]
      ,[ClassLevel]
      ,[GenderCode]
      ,SUM([Enrol]) AS Enrollments
  FROM [warehouse].[enrol] E INNER JOIN Schools S ON E.schNo = S.schNo
  WHERE E.[schNo] = $P{SchoolNo} AND [surveyYear] = $P{Year}
  GROUP BY E.[schNo]
      ,[schName]
      ,[surveyYear]
      ,[ClassLevel]
      ,[GenderCode]
  ORDER BY CASE 
           WHEN [ClassLevel] = 'GPRES' THEN 1
           WHEN [ClassLevel] = 'GPREK' THEN 1
           WHEN [ClassLevel] = 'GK' THEN 2
           WHEN [ClassLevel] = 'G1' THEN 3
           WHEN [ClassLevel] = 'G2' THEN 4
           WHEN [ClassLevel] = 'G3' THEN 5
           WHEN [ClassLevel] = 'G4' THEN 6
           WHEN [ClassLevel] = 'G5' THEN 7
           WHEN [ClassLevel] = 'G6' THEN 8
           WHEN [ClassLevel] = 'G7' THEN 9
           WHEN [ClassLevel] = 'G8' THEN 10
           WHEN [ClassLevel] = 'G9' THEN 11
           WHEN [ClassLevel] = 'G10' THEN 12
           WHEN [ClassLevel] = 'G11' THEN 13
           WHEN [ClassLevel] = 'G12' THEN 14
           ELSE 15
         END

Use lkpLevels, or better still dimensionLevel to get all the data about the class level, including its logical order - ie its year of education.

This version of the query still works for FSM, but can be used anywhere.

SELECT E.[schNo]
      ,[schName]
      ,[surveyYear]
      ,DL.Level ClassLevel
      ,[GenderCode]
      ,SUM([Enrol]) AS Enrollments
  FROM [warehouse].[enrol] E 
    INNER JOIN Schools S 
        ON E.schNo = S.schNo
    INNER JOIN DimensionLevel DL
        ON E.ClassLevel = DL.LevelCode
  WHERE E.[schNo] = $P{SchoolNo} AND [surveyYear] = $P{Year}
  GROUP BY E.[schNo]
      ,[schName]
      ,[surveyYear]
      ,DL.Level
      , DL.[Year of Education]
      ,[GenderCode]
  ORDER BY DL.[Year of Education], GenderCode

Comments (4)