If you don’t want to use PDO on a WAMP server to connect to and report from your PowerSchool database, you may still want to do some sophisticated reporting from PowerSchool’s custom pages.
Unfortunately, custom pages is a bit of a coarse tool that’s also not well documented (in terms of sophisticated reporting—simple reporting has a lot of good documentation… Customizing PowerSchool web pages, for example). I took the Advanced Customizations course at PowerSchool, and it looks as if some wizard PowerSchool admins are able to get around the limitations of custom pages by either:
A) Writing multi-line extremely complex SQL queries (I’m talking two-page-long queries with multiple subqueries and conditionals).
B) Displaying an initially useless HTML page and then using jQuery methods to push various elements around to where they should be.
My SQL skills are not good enough to do A). I can do INNER JOINs, OUTER JOINs, subqueries, counts, and some other beginner-to-intermediate SQL, but I’m not good enough to get every report looking exactly how I want through SQL manipulation alone.
I also tried doing B) with some garbage HTML and then pushing the elements around using jQuery. Some of the stuff I could do, but my grasp of jQuery is clearly not good enough to handle everything I need to do there… and, frankly, it’s a bit messy—a lot of adding things you need to later remove and a lot of keeping track of ids and parent and child elements.
- Run your ~[tlist_sql] query.
- Get the results and feed them into the array as an array of objects.
I know that sounds complicated, but I tried manipulating the data directly as it’s coming out of ~[tlist_sql], and the documentation with examples that exists for local custom pages variables is scant. I wasn’t able to get that working.
I also tried manipulating the data as I was looping through the array of objects (in other words, “Oh, it’s this value, push out an opening <div>—it’s that value, push out a closing </div>”), but PowerSchool doesn’t like to see opening tags hanging, so it will be “smart” (i.e., overly controlling) about closing the tags prematurely.