We’ve Moved

Please update your bookmarks. Support for PowerSchool Learning has moved to the new PowerSchool Community. Visit the PowerSchool Community to find answers in our knowledge base and participate in discussions.

 

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