Reports

An Exceptionally Simple Class Grade Distribution Chart for DCPS Teachers

This is the latest in lame GSheets hacks I put together for Kelly.

Every Monday, she hands out progress reports. And every Monday, two manners of delusion ensue:

  1. The kids who are doing poorly assume that they’re doing no worse than anybody else (the system must be biased!)
  2. The kids who were doing well but have been slipping assume that they must still be at the top of the heap, because they’re the smart kids so of course they are.

Wouldn’t it be nice if you could point to a classwide grade distribution to show where each student fits in the grand scheme of things?

Indeed it would.

Enter, the grade distribution chart.

It’s driven by this GSheet, which simply takes grade reports from Aspen and turns them into stacked dot charts that can be printed and stapled to your data wall (remember data walls?). You can’t see it in the sample, but there is also a dot at the class average.

It’s anonymous, but as long as the students have their progress reports (which shouldn’t be a problem given that this is generated from them), the kids can see exactly where their grade stacks up against their peers.

The biggest utility, though, is for classes who remain convinced that they’re “the smart ones” and thus must always be at the top of the heap. When they inevitably try to rest on their laurels, it’s always satisfying to show them getting blown away by the “earnest plodders” in the remedial group. ūüôā

So, how do you use this beauty?

Two sections follow: initial setup and regular use.

Initial Setup

  1. Open the template GSheet. Make it your own by clicking File | Make a Copy.
  2. Rename the tabs to match your section numbers. Stick with the existing syntax since the other sheets are designed to expect an A-B section number syntax.
    • If you need more than 5 sections, just click on one of the tabs and choose Duplicate. Remember, though, that you need to do this 2 times: once for the Raw sheet and once for the Chart sheet.
    • Further, you’ll have to direct the Chart sheet to the appropriate columns in the Data sheet. In short, you may need to get in touch.
  3. Go to the Data sheet and change the titles, making sure to retain the format of Course A-B with section numbers.
  4. Update the titles of each chart sheet appropriately (alas, there’s no easy way to make them auto-update).

Regular Use

  1. Open Aspen and navigate to the Gradebook for a particular section.
  2. Load the Scores page.
  3. Run the Assignment History report.
  4. Set the format to CSV and accept the warning.
  5. Open the report file in Excel.
  6. Click the top-left chiclet (the gray square between the headings for Column A and Row 1)
  7. Copy to the clipboard.
  8. In the GSheet, open the appropriate Raw tab.
  9. Click the same top-left chiclet to select everything.
  10. Hit Delete to clear out the old data.
  11. Click Edit | Paste Special | Paste Values Only to paste the data.
  12. Repeat for the remaining sections.
  13. To Print, open the appropriate Chart tab.
  14. Hit Ctrl-P to get to the Print Preview dialog. The charts are formatted in Landscape for improved visibility.
  15. Click Next to get to the Print screen.
  16. Print per usual.

Posted by Adam Labay, 0 comments

PowerSchool ISA Report for DC Schools

If you’re a DC school that uses PowerSchool as your SIS, then you’ve probably faced the situation where none of the attendance reports properly match the In-Seat Attendance (ISA) reports in Qlik.

I still can’t speak to the exact mechanics of why the two reports differ, but to bridge the gap I put together a report for the SQLReports4 package that calculates ISA using the same method as Qlik.

A few notes:

  1. All students need to have a Full-Time Equivalency (FTE) assigned. ¬†Generally, this happens by default when a student is added to PowerSchool, but sometimes it doesn’t register.
  2. The report’s first parameter lets you decide whether to report Year-to-Date or over a date range.
    • Both cases will return the ISA% by month and grade.
    • If the date range is specified, grade and schoolwide values for the range will be returned.
    • If the date range is not specified, grade and schoolwide values YTD will be returned.

The code:

<ReportName>In-Seat Attendance</ReportName>
<ReportTitle>In-Seat Attendance</ReportTitle>
<AfterTitle></AfterTitle>
<ReportGroup>Attendance</ReportGroup>
<ReportDescription>%ISA [(Students Present * Days Present)/(Students Enrolled * Days Enrolled)] per grade, per month.</ReportDescription>
<SQLQuery><textarea name="UF-0060051129">WITH mem_days AS
(
SELECT to_char(cd.Date_Value, 'MM/YYYY') m, s.Grade_Level gl, COUNT(cd.Date_Value) dm

FROM Students s
INNER JOIN Terms t ON t.YearID = ~(curyearid) AND s.SchoolID = t.SchoolID
INNER JOIN Calendar_Day cd ON s.EntryDate &lt;= cd.Date_Value AND s.ExitDate &gt;= cd.Date_Value AND s.SchoolID = cd.SchoolID AND t.FirstDay &lt;= cd.Date_Value

WHERE
cd.Date_Value &lt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param3%', 'MM/DD/YYYY') ELSE SYSDATE END)
AND cd.Date_Value &gt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param2%', 'MM/DD/YYYY') ELSE to_date('1/1/1900', 'MM/DD/YYYY') END)
AND cd.MembershipValue = 1

GROUP BY to_char(cd.Date_Value, 'MM/YYYY'), s.Grade_Level
)
, att_days AS
(
SELECT to_char(cd.Date_Value, 'MM/YYYY') m, s.Grade_Level gl, COUNT(att.Att_Date) da

FROM Students s
INNER JOIN Terms t ON t.YearID = ~(curyearid) AND s.SchoolID = t.SchoolID
INNER JOIN Calendar_Day cd ON s.EntryDate &lt;= cd.Date_Value AND s.ExitDate &gt;= cd.Date_Value AND s.SchoolID = cd.SchoolID AND t.FirstDay &lt;= cd.Date_Value
INNER JOIN Attendance att ON s.ID = att.StudentID AND cd.Date_Value = att.Att_Date
INNER JOIN Attendance_Code attc ON att.Attendance_CodeID = attc.ID

WHERE
cd.Date_Value &lt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param3%', 'MM/DD/YYYY') ELSE SYSDATE END)
AND cd.Date_Value &gt;= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param2%', 'MM/DD/YYYY') ELSE to_date('1/1/1900', 'MM/DD/YYYY') END)
AND cd.MembershipValue = 1
AND att.Att_Mode_Code = 'ATT_ModeDaily'
AND attc.Presence_Status_CD = 'Present'

GROUP BY to_char(cd.Date_Value, 'MM/YYYY'), s.Grade_Level
)
,isa AS
(
SELECT to_char(to_date(mem_days.m, 'MM/YYYY'), 'YYYY') y, to_char(to_date(mem_days.m, 'MM/YYYY'), 'Month') m, mem_days.gl gl, ROUND(att_days.da/mem_days.dm*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
ORDER BY mem_days.m, gl
)
, isa_sch AS
(
SELECT mem_days.m md, to_char(to_date(mem_days.m, 'MM/YYYY'), 'YYYY') y, to_char(to_date(mem_days.m, 'MM/YYYY'), 'Month') m, 'All' gl, ROUND(SUM(att_days.da)/SUM(mem_days.dm)*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
GROUP BY mem_days.m, to_char(to_date(mem_days.m, 'MM/YYYY'), 'YYYY'), to_char(to_date(mem_days.m, 'MM/YYYY'), 'Month')
ORDER BY mem_days.m
)
,isa_ytd AS
(
SELECT (CASE WHEN '%param1%' = 'Yes' THEN 'Range' ELSE 'YTD' END) y, (CASE WHEN '%param1%' = 'Yes' THEN '%param2%' || ' - ' || '%param3%' ELSE 'YTD' END) m, mem_days.gl gl, ROUND(SUM(att_days.da)/SUM(mem_days.dm)*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
GROUP BY mem_days.gl
ORDER BY mem_days.gl
)
, isa_sch_ytd AS
(
SELECT (CASE WHEN '%param1%' = 'Yes' THEN 'Range' ELSE 'YTD' END) y, (CASE WHEN '%param1%' = 'Yes' THEN '%param2%' || ' - ' || '%param3%' ELSE 'YTD' END) m, 'All' gl, ROUND(SUM(att_days.da)/SUM(mem_days.dm)*100, 2) pct
FROM mem_days
INNER JOIN att_days ON mem_days.m = att_days.m AND mem_days.gl = att_days.gl
)

SELECT y, m, to_char(gl), pct
FROM isa
UNION ALL
SELECT y, m, gl, pct
FROM isa_sch
UNION ALL
SELECT y, m, to_char(gl), pct
FROM isa_ytd
UNION ALL
SELECT y, m, gl, pct
FROM isa_sch_ytd</textarea></SQLQuery>
<ReportHeader><th>Year</th><th>Month</th><th>Grade Level</th><th>ISA %</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<CreateStudentSelectionB></CreateStudentSelectionB>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>
<HideCopyButton></HideCopyButton>
<HideCSVButton></HideCSVButton>
<HideTabButton></HideTabButton>
<HidePDFButton></HidePDFButton>
<ShowSearchBox></ShowSearchBox>
<ShowResultsInfo></ShowResultsInfo>
<ParameterName1>Custom Date Range</ParameterName1>
<ParameterVal1>Yes</ParameterVal1>
<ParameterCal1></ParameterCal1>
<ParameterOpt1>yesorno</ParameterOpt1>
<ParameterName2>Start Date</ParameterName2>
<ParameterVal2>8/22/2016</ParameterVal2>
<ParameterCal2>1</ParameterCal2>
<ParameterOpt2></ParameterOpt2>
<ParameterName3>End Date</ParameterName3>
<ParameterVal3>12/5/2016</ParameterVal3>
<ParameterCal3>1</ParameterCal3>
<ParameterOpt3></ParameterOpt3>
<ParameterName4></ParameterName4>
<ParameterVal4></ParameterVal4>
<ParameterCal4></ParameterCal4>
<ParameterOpt4></ParameterOpt4>
<ParameterName5></ParameterName5>
<ParameterVal5></ParameterVal5>
<ParameterCal5></ParameterCal5>
<ParameterOpt5></ParameterOpt5>
<PdfOrientation></PdfOrientation>
<NoCSVQuotes></NoCSVQuotes>
<NoRowSel></NoRowSel>
<ShowSHC></ShowSHC>
<UseColRe></UseColRe>
<UseFixHdr></UseFixHdr>
<HidePrint></HidePrint>
<ReportNotes></ReportNotes>
<UseColFilt></UseColFilt>
<UseRowGroups></UseRowGroups>
<UseRowGroupsOptions>Opt1</UseRowGroupsOptions>
<DisplayRowGroupsCol></DisplayRowGroupsCol>
<UseRowGroupsCounts></UseRowGroupsCounts>
<sqlChartsReport></sqlChartsReport>
<InitialsqlChart></InitialsqlChart>
<InitialChartName></InitialChartName>
<RowGroupsDesc></RowGroupsDesc>
<FLSMapField></FLSMapField>
<ExcFrmList></ExcFrmList>
<ExecGrp></ExecGrp>
<ValueLi3>0</ValueLi3>
<NoHeaderRow></NoHeaderRow>
<ReportDirections></ReportDirections>
Posted by Adam Labay, 0 comments

For My PGCPS Friends: The What-If Report

There’s ongoing and increasing talk of the Growth Mindset going around.

And, more often than not, all it amounts to is bulletin boards chock-full of motivational sayings.  And little else.

Instead, wouldn’t it be nice to do something for your students that actually helps them connect effort and achievement?

Enter the What-If report.

whatifsample

Say you’re like me, and have that kid (or kids, or all of your kids) who never completes more than 60% of an assignment.  Frequently-cited reasons include:

  • I don’t know what I’m doing, so my score’s not going to improve by doing more work anyway.
  • 60% is passing, isn’t it?
  • It’s just not worth my time.

To start proving to them that the work would have paid off, I started placing three grades at the top of each assignment:

  1. Your grade in my class right now
  2. Your score on this assignment
  3. What your class grade would have been if you had received full credit on this assignment.

The kids loved it and started putting forth more effort.  This is even more effective if you do make-up assignments or have a robust system in place for revision.

Here’s how to do it:

Step 1: Pull a grade report from SchoolMax.

Go into SchoolMax, click on Reports >> Grades, and pull a “Student Assignments and Grade Listing” Report.

Make sure to export to CSV.

export

Step 2: Make a copy of this Google Spreadsheet.

savereport

Click the screenshot, go to File >> Make a Copy, and it’s yours to work with.

Or, open the file directly from the link: https://docs.google.com/spreadsheets/d/1DqFpyRWkPW8_ARiSechFnw0Nj6lUNtxaiP1tdC57QUY/edit#gid=0

Step 3: Insert the grade report into the What-If Spreadsheet.

Open the tab named GR101.CSV, delete all the contents, and paste the contents of your grade report.

Step 4: Pick the assignment.

Return to the first sheet, and pick an assignment.

Nota Bene: For this to work, all of your assignments in SchoolMax must:

  1. have unique names, i.e. “Homework 9/16/16” instead of just “Homework” each week.
  2. be unique within 40 characters, i.e. “Revised first draft of analytical essay on <<topic>>” will get chopped to “Revised first draft of analytical essay” and you’re back to problem (a).

Step 5: Read off the appropriate values.

Note: This was written for 3 reporting categories, as tends to be the case in PGCPS.  If there are more than 3 categories, you’re screwed.  Let me know and we’ll work something out.

Step 6: Enjoy a lovely beverage.

Cheers

Posted by Adam Labay, 0 comments

PowerSchool SQLReport: Assignment List and Projected GPA

A vice principal wanted a report that can be given weekly to Homeroom teachers, which provides them with each of their students’:

  • Current grade in each class
  • Average by category (Homework, Classwork, Assessment) in each class
  • Projected GPA

The code is below if you want to use it for yourself. ¬†This is intended to be used with SQLReports4. ¬†If you don’t have it on your PS server, I strongly encourage its addition.

A few notes:

  1. You need to specify the GPA Calculation method. ¬†Mine are named “Projected GPA %%” where %% is the term name. ¬†Change to suit your needs.
  2. If a category has no assignments for a student (possibly from exemptions), it is not included in the report at all.
  3. If a category has a denominator of zero (say, the only assignment was extra credit), then the percent is blank.

The code:

Continue reading →

Posted by Adam Labay, 0 comments