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.
What ended up working best for me (and perhaps for you, too, if you’re in a similar situation—beginner-to-intermediate skills for both SQL and JavaScript/jQuery) is convoluted, but it works:
- Create an empty JavaScript array.
- Run your ~[tlist_sql] query.
- Get the results and feed them into the array as an array of objects.
- Create an empty JavaScript variable.
- Loop through the array of objects and manipulate accordingly, feeding the manipulated data back into the empty JavaScript variable instead of back on to the page.
- Using jQuery to append the JavaScript variable value to an existing element on the HTML page.
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.
So feeding it into the array, reading and manipulating the array data and feeding it into a variable, and then feeding the variable data back on the page is what works best if you know a decent amount of SQL and JavaScript. Let me know if you’d like to try this method and have specific questions about the code (I’ve posted a sample on GitHub).
Leave a Reply