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:
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
<title>Report with student’s GPAs in it</title>
<!– required scripts –>
<!– 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”>
“frn”: “001” + $j(this).attr(“dcid”),
<!– breadcrumb start –><!– breadcrumb end –>
<!– start of main menu and content –>
<!– Start of Page –>
<h1>Report with student’s GPAs in it</h1>
<!– start of content area –>
<th data-sorttype=”attrDec”>Weighted One Way</th>
<th data-sorttype=”attrDec”>Weighted Another Way</th></tr></thead><tbody>
SELECT dcid student_dcid, last_name, first_name, grade_level, dcid student_dcid, dcid student_dcid, dcid student_dcid
ORDER BY grade_level DESC, lastfirst ASC; alternatecolor]
<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]
<!– end of content area –>
What does all this code mean?
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.