Test Scores and Demographics

Project Description

Our administrators were looking for a way to get test score information along side various demographics about the students to drive decisions on progress and how to better serve students.

Challenges

I was fairly new to my position when we were presented with this project. It originally started with trying to just build some SQL that can get all of that information. I was pretty new to SQL at the time, so having to contextually match the query over several columns just took a while to figure out.

Funnily enough, I start with the hardest test to get, which was NWEA MAP, since it is taken three times a year and you had four subjects to get from it. Over some time, I was able to get around 30 subqueries working and I had myself a monster SQL query.

We use a plugin to PowerSchool called sqlReports, which allow you to make a simpler report for users to run that creates a table from SQL. The big issue was that it was not really dynamic. So if you are looking at the report in October, you would see empty columns for winter and spring tests. So then that leads to the next challenge.

You can create custom pages in PowerSchool as well. With the use of something called tlist, you can include SQL queries and display things similar to sqlReports. This opened up the option of using JavaScript, which made me much more comfortable. I work on making the table dynamic, hiding unwanted columns, coloring the scores, and dynamically grabbing information based on the school year you are in.

Iterations

Once I finished the hardest page, I basically repeated the process for two other pages.

The finished pages haven't had many updates, besides one. On the page, I included a button to download it as a CSV. But since I did such a good job at styling the table, when they downloaded the CSV, people were upset that it wasn't in color.

So I figured out a way to create a stylized Excel file, equipped with coloring, hiding columns, etc. So now, admins can download a file that would mimic what they see on that screen.