Audit the Grads- PowerSchool Graduated Students' Data

PowerSchool, PowerQuery, SQL, Audit, Data Compliance, Keep it Real

Overtime, when we clean up our data, we often forget about archival data, inactive data, and data held in stasis.

PowerSchool places students who graduate from high school in the Graduated Students school. This is data in stasis.

Over the years I have seen all kinds of random issues with this data, and those issues can impact graduates/almuni in the future.

Before you begin, I recommend you have SQL Studio installed. You can test the query before uploading the plugin.

I am going to be providing three files needed to make the PowerQuery. You need to copy and assemble them into the installable ZIP file.

If you are a paid subscriber, and you want the ZIP file, complete this form, and I will send you the ZIP.

Step 1: Make your plugin.xml file and add this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<plugin xmlns="http://plugin.powerschool.pearson.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://plugin.powerschool.pearson.com plugin.xsd"
name="Audit Grad Students"
version="1.0"
description="Audit Grad Students">
<oauth></oauth>

	<access_request>
    <field table="STUDENTS" field="student_number" access="ViewOnly" />
    <field table="STUDENTS" field="enroll_status" access="ViewOnly" />
    <field table="STUDENTS" field="entrycode" access="ViewOnly" />
    <field table="STUDENTS" field="EntryDate" access="ViewOnly" />
    <field table="STUDENTS" field="ExitDate" access="ViewOnly" />
    <field table="STUDENTS" field="SchoolID" access="ViewOnly" />
    <field table="STUDENTS" field="FTEID" access="ViewOnly" />
    <field table="STUDENTS" field="Districtentrydate" access="ViewOnly" />
    <field table="STUDENTS" field="Sched_NextYearGrade" access="ViewOnly" />
    <field table="SCHOOLS" field="name" access="ViewOnly" />
    <field table="STUDENTS" field="schoolid" access="ViewOnly" />
    <field table="STUDENTS" field="Next_School" access="ViewOnly" />
    <field table="STUDENTS" field="Sched_Scheduled" access="ViewOnly" />
    <field table="STUDENTS" field="Sched_YearOfGraduation" access="ViewOnly" />
    <field table="STUDENTS" field="Graduated_SchoolName" access="ViewOnly" />
    <field table="STUDENTS" field="Graduated_SchoolID" access="ViewOnly" />
    <field table="STUDENTS" field="lastfirst" access="ViewOnly" />
    <field table="STUDENTS" field="first_name" access="ViewOnly" />
    <field table="STUDENTS" field="last_name" access="ViewOnly" />
    <field table="STUDENTS" field="Grade_Level" access="ViewOnly" />
    <field table="STUDENTS" field="DOB" access="ViewOnly" />
    <field table="STUDENTS" field="Gender" access="ViewOnly" />
</access_request>

	<publisher name="Audit Grad Students">
		<contact email="tony.deprato@domain7.tech"/>
	</publisher>
</plugin>

Step 2: Create the queries_root folder and queries.xml file(mine is named auditgradstudents_named_queries.xml) , then add this to the file:

<queries>
    <query name="domain7.tech.gradstudents" coreTable="Students" flattened="false">
        <description>Student Data Audit</description>
        
<columns>
<column column="Students.student_number">Student_Number</column>
<column column="Students.enroll_status">Enroll_Status</column>
<column column="Students.entrycode">Entry_Code</column>
<column column="Students.EntryDate">EntryDate</column>
<column column="Students.ExitDate">ExitDate</column>
<column column="Students.SchoolID">CurrentSchool</column>
<column column="Students.FTEID">FTEID</column>
<column column="Students.Districtentrydate">DistrictEntryDate</column>
<column column="Students.Sched_NextYearGrade">Sched_NextYearGrade</column>
<column column="Schools.name">SchoolName</column>
<column column="Students.schoolid">CurrSch</column>
<column column="Students.Next_School">Next_School</column>
<column column="Students.Sched_Scheduled">Sched_Scheduled</column>
<column column="Students.Sched_YearOfGraduation">Sched_YearOfGraduation</column>
<column column="Students.Graduated_SchoolName">Graduated_SchoolName</column>
<column column="Students.Graduated_SchoolID">Graduated_SchoolID</column>
<column column="Students.lastfirst">LastFirst</column>
<column column="Students.first_name">First_Name</column>
<column column="Students.last_name">Last_Name</column>
<column column="Students.Grade_Level">Grade_Level</column>
<column column="Students.DOB">DOB</column>
<column column="Students.Gender">Gender</column>
</columns>


    <sql>
      <![CDATA[
    SELECT 
    s.student_number,
    s.enroll_status,
    s.entrycode,   
    s.EntryDate,
    s.ExitDate,
    s.SchoolID,
    s.FTEID,
    s.Districtentrydate,
    s.Sched_NextYearGrade,
    sch.name AS SchoolName,
    s.schoolid AS CurrSch,
    s.Next_School,
    s.Sched_Scheduled,
    s.Sched_YearOfGraduation,
    s.Graduated_SchoolName,
    s.Graduated_SchoolID,
    s.lastfirst,
    s.first_name,
    s.last_name,
    s.Grade_Level,
    s.DOB,
    s.Gender
FROM 
    Students s
INNER JOIN 
    Schools sch ON s.SchoolID = sch.school_number
WHERE 
    s.enroll_status = 3
ORDER BY 
     s.Sched_YearOfGraduation, s.DOB, s.grade_level, s.last_name
]]>
    </sql>
        
        
        
    </query>

</queries>

You will see at the top of this file, domain7.tech.gradstudents, I would recommend adding your own domain before “gradstudents”.

Step 3: Create the permissions_root folder and the permissions.xml file (mine is called auditgradstudents_permission_mappings):

<permission_mappings>
  <permission name='/admin/home.html'>
	<implies allow="post">/ws/schema/query/domain7.tech.gradstudents</implies>
  </permission>
</permission_mappings>

And, update your domain in this file too.

Add Age and a control for the scope of Years

Ok, after you ZIP and run this, you will see it lists all the students in the Graduated School. This could be overwhelming. Here is another script that limits results to the last four years and calculates the age of the student:

SELECT
    s.Graduated_SchoolName,
    s.student_number,
    s.lastfirst,
    s.first_name,
    s.last_name,
    s.Gender,
    s.DOB,
    TRUNC(
        MONTHS_BETWEEN(
            TO_DATE(TO_CHAR(s.Sched_YearOfGraduation) || '-06-01', 'YYYY-MM-DD'),
            s.DOB
        ) / 12
    ) AS Age,
    sch.name AS SchoolName,
    s.EntryDate,
    s.ExitDate,
    s.SchoolID as GradSchoolID,
    s.Sched_YearOfGraduation,
    TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) AS Current_Year
FROM
    Students s
    INNER JOIN Schools sch ON s.SchoolID = sch.school_number
WHERE
    s.enroll_status = 3
    AND s.schoolid = ~(curschoolid)
    AND s.Sched_YearOfGraduation BETWEEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 4
    AND TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
ORDER BY
    s.Sched_YearOfGraduation,
    s.Graduated_SchoolName,
    s.DOB,
    s.grade_level,
    s.last_name;

If you want to modify the range, simply adjust the “4” in this part of the statement,

s.Sched_YearOfGraduation BETWEEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 4

Good luck auditing!

Need more information on PowerQuery File Structure? Here are two resources:

1. D7CC reference lesson

2. Jim Parson’s excellent resource

Copyright © Domain Seven LLC. All rights reserved.
For permissions to use or share any content behind our paywall, please email us at: tonydeprato@domain7.tech .