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>

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:

The code:

Continue reading →