We’re in the middle of summer here in Western Australia and we are starting to see the spread of COVID-19 in our community. It’s 2022 and we’ve been quite lucky to live a relatively good mostly lockdown-free life so far in this pandemic. The need has arisen to have an easy way to find which students have been situated in the same class as a confirmed case of COVID-19.
We currently use Maze from Civica Education as our Student Information System. Maze uses a MSSQL database to store its data, and has a reporting front end where reports can be easily produced and retrieved by end users.
The project started with crafting a SQL query to fetch the required data. This looked at various tables including: Staff, Students, Families, Timetable Quilt and Student Timetables. From here, we can deduce the lessons that particular individual had on that day, the other students in those lessons and the contact details for the families associated with those students. This helps to create a notification register for all affected.
In the end the query looks a like this:
This query returns the following fields:
- Class code with the Staff Key appended. This is used to determine in Maze whether a new page should be printed with the alternate teacher. This is where the class might have a second teacher for one of the lessons.
- Student Information: Name, Birthdate, Gender, Roll Group
- Student Vaccination Status (This requires the Maze Covid Update which stores a Y OR N for double vaccination in the Students table).
- Mothers Information: Name, Mobile, Address
- Fathers Information: Name, Mobile, Address
- Teachers Name
- Students Name with the search day appended.
Now that the query was built, we can either use it like this in SQL or we can build a report in Maze for the end users. In Maze, we create query parameters as defined items, and from here we can reference them with [@{PARAM_NAME}] in the SQL query. Firstly, parameters for the student key, day and vaccination status were created. This look like the picture below:
Now that the parameters are created, the query needs to be modified slightly. In the Maze SQL Editor, remove the variables from the top of the query, then change @studentkey to [@studentkey], @daytocheck to [@query_day_raw] and finally @vaccination to [@vac_stat]. Maze requires square brackets to be placed around parameters for them to be used in the SQL query. Now the query will look like this:
Now that we have the data we need for the report, we need to create the format display for the report. In the report designer, we need two areas. One for the main data, and one that contains a page break. The page break area will contain a condition to only present when the CLASSIDENT key is different to the previous. This will result in a new page being printed for a different class. Once the fields are added to the report, the report designer will look like this:
Finally we need to add some report logic to perform the page break and to keep track of the CLASSIDENT key. To achieve this we need to create another parameter in defined items called @CURR_CLASSIDENT in the report properties. This will be used to keep track of the current CLASSIDENT key and compare it to the next.
Once the parameter is added, an area needs to be created that just contains a page break. This page break area will only show when the @CURR_CLASSIDENT key changes.
Finally, the report procedures are updated to set the @CURR_CLASSIDENT parameter to be the CLASSIDENT key.
That’s it! We now have a report to list all of the contacts a student has in a classroom with the family contact details for notification of a COVID-19 positive case. If you’d like to obtain a copy of the SQL query or the final Maze report, please don’t hesitate to contact me.