Acknowledgements: the actual genius of this workaround approach (including the important snippets of code) is from Adam L. on the PowerSource forums (you can search the forums for a thread entitled “tlist_sql and GPA methods”).
I find ~[tlist_sql] handy for doing reports out of PowerSchool, because it’s transparent (someone can look directly at the page code and see what is being selected and how) and because it’s semi-universal (PowerSchool has its own quirks, but the queries are fairly standard Oracle SQL, so someone coming in knowing only SQL or MySQL can easily adapt to this approach to reporting out of PowerSchool). One of my users asked for a report with three different kinds of GPA calculations. For the sake of this example, we’ll just call them Unweighted, WeightedOneWay, and WeightedAnotherWay.
On a custom page looking at only one student, you can reference the GPA calculation this way:
As I had to do with test score reporting, GPAs for multiple students involves some JavaScript gymnastics.
The basic idea is that you put in a placeholder for the GPA that has the dcid as a parameter, and then you use jQuery to substitute in a separate .html page with that individual’s GPA, and then just go down the line substituting in that page for every result.
This is a version of what I ended up with (I left out some extra tweaks specific to our school):
Referenced HTML file called student_gpas.html
Report HTML file (call it whatever you want) that calls the referenced file
<html>
<head>
<title>Report with student’s GPAs in it</title>
<!– required scripts –>
~[wc:commonscripts]
<!– Required style sheets: screen.css, and print.css –>
<link href=”/images/css/screen.css” rel=”stylesheet” media=”screen”>
<link href=”/images/css/print.css” rel=”stylesheet” media=”print”>
<script>
$j(document).ready(function()
{
$j(“.gpa”).each(function()
{
$j(this).load(“student_gpas.html”,
{
“frn”: “001” + $j(this).attr(“dcid”),
“method”: $j(this).attr(“method”)
})
});
})
</script>
</head>
<body>
~[wc:admin_header_css]
<!– breadcrumb start –><!– breadcrumb end –>
<!– start of main menu and content –>
~[wc:admin_navigation_css]
<!– Start of Page –>
<h1>Report with student’s GPAs in it</h1>
<!– start of content area –>
<div class=”box-round”>
<table class=”tableToGrid”>
<thead>
<tr>
<th data-sorttype=”customText”>Student</th>
<th data-sorttype=”integer”>Gr.</th>
<th data-sorttype=”attrDec”>Unweighted</th>
<th data-sorttype=”attrDec”>Weighted One Way</th>
<th data-sorttype=”attrDec”>Weighted Another Way</th></tr></thead><tbody>
~[tlist_sql;
SELECT dcid student_dcid, last_name, first_name, grade_level, dcid student_dcid, dcid student_dcid, dcid student_dcid
FROM students
WHERE enroll_status=0
ORDER BY grade_level DESC, lastfirst ASC; alternatecolor]
<tr>
<td>~(last_name), ~(first_name)</td>
<td>~(grade_level)</td>
<td class=”gpa” dcid=”~(student_dcid)” method=”Unweighted”></td>
<td class=”gpa” dcid=”~(student_dcid)” method=”WeightedOneWay”></td>
<td class=”gpa” dcid=”~(student_dcid)” method=”WeightedAnotherWay”></td> </tr>[/tlist_sql]
</tbody></table> </div>
<!– end of content area –>
~[wc:admin_footer_css]
</body>
</html>
What does all this code mean?
The bolded bits are the parts doing all the heavy lifting. So the first thing that happens is the page renders with a bunch of empty cells that have a class of “gpa” and a dcid of the student’s dcid (one student per row). Then, once the page is loaded, the JavaScript at the top of the page looks through each item that has the class “gpa” and then substitutes in the content from student_gpas.html?frn=001#### where #### is the actual student’s dcid number, with the method being the GPA method, so the first time it gets called, it will look for the unweighted GPA, then weighted one way, then the other. Then, it’ll go to the next line and keep looking for those three for the next student.
Keep in mind, depending on how many results you have (how many students come back from your SQL query), it could take the page a long time to load. I ran a report (the select criteria was more specific than just any enrolled student) that had about 100 students with three GPA calculations per student, and it took almost a full minute for the page to load.