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.

Leave a Reply

Your email address will not be published. Required fields are marked *