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:
- You need to specify the GPA Calculation method. Mine are named “Projected GPA %%” where %% is the term name. Change to suit your needs.
- If a category has no assignments for a student (possibly from exemptions), it is not included in the report at all.
- If a category has a denominator of zero (say, the only assignment was extra credit), then the percent is blank.
The code:
<ReportName>Student Projected GPA</ReportName>
<ReportTitle>Student Projected GPA</ReportTitle>
<AfterTitle></AfterTitle>
<ReportGroup></ReportGroup>
<ReportDescription>Returns student grades by category for all enrolled classes, along with the weighted final grade for all enrolled classes, and the student's projected GPA. Sorted by Homeroom.</ReportDescription>
<SQLQuery><textarea name="UF-0060051075">SELECT slf, gl, expr, courseid, tlf, pct, category
FROM
(
SELECT s.lastfirst slf, s.grade_level gl, cc.expression expr, (c.course_number || '.' || sec.section_number) courseid, t.lastfirst tlf,
(CASE WHEN sum(pga.pointspossible) = 0 THEN Null ELSE
round(sum(to_number(ssa.score, '999.99')) / sum(pga.pointspossible) * 100, 2) END) pct, pgc.name category
FROM Students s
~[if#cursel.%param2%=Yes]
INNER JOIN ~[temp.table.current.selection:students] stusel ON s.dcid = stusel.dcid
[/if#cursel]
INNER JOIN CC cc ON s.id = cc.studentid
INNER JOIN Sections sec ON cc.sectionid = sec.id
INNER JOIN Courses c ON upper(sec.course_number) = upper(c.course_number)
INNER JOIN Teachers t on cc.teacherid = t.id
INNER JOIN SectionScoresID ssi ON cc.sectionid = ssi.sectionid AND cc.studentid = ssi.studentID
INNER JOIN SectionScoresAssignments ssa on ssi.dcid = ssa.fdcid
INNER JOIN PGAssignments pga ON pga.id = ssa.assignment
INNER JOIN PGCategories pgc ON pgc.id = pga.pgcategoriesid
WHERE
s.enroll_status = 0
and sec.SchoolID = ~(curschoolid)
and to_char(sec.termid) like '~(curyearid)%%'
and ssa.score not like '%-%'
GROUP BY s.lastfirst, s.grade_level, cc.expression, c.course_number || '.' || sec.section_number, t.lastfirst, pgc.name
UNION ALL
SELECT s.lastfirst slf, s.grade_level gl, cc.expression expr, (c.course_number || '.' || sec.section_number) courseid, t.lastfirst tlf, max(to_number(pgf.Percent, '999.99')) pct, ' Course Grade' category
FROM Students s
~[if#cursel.%param2%=Yes]
INNER JOIN ~[temp.table.current.selection:students] stusel ON s.dcid = stusel.dcid
[/if#cursel]
INNER JOIN CC cc ON s.id = cc.studentid
INNER JOIN Sections sec ON cc.sectionid = sec.id
INNER JOIN Courses c ON upper(sec.Course_Number) = upper(c.Course_Number)
INNER JOIN Teachers t on cc.teacherid = t.id
INNER JOIN PGFinalGrades pgf ON cc.sectionid = pgf.sectionid AND cc.studentid = pgf.studentid
WHERE
s.enroll_status = 0
and sec.SchoolID = ~(curschoolid)
and to_char(sec.termid) like '~(curyearid)%%'
and pgf.finalgradename = '%param1%'
GROUP BY s.lastfirst, s.grade_level, cc.expression, c.course_number || '.' || sec.section_number, t.lastfirst
UNION ALL
SELECT s.lastfirst slf, s.grade_level gl, to_char (Null) expr, to_char(Null) courseid, to_char(Null) tlf, round(to_number(cr.GPA, '999.9999'), 2) pct, ' GPA' category
FROM Students s
~[if#cursel.%param2%=Yes]
INNER JOIN ~[temp.table.current.selection:students] stusel ON s.dcid = stusel.dcid
[/if#cursel]
INNER JOIN ClassRank cr ON s.id = cr.studentid
WHERE
s.enroll_status = 0
AND cr.SchoolID = ~(curschoolid)
AND cr.yearid = ~(curyearid)
AND cr.GPAMethod = 'Simple Projected ' || '%param1%'
)
ORDER BY hr, slf, expr, courseid, category</textarea></SQLQuery>
<ReportHeader><th>Student</th><th>Grade</th><th>Period</th><th>Class</th><th>Teacher</th><th>Percent</th><th>Category</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>Term</ParameterName1>
<ParameterVal1>A1</ParameterVal1>
<ParameterCal1></ParameterCal1>
<ParameterOpt1>term</ParameterOpt1>
<ParameterName2>Use Current Selection?</ParameterName2>
<ParameterVal2>No</ParameterVal2>
<ParameterCal2></ParameterCal2>
<ParameterOpt2>yesorno</ParameterOpt2>
<ParameterName3></ParameterName3>
<ParameterVal3></ParameterVal3>
<ParameterCal3></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>1</ValueLi3>
<NoHeaderRow></NoHeaderRow>
<ReportDirections>Ensure that the appropriate grading term is selected. To restrict the report to a group of students, select those students from the Start page and choose "Use Current Selection".</ReportDirections>
