Skip to content

BI Tool: A-G Tracker

Written by
September 16th, 2019

The Illuminate BI Tool Blog is back! We hope everyone had a wonderful summer and is at least surviving the start of the new school year. It’s a hectic time of year for educators, including us at Illuminate, to put it mildly. I believe deeply in the power of technology to help solve problems, but I’ve also been around long enough to know that technology is never going to calm the frantic pace at which educators have to react and respond to the relentless stream of problems and opportunities that they’re faced with every hour of every day. That said, I’m convinced that technology can help us avoid squandering our time and allow us to dedicate our energy to what matters most. That’s where technology, like Illuminate’s BI Tool, can help.

Case in point, take a look at the remarkable “A-G Tracker” BI Tool Report that David Jansson created at Cutler-Orosi Joint Unified School District. David’s report makes fantastic use of color, icons, and drill down to visualize a student’s progress towards meeting the A-G requirements. In his post below, David describes his head counselor “often pouring over transcripts by hand.” Now, as a result of David’s work with the BI Tool, all of COJUSD’s counselors can dedicate their energy to what matters most, andSpoiler Alert!it’s definitely not parsing through transcripts by hand!

This is the kind of report every high school wishes it had. It’s the kind of report that requires careful, custom configuration to ensure that the definitions of “On Track,” “Needs Attention,” and “Off Track” are aligned with the expectations of the school and their counselors. A tremendous amount of nuance had to go into creating the conditional logic for this report to ensure the right students were flagged at the right time for the right reasons. This report is a wonderful example of a report that really takes advantage of the power of Illuminate’s BI Tool to do this kind of custom reporting and data visualization. Great job David! This is one of David’s first BI Tool reports, and I can’t wait to see what he does next.

And remember: for those of us who just don’t have the time or technical prowess to build BI Tool Reports, please keep in mind that Illuminate also offers a Report Building Service. You can contract our team of experts to build the custom BI Tool reports that your district needs. We’ll work hand-in-hand with your team to make sure your vision comes to life and your reporting and data visualization needs are met. Email, and we’ll set up a meeting to discuss your reporting needs.

Please join Illuminate’s BI Tool User Community to collaborate with Illuminate BI Tool users throughout the country. Simply follow this link and click the “Follow” button at the top. (To participate in the forum, login to Illuminate, click your “Control Panel,” select “Illuminate Help Center,” and click on “Community.”) If you are interested in contributing to the BI Tool Blog, please email me at:


Franck Reyherme
Director of Business Intelligence Services
Illuminate Education


A-G Tracker

By: David Jansson, Instructional Data Specialist, Cutler-Orosi Joint Unified School District

I am the Instructional Data Specialist for Cutler-Orosi Joint Unified School District, a small, rural school district of about 4,200 students in the heart of California’s Central Valley. I was formerly an English teacher at our high school, but my irrepressible love for math and computers led me to dabble in IT and, eventually, arrive in this position within our district office.

From the moment I began in my position a year and a half ago, our high school counselors have been requesting a report that shows student progress toward completing their CSU/UC A-G requirements. There was no available option to quickly assess when a student was falling off-track, and our head counselor was often pouring over transcripts by hand. Once I received training on the BI Tool, I knew it could help. This summer, I finally had enough downtime to begin digging into the BI Tool, and I created the “A-G Tracker.”

Designing this report was a fun challenge because I wanted to balance the counselors’ desire for a quick overview with the ability to dig deeper as needed. To accomplish that task, I designed the report to give three levels of information.

The first level of information is the colored box around the student’s progress. It is color-coded using a standard green-yellow-red scheme where a green box indicates a student is doing fine and is on-track to meet the requirements, yellow alerts the counselors that investigation is advised, and red warns that the student requires immediate intervention to be able to meet the requirements. The goal of this level is that a counselor could quickly scan down the list to get a general impression of where the students are at.

The second level of information in the report comes from the indicators inside each box, which break down the student’s standing in each individual A-G category. If a counselor wants to know why an individual student received a yellow or red box, they can pause and briefly see where the student is in each area. For this level, I continued the same color scheme but added the use of different icons to better differentiate the progress.

Finally, if a counselor wants to do a deep dive on an individual student, I used a drill down report so that she can click on the student’s ID and get a list of the student’s transcript courses and grades for each category. The same color-coding and symbols are used for consistent understanding.

Overall, this was a fun report to get my feet wet with what the BI Tool can help do. The biggest challenge I faced was figuring out the logic of how the report would evaluate a student’s progress. The report is meant to be used for any high school student, and there are different expectations of completed credits for a first-semester sophomore than for a second-semester junior. This required a lot of advance planning as I had to map out where a student should be (credit-wise) toward each of the categories by each semester of their high school career while also taking into account natural variations of schedules. For example, how much did I want to penalize a student who didn’t take a foreign language their freshman year even though they should have plenty of time left to complete the requirements? At what point does the scale tip from “probably fine” to “pay attention?” In conversation with our counseling staff, these were decisions I had to make.

Just as in woodworking, the motto is “Measure twice; cut once,” so in report building the motto should be “Plan twice; build once.” This matters because, while my planning worked for the most part, there were a couple times after I began building the report that I realized something wasn’t going to work, and it took a lot more backtracking and careful insertion of code than if I had done it right the first time.

Moving forward, my next steps are to use the concepts I learned building the A-G Tracker to make other kinds of tracking reports, whether it for graduation progress or CTE pathway completion. I also look forward to learning SQL so that I can incorporate grades from the current semester rather than solely looking at transcript data.

Creating opportunities for efficiency like the A-G Tracker are one of the best ways I know I can use Illuminate and the BI Tool to support my staff.


Illuminate Education partners with K-12 educators to equip them with data to serve the whole child and reach new levels of student performance. Our solution brings together holistic data and collaborative tools and puts them in the hands of educators. Illuminate supports over 17 million students and 5200 districts/schools.

Ready to discover your one-stop shop for your district’s educational needs? Let’s talk.

Share This Story

Leave a Reply

Your email address will not be published. Required fields are marked *