JasperReports Simplify All Pupil Grid Reports

[Ghislain Hachey]

There are a lot of reports (and sub-reports) that make use of non-flexible queries to generate pupil by class level (e.g. Grade 1, Grade 2) grids. The issue with these queries is they must be managed by each different countries with different class levels. Also a change in the configuration would require a change in the report definition and every other place that data is presented. In this issue, we significantly improve this.

[Ghislain Hachey]

I'll explain further with an example. The report Students\Student Enrol by Age Level EA.jrxml makes use of the following SQL queries to retrieve the grid-like data.

SELECT 
       a.authName as authName,
       ee.enAge as Age,
       SUM(CASE WHEN ee.enLevel ='GPRES' THEN COALESCE(ee.enF,0) ELSE 0 END) as GPRES_F,
       SUM(CASE WHEN ee.enLevel ='GK' THEN COALESCE(ee.enF,0) ELSE 0 END) as GK_F,
       SUM(CASE WHEN ee.enLevel ='G1' THEN COALESCE(ee.enF,0) ELSE 0 END) as G1_F,
       SUM(CASE WHEN ee.enLevel ='G2' THEN COALESCE(ee.enF,0) ELSE 0 END) as G2_F,
       SUM(CASE WHEN ee.enLevel ='G3' THEN COALESCE(ee.enF,0) ELSE 0 END) as G3_F,
       SUM(CASE WHEN ee.enLevel ='G4' THEN COALESCE(ee.enF,0) ELSE 0 END) as G4_F,
       SUM(CASE WHEN ee.enLevel ='G5' THEN COALESCE(ee.enF,0) ELSE 0 END) as G5_F,
       SUM(CASE WHEN ee.enLevel ='G6' THEN COALESCE(ee.enF,0) ELSE 0 END) as G6_F,
       SUM(CASE WHEN ee.enLevel ='G7' THEN COALESCE(ee.enF,0) ELSE 0 END) as G7_F,
       SUM(CASE WHEN ee.enLevel ='G8' THEN COALESCE(ee.enF,0) ELSE 0 END) as G8_F,
       SUM(CASE WHEN ee.enLevel ='G9' THEN COALESCE(ee.enF,0) ELSE 0 END) as G9_F,
       SUM(CASE WHEN ee.enLevel ='G10' THEN COALESCE(ee.enF,0) ELSE 0 END) as G10_F,
       SUM(CASE WHEN ee.enLevel ='G11' THEN COALESCE(ee.enF,0) ELSE 0 END) as G11_F,
       SUM(CASE WHEN ee.enLevel ='G12' THEN COALESCE(ee.enF,0) ELSE 0 END) as G12_F,
       SUM(CASE WHEN ee.enLevel IN ('GPRES','GK','G1','G2','G3','G4','G5','G6','G7','G8','G9','G10','G11','G12') THEN COALESCE(ee.enF,0) ELSE 0 END) as Total_F,
       SUM(CASE WHEN ee.enLevel ='GPRES' THEN COALESCE(ee.enM,0) ELSE 0 END) as GPRES_M,
       SUM(CASE WHEN ee.enLevel ='GK' THEN COALESCE(ee.enM,0) ELSE 0 END) as GK_M,
       SUM(CASE WHEN ee.enLevel ='G1' THEN COALESCE(ee.enM,0) ELSE 0 END) as G1_M,
       SUM(CASE WHEN ee.enLevel ='G2' THEN COALESCE(ee.enM,0) ELSE 0 END) as G2_M,
       SUM(CASE WHEN ee.enLevel ='G3' THEN COALESCE(ee.enM,0) ELSE 0 END) as G3_M,
       SUM(CASE WHEN ee.enLevel ='G4' THEN COALESCE(ee.enM,0) ELSE 0 END) as G4_M,
       SUM(CASE WHEN ee.enLevel ='G5' THEN COALESCE(ee.enM,0) ELSE 0 END) as G5_M,
       SUM(CASE WHEN ee.enLevel ='G6' THEN COALESCE(ee.enM,0) ELSE 0 END) as G6_M,
       SUM(CASE WHEN ee.enLevel ='G7' THEN COALESCE(ee.enM,0) ELSE 0 END) as G7_M,
       SUM(CASE WHEN ee.enLevel ='G8' THEN COALESCE(ee.enM,0) ELSE 0 END) as G8_M,
       SUM(CASE WHEN ee.enLevel ='G9' THEN COALESCE(ee.enM,0) ELSE 0 END) as G9_M,
       SUM(CASE WHEN ee.enLevel ='G10' THEN COALESCE(ee.enM,0) ELSE 0 END) as G10_M,
       SUM(CASE WHEN ee.enLevel ='G11' THEN COALESCE(ee.enM,0) ELSE 0 END) as G11_M,
       SUM(CASE WHEN ee.enLevel ='G12' THEN COALESCE(ee.enM,0) ELSE 0 END) as G12_M,
       SUM(CASE WHEN ee.enLevel IN ('GPRES','GK','G1','G2','G3','G4','G5','G6','G7','G8','G9','G10','G11','G12') THEN COALESCE(ee.enM,0) ELSE 0 END) as Total_M,
        SUM(CASE WHEN ee.enLevel IN ('GPRES','GK','G1','G2','G3','G4','G5','G6','G7','G8','G9','G10','G11','G12') THEN COALESCE(ee.enSum,0) ELSE 0 END) as Grand_Total
FROM pEnrolmentRead.EstimateEnrolment ee
     INNER JOIN dbo.Schools s ON s.schNo = ee.schNo
     LEFT JOIN dbo.Authorities a ON a.authCode = s.schAuth
WHERE ee.LifeYear = 2018 
GROUP BY ee.enAge,a.authName
ORDER BY a.authName, ee.enAge

Yielding data that looks like this:

And polished into a JasperReports that looks like this:

As you see, Class Levels (Grade 1, etc.) are hard coded in the SQL query, non-flexible and needed to be adjusted by country or for any changes in them over time. It's also a large unpleasant query that can be abstracted away. A recent change in the system now enables the construction of such data grids very flexibly and easily. Instead of the above query you can do the following SQL Query instead:

exec warehouse.GridMaker 'A', 2018

And you would get essentially the same data. Note you may have to rename a few of the fields. Many examples of how to use this warehouse.GridMaker SQL stored procedure can be found directly in the SQL code find the procedure and right click "Modify" to see the code with comments and examples.

The task for this issue is to locate every report in both fedemis/miemis folders and replace the query with the simpler more flexible new one. Adjust any minor things needed adjusted (e.g. the field name Total_M to Tot_M) and that's it. This will be all the reports (and subreports) that have a pupil by class level grid like in the example above. There are several. Note this is not only for student enrolments but will also work with other types of pupil by class level grids like with repeaters, dropouts, etc.

Finally, going forward, anytime there is a reporting need on such pupil by class level this procedure should be used.

The same procure will be used to build a neat little RESTful API for a wealth of enrolment and other pupil data grid.

[Ghislain Hachey]

I just noticed that there are already warehouse views ready for some of the reports:

• warehouse.EnrolGrid
• warehouse.EnrolGridDistrict
• warehouse.EnrolGridAuthority
• warehouse.EnrolGridSchoolType

Refer to comments by Brian here also #578

[Brian Lewis]

warehouse.EnrolGrd etc are a little different - they group by year of education rather than class level. this makes a bit of difference in RMI because we have 2 class levels (G8 GPRE9) with the same Yearof Educaiton = 8

So I think the GridMaker proc will be more generally useful.

[Shraddha Tambe]

@ghacheytest We have verified all the reports and found the below reports which are showing the data by ‘ClassLevel’ and using hardcoded ‘ClassLevel’ in the query.

Students\Student Enrol by Age Level EA
Students\Student Enrol Distribution by Age Grade
Students\Student Enrol Distribution by Age Grade Female
Students\Student Enrol Distribution by Age Grade Male

We have updated the query to use ‘GridMaker’ procedure in the above reports.