By default, PowerSchool will show you a list of tests, and then you have to drill down into each test to get to the actual set of scores. There isn’t a way built into PowerSchool to view all the scores and subscores for a particular student.
That was a lot of overhead, though. Fetch too much data, shove it into an array, create a holder variable. Loop through the array and put the processed data into the holder variable. Push the holder variable data back to the page.
I couldn’t find any clear documentation on how to concatenate a string of results back into a subquery in the select clause for Oracle. Through trial and error and through the unclear documentation out there, I finally got the subquery working. This is how you do it:
END gr_level, to_char(st.test_date, ‘MM/DD/YYYY’) test_date, st.id,
(SELECT LISTAGG(‘<div class=”test_cell”><strong>’ || REPLACE(ts.name, t.name || ‘_’, ”) || ‘</strong><br />’ || sts.numscore || ‘</div>’) WITHIN GROUP (ORDER BY ts.sortorder)
FROM studenttestscore sts
INNER JOIN testscore ts ON sts.testscoreid=ts.id
WHERE sts.studenttestid=st.id AND sts.studentid=st.studentid
FROM students stu
INNER JOIN studenttest st
INNER JOIN test t
ORDER BY t.name, st.test_date