When Field Calculations Identify Typos in Sources

The project: aggregate Fountas & Pinnell performance results for the past three school years.

The challenge: put together the assessments from 6 grade levels over 3 years in 2 test periods per year into one table (so a total of 36 separate sources into one).

Result: something isn’t right.

I created the source table in Illuminate and downloaded it to prepare to use JasperSoft Studio Pro to create a BI analysis of performance over time.  That’s when I noticed something weird:

At the same time, I loaded the file into Excel to start a pivot chart, and saw the same thing:

Where is the 2016 academic year for 1st grade?  And what the hell is that 201 doing there?  I clearly had bad data in the source file.

So I went back to Illuminate to look at the custom report that created that data file.  I sorted by academic year to see where that 201 was coming from.

The reason this jumped out at me is because of the use of calculations and translations in the Advanced menu of the report builder.  I needed to somehow grab the grade level of the student and the academic year in order to get some kind of separation in my BI report, but that information isn’t stored within the assessment itself because of how Illuminate manages student visibility to users, depending on permissions and filters.

To get around that, I created the Grade column as a calculation, using the formula SUBSTRING(G FROM 19 FOR 1), which means “take Column G (the one titled Assessment Grade/Administration) and starting at position 19, grab 1 character.”  Because all of the assessments are named according to the same convention, that position would be the grade value (K, 1, 2, etc.).

Then, I created a second calculation column for Academic Year.  That formula was SUBSTRING (G FROM 1 FOR 4), so taking the first four characters of that same column, resulting in values of 2014, 2015, and 2016.

I wasn’t done yet, because those don’t really represent the academic year the way we need it to.  Those values are the start of the school year, when I want the end.  In comes the translation feature in the advanced menu.

I added translations for all three values, making 2014 = 2015, 2015 = 2016, and 2016 = 2017 so that at the very least, when I download the data, it’ll look right and I can do additional manipulation after that.

So when I saw the blank in Grade and 201 in Academic Year, there was something goofy going on with my calculated fields and translation.

Then I looked to the right and saw that they all shared the same assessment source.  2015-16 F&P Grade 1 Spring.  I stared at it for a moment.  Every other assessment grabbed those substring values correctly except for this one.

Which meant that, since the substring calculation for academic year was supposed to grab the first four characters, and the grade field grab the 19th character, but I was only getting the first three of the year and a blank for grade…..

Was there a space in the name of this particular assessment that I can’t see right off the bat?

I went into that assessment and opened up the information window for it, and lo and behold….

The calculations and translations were working correctly.  The calculation for the year was grabbing the first four characters, as designed.  The problem is that the source had an extra character where it shouldn’t have been.  Which caused the 19th character to be the space before the 1.

And since I didn’t build a translation rule for values of ” 201,” it didn’t translate anything for me.

Take out the space, go back to my custom report and…


So even though we don’t want bad data, bad data can still tell a story.