PowerQuery DATs

The PowerQuery DAT leverage specially crafted PowerQueries that allow for Core, State Reporting, and Customer created PowerQueries to be used in Reports and some Export functions. Access to PowerQueries, and their data, are linked to Page Permissions. Users must have at least Read Only access to the Page listed under the Portal - Page section of the table below for the PowerQuery DAT to return results.

For PowerSchool SIS Technical Contacts who would like to learn more about creating your own PowerQueries for use in the PowerQuery DAT please reference the PowerQuery DAT section of the Developer Site.

Customer created PowerQueries, or those installed as part of a plugin, are considered a customization and their use in the PowerQuery DAT is also considered a customization.

Basic Format: ~(*powerquery;query=[powerqueryname])

Data Access Tag	Description	Portal - Page	Optional Parameters	Available	Version
~(*powerquery;query=com.powerschool.core.dats.students.attendance_daily_by_date_range)	Att_date, Att_comment, Attendance_codeid, Att_code, Description, School_name, School_abbreviation of the Daily Attendance records for the current school and year.	Admin Student Daily Attendance Teachers Students/Parents	arg.startDate MM/DD/YYYY Default - First day of current year arg.endDate MM/DD/YYYY Default - Last day of current year arg.schoolId Default - Current school arg.attendanceCodes Comma separated list of attendance codes	Reports Web page	22.11.0.0
~(*powerquery;query=com.powerschool.core.dats.students.attendance_meeting_by_date_range)	Att_date, Att_comment, Attendance_codeid, Att_code, Description, Period_name, Period_abbreviation, School_name, School_abbreviation, Course_name, Course_number, Teacher_name of the Meeting Attendance records for the current school and year.	Admin Student Meeting Attendance Teachers Students/Parents	arg.startDate MM/DD/YYYY Default - First day of current year arg.endDate MM/DD/YYYY Default - Last day of current year arg.schoolId Default - Current school arg.attendanceCodes Comma separated list of attendance codes arg.courseNumbers Comma separated list of course numbers arg.periodAbbreviations Comma separated list of period abbreviations	Reports Web page	22.11.0.0
~(*powerquery;query=com.powerschool.core.dats.students.student_email)	Student Email	Admin Student Email Teachers Students/Parents		Reports Export Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.students.globalid)	Student Global ID	Admin Access Accounts Teachers		Reports Export Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.students.racecodes)	Student Race Codes	Admin Demographics Teachers	listaggdelim	Reports Export Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.students.late_assignment_counts_year)	Student Course Number, Name, and Count of all late assignments for the currently selected year.	Admin Quick Lookup Scores Teachers Students/Parents	value-delim	Reports Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.students.missing_assignment_counts_year)	Student Course Number, Name, and Count of all missing assignments for the currently selected year.	Admin Quick Lookup Scores Teachers Students/Parents	value-delim	Reports Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.teachers.adminglobalid)	Administrator Global ID	Admin Admin Access/Roles Teachers		Reports Export Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.teachers.teacherglobalid)	Teacher Global ID	Admin School Affiliations Teachers		Reports Export Web page	22.9.0
~(*powerquery;query=com.powerschool.core.dats.teachers.racecodes)	Teacher Race Codes	Admin Staff Information Teachers	listaggdelim	Reports Export Web page	22.9.0

Optional Parameters

listaggdelim (Listagg Delimiter)

What	Specifies the delimiter to use for PowerQueries that utilize the Listagg function.
Keyword	listaggdelim
Default	barecomma
Values	dlf (double line-feed) lf (line-feed) p (HTML paragraph: <p>) br (HTML break: <br>) comma (, ) barecomma (,) semicolon (;) space ( ) pipe (\|) dash ( - )
Notes	Reports do not honor lf or dlf; use p or br instead.
Examples	~(*powerquery;query=com.powerschool.core.dats.students.racecodes;listaggdelim=pipe)

value-delim (Value Delimiter)

What	Specifies the delimiter to use between fields.
Keyword	value-delim
Default	space
Values	dlf (double line-feed) lf (line-feed) p (HTML paragraph: <p>) br (HTML break: <br>) comma (, ) barecomma (,) semicolon (;) space ( ) pipe (\|) dash ( - )
Notes	Reports do not honor lf or dlf; use p or br instead.
Examples	~(*powerquery;query=com.powerschool.core.dats.students.late_assignment_counts_year;value-delim=dash)

fields

What	When a PowerQuery DAT returns more than one field the fields option allows the limiting and ordering of specific fields
Keyword	fields
Default	All fields returned by the PowerQuery DAT
Examples	~(*powerquery;query=com.powerschool.core.dats.students.attendance_daily_by_date_range;fields=Att_date,Att_code)

no-rows-message

What	Value to return when the PowerQuery DAT returns no rows
Keyword	no-rows-message
Default	blank
Examples	~(*powerquery;query=com.powerschool.core.dats.students.attendance_daily_by_date_range;no-rows-message=No Records)

no-value-message

What	Value to return when the PowerQuery DAT returns a blank value
Keyword	no-value-message
Default	blank
Examples	~(*powerquery;query=com.powerschool.core.dats.students.attendance_daily_by_date_range;no-value-message=N/A)