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.
Previously, I’d created a custom page that fetched a lot of data and then shoved the data into a JavaScript array, manipulated the data there, and then spit the data back onto the page. This is because PowerSchool custom pages won’t let you just manipulate the data as you go. The custom pages try to be “smart” and prematurely close </div> tags.
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:
CASE
WHEN st.grade_level!=0
THEN st.grade_level
ELSE NULL
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
) scores
FROM students stu
INNER JOIN studenttest st
ON stu.dcid=st.studentid
INNER JOIN test t
ON st.testid=t.id
WHERE stu.dcid=~(curstudid)
ORDER BY t.name, st.test_date