Escaping an apostrophe on a PowerSchool custom page variable

Acknowledgements: Thanks to Roger Sprik on the PowerSchool forums for this tip.

I have a custom page that uses the last name field:

~([05]Last_Name)
The only problem with that is if the last name has an apostrophe in it, that can lead to unexpected stuff when used in jQuery.

Apparently, you can strip out the variable to show only the letters and numbers:

~([05]Last_Name;keep_ascii=48-57,65-90,97-122)

PowerSchool tlist_sql fails but SQL Developer query is fine

Full props to Romy from the PowerSource forums for helping me sleuth a problem with a query working just fine in SQL Developer but then failing (with a blank-loaded page) in tlist_sql on a PowerSchool custom page.

Basically, a colon, even within quotation marks, messes up the query within tlist_sql, so I had to use the HTML ASCII code for it instead.

Worked a charm.

For sqlReports4, you should use these ASCII codes without quotation marks around them.

Converting seconds to time in PowerSchool custom pages

I know someone's going to jump in here and tell me that PowerSchool's custom pages have no problems with SQL statements but are just less forgiving of syntactical errors than SQL Developer is, but I couldn't get any of the convert-seconds-to-time standard SQL approaches to work in a PowerSchool custom page using tlist_sql, even when those solutions worked fine in SQL Developer. Here are some examples.

Eventually, the solution that did end up working best was taking a trunc / mod approach in combination with lpad to get some times together. Here is what it looks like as part of the select statement:

SELECT TO_CHAR(l.logindate, 'MM/DD/YYYY') logindate,
lpad(trunc(l.logintime/3600), 2, '00') loginhour,
lpad(trunc(mod(l.logintime, 3600)/60), 2, '00') loginminutes,
lpad(mod(l.logintime, 60), 2, '00') loginseconds
FROM logins l
WHERE l.logindate > sysdate -21
ORDER BY l.logindate, l.logintime
It takes a raw seconds field and then converts it to corresponding hours, minutes, and seconds.

PowerSchool Custom Pages Exporting Tables to .csv Using jQuery

Someone on Stack Overflow made a jsfiddle to export existing HTML tables to .csv using jQuery.

It does not, however, translate directly to PowerSchool custom pages, because PowerSchool has its own implementation of jQuery that's a little funky.

I've done a little playing around with it, and this tweaked code seems to work.

For the table, make sure it's enclosed with a div with an id (for this example, we're calling it dvData):

<div id="dvData">
<table class="tableToGrid">
</table>
</div>

Include an actual clickable link to do the download:

<p><a href="#" class="export">Export table data into Excel</a> </p>

Put the table-to-CSV code into your jQuery document ready section (or create the section if it doesn't exist yet:

<script>
$j(document).ready(function(){

// Adapted from http://jsfiddle.net/terryyounghk/KPEGU/ but tweaked for PowerSchool's jQuery implementation
function exportTableToCSV($table, filename) {

// Original code
//var $rows = $table.find('tr:has(td)'),
// My tweak so we can get the header row
var $rows = $table.find('tr'),

// Temporary delimiter characters unlikely to be typed by keyboard
// This is to avoid accidentally splitting the actual contents
tmpColDelim = String.fromCharCode(11), // vertical tab character
tmpRowDelim = String.fromCharCode(0), // null character

// actual delimiter characters for CSV format
colDelim = '","',
rowDelim = '"\r\n"',

// Grab text from table into CSV formatted string
csv = '"' + $rows.map(function (i, row) {
var $row = $j(row),
$cols = $row.find('td');

// This one line from https://gist.github.com/adilapapaya/9787842
if(!$cols.length) $cols = $row.find('th');

return $cols.map(function (j, col) {
var $col = $j(col),
text = $col.text();

return text.replace(/"/g, '""'); // escape double quotes

}).get().join(tmpColDelim);

}).get().join(tmpRowDelim)
.split(tmpRowDelim).join(rowDelim)
.split(tmpColDelim).join(colDelim) + '"',

// Data URI
csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

$j(this)
.attr({
'download': filename,
'href': csvData,
'target': '_blank'
});
}

// This must be a hyperlink
$j(".export").on('click', function (event) {
// CSV
exportTableToCSV.apply(this, [$j('#dvData'), 'export.csv']);

// IF CSV, don't do event.preventDefault() or return false
// We actually need this to be a typical hyperlink
});

// End document ready
});
</script>

You should then have a link at the bottom of the report that allows users to download the existing table to .csv.

I have a full example on GitHub that just pulls all enrolled students, but you can tweak the SQL and headers, obviously.

th tags?

I made a couple of tweaks after extensive trial and error and managed a messy hack that gets the headers in but also may provide some encoding issues (not sure where those are coming from—the HTML generated by tlist_sql should be the same as the regular HTML), as well as a random extra empty row after the header row

custompageencodingissue01 custompageencodingissue02 As you can see, there's a random "space" generated in front of each header title (the space isn't there on the HTML page), and then in Excel that translates to Â.

Granted, this happened to only one of my custom pages, so your mileage may vary. Even for that page, it's more useful to end users than no header row at all, and it's far easier for them to delete an empty row in Excel than to highlight and copy the header row separately.

Unfortunately, the code as is does not export any <th> tags. I haven't had time to fully experiment with how to tweak it to do so.

I found this fork that includes table headers, and it works great in a regular HTML file, but, for some reason, it totally breaks on a PowerSchool custom page, even if you change the $ jQuery references to $j, which is weird, because it seems to be almost the exact same method, except it extracts out some of the other pieces to be functions, which get called twice.

DataTables?

Also, using DataTables seems to work fine in regular HTML, but I haven't found a way to properly implement it in PowerSchool custom pages. Here are the problems I've found:

  • No matter what you do with the width of the datatables element in CSS, if it's within the box-round div on the custom page, it will be pushed down (lots of extra white space at the top) if the sidebar is expanded. I can't figure out how to stop that from happening.
  • You can use jQuery.noConflict(true) to separate the DataTables jQuery from the PowerSchool jQuery, but then no other jQuery commands will run. It's very strange.
  • Even then, you have to put the CDN from DataTables above the ~[wc:commonscripts], or even the noConflict() won't work at all.

sqlReports4

Someone on PowerSource forums mentioned that sqlReports4 would probably be the best solution for making reports that can then be exported as .csv. I tried it out, and it looks promising, assuming you can get the SQL statement just right. My SQL skills are beginner-to-intermediate and not advanced, so I sometimes use custom pages with normal SQL (some joins, and some if/else) and then futz with the results using JavaScript to push them to the appropriate places. I may do a separate blog post on sqlreports4...

PowerSchool custom pages and REGEXP_LIKE

In general, if you test a SQL query in SQL Developer against your PowerSchool database, that same SQL query should work in a PowerSchool custom page using ~[tlist_sql][/tlist_sql]. It does not, however—I discovered just now—work if you include a REGEXP_LIKE as part of your query. Will work fine in SQL Developer... just not in a PowerSchool custom page.

Unfortunately, the only workaround I had was to fetch more results than I wanted server side and then use jQuery on the client side to filter out the extraneous results.

Update: Apparently, REGEXP_LIKE does work with ~[tlist_sql], or so I hear from someone with a lot more PowerSchool experience than I have. I thought I was being careful with my syntax, but it's possible I just screwed up, and SQL Developer is more forgiving of punctuation mistakes than ~[tlist_sql] is.

PowerSchool custom pages tlist sql queries for specific courses

Usually if you're doing a tlist_sql query on a PowerSchool custom page, any SQL query that actually works in SQL Developer will work on the custom page.

I found an odd one today, though—if you have a SQL query that actually works in SQL Developer that uses course names (e.g., ((course_name='Name of One Course') OR (course_name='Name of Another Course'))), that same query will just die silently on a PowerSchool custom page.

On the PowerSchool custom page, you have to use the course number instead. You cannot explicitly call the course name.

Update: Someone brought to my attention that the course name search should work in tlist_sql, but there may just be a character encoded weirdly. Very true, very possible. I'd still probably go with just searching by course number, since that's less likely to change than the course name.

Using “current selection” of students in PowerSchool custom pages

I didn't come up with this one on my own (big shout-out to Roger from the PowerSource forums), but I wanted to document how you use a "current selection" of students in the PowerSchool custom page:

~[tlist_sql; SELECT st.dcid, st.last_name, st.first_name, st.grade_level
FROM Students st
INNER JOIN ~[temp.table.current.selection:students] stusel
ON stusel.dcid = st.dcid ]<div class="photo_box">
<a href="../stp/~(dcid)ph.jpeg" target="_blank">
<img src="../stp/~(dcid)ph.jpeg" width="100" border="0" /></a>
<br clear="both" />
<strong>~(last_name), ~(first_name)</strong>: ~(grade_level)</div>[/tlist_sql]
The current selection (in bold) can be joined in the SQL query to the students table.

Random P.S. I'm referencing here the student photo by exact location. There is a way to reference it indirectly as a variable (instead of directly by filename), but then the photo is reduced in size. Since the raw photos aren't that large, I prefer to load the entire raw photo. Here's how you reference it indirectly:

<span class="account-photo">~[studenttitlephoto]</span>

Getting GPA methods into a PowerSchool custom page that uses tlist_sql

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:

~(*gpa method="Unweighted")
But if you're reporting out listing several students, that won't work, and PowerSchool doesn't offer a built-in way to do so.

As I had to do with test score reporting, GPAs for multiple students involves some JavaScript gymnastics.

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

~(*gpa method="~[gpv:method]")

Report HTML file (call it whatever you want) that calls the referenced file

<!DOCTYPE html>
<html>
<head>
<title>Report with student's GPAs in it</title>
<!-- required scripts -->
~[wc:commonscripts]
<!-- 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">
<script>
$j(document).ready(function()
{
$j(".gpa").each(function()
{
$j(this).load("student_gpas.html",
{
"frn": "001" + $j(this).attr("dcid"),
"method": $j(this).attr("method")
})
});
})
</script>

</head>
<body>
~[wc:admin_header_css]
<!-- breadcrumb start --><!-- breadcrumb end -->
<!-- start of main menu and content -->
~[wc:admin_navigation_css]
<!-- Start of Page -->
<h1>Report with student's GPAs in it</h1>
<!-- start of content area -->
<div class="box-round">
<table class="tableToGrid">
<thead>
<tr>
<th data-sorttype="customText">Student</th>
<th data-sorttype="integer">Gr.</th>
<th data-sorttype="attrDec">Unweighted</th>
<th data-sorttype="attrDec">Weighted One Way</th>
<th data-sorttype="attrDec">Weighted Another Way</th></tr></thead><tbody>
~[tlist_sql;
SELECT dcid student_dcid, last_name, first_name, grade_level, dcid student_dcid, dcid student_dcid, dcid student_dcid
FROM students
WHERE enroll_status=0
ORDER BY grade_level DESC, lastfirst ASC; alternatecolor]
<tr>
<td>~(last_name), ~(first_name)</td>
<td>~(grade_level)</td>
<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]
</tbody></table> </div>
<!-- end of content area -->
~[wc:admin_footer_css]
</body>
</html>

What does all this code mean?

The bolded bits are the parts doing all the heavy lifting. So the first thing that happens is the page renders with a bunch of empty cells that have a class of "gpa" and a dcid of the student's dcid (one student per row). Then, once the page is loaded, the JavaScript at the top of the page looks through each item that has the class "gpa" and then substitutes in the content from student_gpas.html?frn=001#### where #### is the actual student's dcid number, with the method being the GPA method, so the first time it gets called, it will look for the unweighted GPA, then weighted one way, then the other. Then, it'll go to the next line and keep looking for those three for the next student.

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.

Using text variables in tlist_sql queries in PowerSchool

On custom pages in PowerSchool, you have the option to use something called tlist_sql to run SQL queries on your PowerSchool database. Sometimes, you can insert variables in your query. I've had good luck with SQL queries using built-in variables that are essentially a number:

~[tlist_sql; SELECT t.Name, to_char(st.Test_Date, 'MM/DD/YYYY'), st.Grade_Level, ts.Name, ts.Description, sts.NumScore, sts.studenttestid FROM StudentTest st
INNER JOIN StudentTestScore sts ON sts.StudentTestID=st.ID AND sts.StudentID=st.StudentID
INNER JOIN TestScore ts ON sts.TestScoreID=ts.ID
INNER JOIN Test t ON ts.TestID=t.ID
WHERE st.StudentID=~(curstudid) ORDER BY t.Name, st.Test_Date, sts.studenttestid, ts.SortOrder;]
Here, ~(curstudid) is the ID of the student we're currently looking at, usually referenced by something like nameofpage.html?frn=0019999, where 001 is the students table and 9999 is the student's ID number.

I recently came across an issue trying to use built-in text in a query. (Confession time: we are still using custom fields and haven't migrated them all over to database extensions.)

I tried using ~([05]Last_Name) to get the counselor's name, which actually works if you put it in the straight HTML file just to see that the name is fetched. However, if you try to create a query using that variable, the query fails:

"Student Number","Last Name","First Name","Grade Level","Counselor"~[tlist_sql;
SELECT student_number, last_name, first_name, grade_level, ps_customfields.getcf('students', id, 'student_counselor') student_counselor
FROM students
WHERE ps_customfields.getcf('students', id, 'student_counselor')
= '~([05]Last_Name)' AND enroll_status=0
ORDER BY grade_level, lastfirst]
"~(student_number)","~(email)","~(last_name)","~(first_name)","~(grade_level)","~(student_counselor)"[/tlist_sql]
I tried all sorts of variations. I tried using LIKE (instead of =), in case there was extra space. I tried using double quotation marks (which fail even in SQL Developer). No variation whatsoever of ~([05]Last_Name) worked (or [05]Last_Name).

Eventually, I came to a rather odd workaround, but it works. Instead of using the frn passed as a GET variable in the URL, I used the actual last name instead (so the address is more like nameofpage.html?lastname=actuallastname.

With that other approach in place, I could use the GET variable in the query:

"Student Number","Last Name","First Name","Grade Level","Counselor"~[tlist_sql;
SELECT student_number, last_name, first_name, grade_level, ps_customfields.getcf('students', id, 'student_counselor') student_counselor
FROM students
WHERE ps_customfields.getcf('students', id, 'student_counselor')
= '~(gpv.lastname)' AND enroll_status=0
ORDER BY grade_level, lastfirst]
"~(student_number)","~(email)","~(last_name)","~(first_name)","~(grade_level)","~(student_counselor)"[/tlist_sql]
I couldn't find any PowerSchool documentation around this, so I hope this blog post is helpful to some PowerSchool custom pages folks out there...

Creating a custom page in PowerSchool for a .csv export

Even though teachers can easily get their own class lists out as an export from Gradebook, and you can also use DDE (Direct Database Export) to get such a list out after having selected the group of students, I thought it'd be nice to have a page that's dedicated to get a .csv out of all the students and sections a teacher was teaching.

I'm assuming you have access to PowerSchool Systems Administrator and thus can drill down on Custom Pages and make new custom pages. This one I put in admin/teacherschedules.

If you're unfamiliar with custom pages in PowerSchool, you might want to get a quick orientation at PSU 2012 Customization: Introduction.

First, let me show you the file I created (which I just called export.csv), and then I'll break it down step by step so you can understand a bit how it works.

First of all, even though the file extension is .csv (comma-separated values) instead of .html, PowerSchool's custom pages engine will interpret it the same way it would a regular .html file.

Okay. Here's the code (explanation to follow):

"Email","Last Name","First Name","Course","Section","Room","Expression","Teacher"~[tlist_sql;
SELECT (st.student_web_id || '@siprep.org') email, st.first_name, st.last_name, c.course_name course, s.section_number section, s.room, s.expression, tea.lastfirst teacher
FROM teachers tea
INNER JOIN sections s
ON tea.id=s.teacher
INNER JOIN courses c
ON s.course_number=c.course_number
INNER JOIN terms ter
ON s.termid=ter.id
INNER JOIN cc
ON s.id=cc.sectionid
INNER JOIN students st
ON cc.studentid=st.id
WHERE
tea.dcid=~(rn) AND sysdate between ter.firstday and ter.lastday
ORDER BY s.id, st.lastfirst]
"~(email)","~(last_name)","~(first_name)","~(course)","~(section)","~(room)","~(expression)","~(teacher)"[/tlist_sql]
That's the file. Let's look at it piece by piece...

"Email","Last Name","First Name","Course","Section","Room","Expression","Teacher"~[tlist_sql;
The first line is basically the header line for the .csv file, so when you open it up in Excel or Numbers (or upload it to Google Docs and open it in Sheets), you'll have a header row to sort by. Notice there is no space between the end of the header row and the beginning of the ~[tlist_sql; tag. If you put in a carriage return there, you'll get an extra empty row between your header row and the first row of returned data.

SELECT (st.student_web_id || '@yourschool.edu') email, st.first_name, st.last_name, c.course_name course, s.section_number section, s.room, s.expression, tea.lastfirst teacher
This is a standard opening to a SQL query. For some reason, I couldn't find the field for the student email address, so I just concatenated the student_web_id field with our domain name. For the sake of brevity and clarity, I created aliases for the tables we're using (that's why you see st.first_name instead of students.first_name) as well as aliases for the field names themselves (so, for example, you see section as the header in your spreadsheet instead of section_number).

FROM teachers tea
INNER JOIN sections s
ON tea.id=s.teacher
INNER JOIN courses c
ON s.course_number=c.course_number
INNER JOIN terms ter
ON s.termid=ter.id
INNER JOIN cc
ON s.id=cc.sectionid
INNER JOIN students st
ON cc.studentid=st.id

Here we're just getting all the relevant table joins. The teachers table is connected to the sections table, which is connected to the courses, terms, and cc tables, and the cc table is connected to the students table. It did look as if the cc table itself may have most of the relevant information in there, but when I tried to build a query with it earlier, I got funky results back. I may not have been doing it right, so if you want to use the cc table instead of doing this many joins, go for it!

WHERE tea.dcid=~(rn) AND sysdate between ter.firstday and ter.lastday
We want to limit it to just this particular teacher's classes. So if you're passing along the frn (you'll see it in the URL of the page, which will look something like admin/teacherschedules/sched.html?frn=005####&dispic=1 if you open up the teacher current schedule in a new tab, where #### is the teacher's actual ID number. The frn, I believe is the full number including the teachers table (so 005####), and the rn should be just the tailend, which is just the teacher's ID (####).

The way to reference the rn is with ~(rn)

I'm not sure if it's just peculiar to how our school does terms, but we have year-long terms and also semester-long terms, so I found the most effective way to find the current classes for the teacher in question was to search for a sysdate between the first day and last day of the terms.

ORDER BY s.id, st.lastfirst]
You can choose to sort it however you want. I did it by section ID and then by student's last name and first name.

"~(email)","~(last_name)","~(first_name)","~(course)","~(section)","~(room)","~(expression)","~(teacher)"[/tlist_sql]
When you display out the results of the SQL query, you can't just pick any field names—they have to be in the order you called them in the original SQL query. So I put email first in the original SQL query, so I have to display it out with email first.

Also, just as with the header row, do not put a carriage return after the row of returned items and before the closing [/tlist_sql] tag. Otherwise, you'll have blank rows every other row in your .csv.

That's pretty much it. If you then call up export.csv?frn=005#### with the teacher's ID number in place of ####, it should automatically download a .csv of the teacher's student roster by section.