How can we help?

[d] Powerschool SIS 7.8.0+ Custom SQL Query Solution

Follow

Looking for a better way to integrate with PowerSchool SIS? Check out this article!

Please Note: This is a community supplied solution. The contributor of these queries reports success with these queries at their school and have used them to generate successful reports.

However, since this is a community supplied solution we cannot guarantee that it will work with your data. Please be sure to check the output against the data within PowerSchool Learning (especially import_ids).

In addition, we realize this does not pull data for students/parents (users.csv). We hope this is a great start.

We are deeply appreciative of our schools and their contributions to our tech community. If you can add to this solution, please contact us at integrations@PowerSchool Learninglearning.com

--PowerSchool Learning Support Team

 

CLASSES
========
Report Header
<th>import_id</th><th>name</th><th>shortname</th><th>description</th><th>code</th><th>year</th><th>teacher_id</th><th>organization_id</th>

SQL Query
select '<td>'|| s.id||'</td><td>'|| c.course_name||'</td><td>'|| ''||'</td><td>'|| ''||'</td><td>'|| s.course_number||'</td><td>'|| t.yearid||'</td><td>'|| s.teacher||'</td><td>'|| sc.id||'</td>' from sections s join courses c on s.course_number = c.course_number join teachers te on s.teacher = te.id join terms t on s.termid = t.id and s.schoolid = t.schoolid join schools sc on s.schoolid = sc.school_number where t.yearid = 23 and te.status = 1;

ROSTER
========
Report Header
<th>class_id</th><th>user_id</th><th>role</th>

SQL Query
select '<td>'|| cc.sectionid||'</td><td>'|| st.ID||'</td><td>'|| 'S'||'</td>' from cc join students st on cc.studentid = st.id join sections s on cc.sectionid = s.id join terms t on s.termid = t.id and s.schoolid = t.schoolid where t.yearid = 23 and st.enroll_status = 0;

USERS (faculty only)
=================
Report Header
<th>import_id</th><th>first_name</th><th>middle_name</th><th>last_name</th><th>suffix</th><th>nickname</th><th>login</th><th>password</th><th>email</th><th>user_type</th><th>organization_id</th><th>enabled</th><th>display_id</th><th>google_email_address</th>

SQL Query
select '<td>'|| t.id||'</td><td>'|| t.first_name||'</td><td>'|| ''||'</td><td>'|| t.last_name||'</td><td>'|| ''||'</td><td>'||
''||'</td><td>'|| t.LoginID||'</td><td>'|| ''||'</td><td>'|| t.email_addr||'</td><td>'|| 't'||'</td><td>'|| s.id||'</td><td>'|| '1'||'</td><td>'||t.id||'</td><td>'|| t.email_addr||'</td>' from
teachers t join schools s on t.schoolid = s.school_number where status = 1;

ORGANIZATIONS
==============
Report Header
<th>import_id</th><th>name</th><th>parent_org_id</th>

SQL Query
select '<td>'|| s.id||'</td><td>'|| s.name||'</td><td>'|| ''||'</td>' from schools s;

USER LEVELS
============
Report Header
<th>user_id</th><th>level_id</th>

SQL Query
select '<td>'|| student_number||'</td><td>'|| grade_level||'</td>' from students where enroll_status = 0;
classes_SQL.png
Powered by Zendesk