Category: Illuminate

Posted on

Add Former English Learner from PowerSchool Into Illuminate

Our English Language Development department wants to track students who are considered “former English learners” in Illuminate.  The criterion is if they’ve been exited from the EL program within the last four years.  This requires a few preparations.

First, set the code within Illuminate to display the former EL status.  I just chose the next one available.  I’ll use this variable in my SQL script, which comes next.

From the cog, choose Code Management

Look for English Proficiency.  I just start typing “english,” and it appears:

Then add the new code.  In our case, plain number 6 was the next available one.  I have to remember this value:

Next, I need to edit studemo.sql on the server where the SQL extract scripts are installed.  These are the scripts that build queries from the PowerSchool database, assemble the files, and then SFTPs them to Illuminate’s server.

The column that provides English proficiency status is column 14.  For that column, I use a CASE statement:

case
   when (sc.flaglep = 1) then 3
   when (sc.lepexitdate > to_date('6/15/'||to_char(extract(year from sysdate)-4),'mm/dd/yyyy')) then 6
else null end

The prefix sc refers to an alias for our state reporting code.  PowerSchool users outside of Michigan may have different column names.  Walking through this CASE statement, here’s what I’m saying:

  • If the “is an English learner” checkbox is marked, the value is 1 in the database, so I want to return the value of 3 to send to Illuminate (note in the screen shot above of the code table, where 3 indicates “English Learner”).
  • If the EL exit date is greater than a date of June 15 of the year that’s 4 earlier than the current system date, then return a 6.
  • Otherwise, return nothing

So the possible output values will be 3, 6, or nothing.  This is proven when looking at studemo.txt in Excel:

Once uploaded to Illuminate, this value can then be used in filters for reports, student groups, and assessments:

Posted on

Use Assessments to Build Student Groups

Illuminate as two main ways to build student groups: choose a common characteristic of those students as the basis or use the results of an assessment to separate out a group of kids.  But what if you need to provide an assessment to a group of students who have no common characteristic?

Or what if that common characteristic doesn’t exist in the Illuminate dataset?

For example, a question I was asked was “do kindergarteners who receive a waiver perform differently academically in later years versus those who do not receive a waiver (and therefore, start kindergarten later)?”  Another request I received was to be able to generate Illuminate answer sheets for math test-outs being administered to various students of various grades from different buildings, simply because they applied for the test to skip certain math classes.

In both of these cases, Illuminate stored no common characteristic of these students.  For the kindergarten waiver question, I could have used an unused field in student demographics to build that common feature (which I’ll discuss in a future post), but because this was an ad hoc report intended to prove a point, it wasn’t worth the extra effort to build automation around it.

The students applying to test out of certain math classes certainly had no common characteristic.

So I used an assessment itself as the tool to establish that commonality to create the student group.  Here’s how.

Create a Dummy Assessment

The dummy assessment simply consists of one question.  The answer doesn’t matter, as all we’re looking for is a way to capture the specific students we need.

The students we need are identified in our SIS, so the import file can be built out of there.  I narrow down the students who had kindergarten waivers, or applied to test out of Math 8 or pre-calculus.

Upload the Students

I reduced this CSV to two columns: StudentID and Q1.  Q1 (the “answer” to the question) simply needed to contain the same character for each record.  This was then uploaded to the dummy assessment, which produced the familiar overview:

Now we know that 403 students in the district received a waiver for kindergarten at some point.  The rest of their demographic details are stored in Illuminate already.

Create the Student Group

Creating a student group from an assessment requires generating a report first.  So let’s do that.  Clicking on either the donut or the number of students in the summary section will display the list of students:

Click Create Custom Report to create the report consisting of only these students.

From inside that new report, click Advanced then Create Student Group.

And now that student group can be used as any normal student group.  That group can be used to generate pre-filled answer sheets for particular assessments, it can be used as the basis of a trend report, or it can even be used as a focus group or intervention group.

What otherwise started as no other commonly bound group of students are now together through a few extra steps.

Posted on

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…

Perfect.

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