Elementary School Report Cards

Posted May 29th, 2016 in PowerSchool, Project Management, SSRS by Kshitij

At AES, we transitioned our Elementary School from Veracross to PowerSchool this year while our Middle School was transitioned a year before.

One of the important milestone in this transition was semester-end report cards. Our previous report cards were based on Crystal Reports templates. As we migrated to PowerSchool and in the process adopted standards-based grading, a new solution was required. This post is my attempt to broadly outline the process and the lessons we learned in this project.

The Requirements
We were lucky to receive a basic design-prototype which gave us the direction to build the report cards. When you begin a project, it is important you know what the Admin team expects as an end-product. Of course there will be iterations but having a this direction is very helpful and equally helpful was to constantly seek feedback during the prototype development so as to close in any gaps in the requirement.

Here are the screenshots of initial design we received:

The Prototype
Based on the initial requirements we designed the report card as an Object Report in PowerSchool. It was easy and we used Visual PST for the design. However, we quickly came to realize the shortcomings. The key roadblocks we faced were in the following areas:

Grading Keys: Ours were standards-based grades were to be displayed as symbols and not numerical values which teachers were to enter through PowerTeacher Gradebook. Here is the screenshot of our grading key:

powerschool-grading-key
We started with pre-designed images for each level of grade-key so we could name them for each grade value and display them using the photo objects in the reports. It soon dawned that we can not dynamically create image tags for corresponding values of the standard grade-key since in Powerschool there is no way to attach a condition on a photo-object.

We could work around it by designing our own font – which is what we did – however, notwithstanding the formatting issues, there was another issue waiting to block our progression.

Displaying Teacher Comments: Our Middle School set up was done an year before our ES one and we used “assignments” as an object to award a grade upon for individual course in specific semester. The roadblock in ES context was that we cannot pull the “comments” on the assignments using data-tags that object reports uses. What!? Yeah, we could on the standard itself but not the comments that were made on assignments.

Homeroom Subjects: Homeroom subjects in our PowerSchool instance are not proper courses although Specials (P.E., Arts, Indian Studies, Music, World Languages) are. For example, HomeRoom is a course while Mathematics, Science, Social Studies etc. are not.

Attendance: There were some reliability issues related to attendance DATs and none of those specified in documentation seemed to work in our case, although I do like to believe these were specific to us, but in general my observation has been that many schools – if not all – faced similar problems.

All in all, given the complexities and limitations of this easier way, we decided object reports was not the best way forward in our case and chose the way our MS had done it, that is, using SQL server reports. Another factor that weighed in was the consistency of platform among all the three schools.

Prototype 2.0
While we shifted the platform to Visual Studio BIDS and SSRS, the key challenges remained:

  • Standards Grading Key:  We overcame this by designing a custom font using BirdFont.
  • Homerooms: no courses for Maths, Science, Social Studies etc. This was not a roadblock anymore given SSRS allows you to customize the SQL to pull specific data from Powerschool, nevertheless was challenging. We were able to modify the course SQLs to pull the data on matching keywords in the standards and got this going.

The End Product
Here is the PDF of the end-product with some pages redacted.

But it doesn’t end there…


Setting up the Gradebook
With the design done, there was one key operational issue that still needed to be addressed and is very much relevant to the report cards delivery: the gradebook setup.

It was impractical to expect teachers or their assistants to get down to creating semester categories, then assignments and then mapping the standards in the gradebook for teachers so they could grade students on them. Even by the standards of tech staff, this was a tedious job. But we found a solution…

We used Macros to achieve this task to minimize the technology overheard for the teachers and divisional tech. staff. Read more about this on Clint Carlson’s post on how he helped us achieve this.

Project Lessons:
Elementary Schools are known to have complex processes and procedures and working with them is always challenging, and at the same time a tremendous learning experience. I for one, definitely benefited by learning new systems like BIDS, Visual PST and advanced SQL usage.

Here are specific lessons I learned in project-management context:

  • Always have a design ready beforehand:
    • Be it a paper design or one done on a computer, it does not matter. Visualizing the end product is the key to identifying potential roadblocks and challenges earlier in the development lifecycle.
  • Work on a prototype first:
    • This will give you a fair idea of what is possible and what is not.
    • This will also help you identify the right way to design the report cards for your school. You basically touch the boundaries of what you can achieve with the available resources.
    • This will also keep a check on everyone’s expectations from the end product.
  • Freeze scope as soon possible:
    • Put a barrier as soon you freeze the scope and make it clear that it must be respected.
    • If you don’t that, the requests for modifications will keep pouring in particularly because, in K12 setup, we are dealing with people who have limited knowledge of how much effort it takes for what seems to be a small customization.
  • Be proactive and know the inner workings of the school
    • If you don’t know everything, get someone on-board who does. In our case, we were lucky to have an SIS expert who had a deep and very solid understanding of Elementary School processes and workings that allowed us to proactively look for possible points of failure.
    • Remember that school staff is not necessarily tech. savvy, so there is a lot of presumption on their part, especially the technical one. The onus thus lies on the technology team or the developers to fill any possible gaps in the requirements.
    • The more proactive you are at the beginning, the less painful it will be in the later stages of development.
  • Be prepared to be flexible:
    • Regardless of how hard you work to fill in the gaps, how smoothly you work to freeze the project scope, at the end of the day, there will be requests for modifications and alterations. While it is always a good practice to establish a hard deadline for any new requests to allow you time for fine-tuning, be prepared to handle those and keep enough time for your testing as well buffer to cater last minute requests.
  • Follow the agile model of development
    • This is the single most important lesson I learned in this project. Develop, get approved, gather feedback, iterate. That’s the best approach in international school landscape that is known to be highly unpredictable.