Skip to main content
Skip table of contents

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 TagDescriptionPortal - PageOptional ParametersAvailableVersion
~(*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)

WhatSpecifies 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)

WhatSpecifies 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

WhatWhen 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

WhatValue 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

WhatValue 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)

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.