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:
- 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.
- 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 <= cd.Date_Value AND s.ExitDate >= cd.Date_Value AND s.SchoolID = cd.SchoolID AND t.FirstDay <= cd.Date_Value
WHERE
cd.Date_Value <= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param3%', 'MM/DD/YYYY') ELSE SYSDATE END)
AND cd.Date_Value >= (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 <= cd.Date_Value AND s.ExitDate >= cd.Date_Value AND s.SchoolID = cd.SchoolID AND t.FirstDay <= 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 <= (CASE WHEN '%param1%' = 'Yes' THEN to_date('%param3%', 'MM/DD/YYYY') ELSE SYSDATE END)
AND cd.Date_Value >= (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>
Recent Comments