Assignment Data


Description

Access PESB's data related to Washington's Content Assignment Rules.  Programmatically returns data matching teaching credentials to course.  This information is helpful for contextualizing OSPI CEDARS Course Data.  

URL

https://script.google.com/macros/s/AKfycbyWrG23WgMx0cbqKaLla6QyZRoMs-k4iiM-za-aKDC8aBRj6O8/exec


Method

GET


Parameters

 Parameters  TYPE

 count

 STRING

 assessment

 BOOLEAN

 schoolYear

 STRING

 districtCode 

 INTEGER

 schoolCode 

 INTEGER

 courseCategory

 STRING

 courseRigor

 STRING

 courseCode

 STRING

 certificateNumber

 STRING

 Special Parameters

 Type

 getRules

 BOOLEAN

 getAllDistricts

 BOOLEAN

 getAllSchools

 BOOLEAN



Base Table

A call to this url will return the base table, which has the following elements.
 Elements  Type  Description

 SchoolYearCode

 STRING

 The academic year associated with this row

 Category

 STRING

 The base category, such as Art, Mathematics, or Science


Example

Return all years and course categories.
Note - this has the returnType category set to CSV to make it easier to read.  See here for additional returnType options.

Select Elements to Count

count

Adding the count parameter defaults to counting students.

Added Elements

 Elements  Type  Description

 CountType

 STRING

Adds the count parameter value (Student, Teacher, or Course)


 Count

 FLOAT

 The number of students, teachers, or courses.

Valid Values

 Value  Description

 student

Returns counts by students enrolled in the course.  This value considers the term length.  For instance, a student enrolled in;

  • A year-long course is counted as "1"
  • A semester course is counted ".5"
  • A trimester course is counted as ".33"
Note - student is the default value if the count parameter is used

 course

Returns counts by course.  This value considers the term length.  For instance, a course with a term-code indicating;

  • A year-long course is counted as "1"
  • A semester course is counted ".5"
  • A trimester course is counted as ".33

 teacher

Returns counts by unique teacher.  For instance, if one teacher is teaching 4 science courses, the value is returned as '1'.  


Example


assignment

The assignment parameter adds three elements to the base table.  This is essentially a pivot of the count parameter by assignment type, which is calculated using the course code as well as the instructors' endorsements on their teaching credential. 

Added Elements

 Elements  Type  Description is count is noted as student

 CountType

 STRING

 Adds the count parameter

 MatchedEndorsement

 FLOAT

Number of students enrolled in courses with a teacher having an endorsement that matches the course content.  

 NoMatchedEndorsement

 FLOAT

Number of students enrolled in courses with a teacher not having an endorsement that matches the course content.   

 Grandfathered

 FLOAT

Number of students enrolled in courses with a teacher having an older teaching credential (before information on subject endorsements were collected).   


Valid Values

 Value  Description

 true

Adds all assignment elements to the return table 

 match

Adds only the CountType and MatchedEndorsement elements.

 no_match

Adds only the CountType and NoMatchedEndorsement elements. 

 grandfather

Adds only the CountType and Grandfathered elements


Example

Count the number of teachers teaching at least one course that does not match their endorsements.

Drill-down and/or limit parameters

The parameters below are used to drill down (add elements) to the query as well as liming the values returned.  



schoolYear

Limits by school year. If empty, the results will include all available data. 

Added Elements

School Year Code is returned by default.

Valid Values

 Value  Description

 latest

The latest school year

 2013-2014

 Returns the results from the 2013-2014 school year.

 2012-2013

Returns the results from the 2012-2013 school year.

 2011-2012

Returns the results from the 2011-2012 school year.



districtCode

Adds district elements to the base table.  Use a district code to limit to a specific district, or use 'true' to return all districts.

Added Element

 Elements  Type  Description

 DistrictCode

 INTEGER

OSPI's County/District Code.


 DistrictName

 STRING

The school district name.


Valid Values

 Value  Description

 true

Returns all information, but adds the district elements

 INTEGER

Adds the district elements and limits the return to only this district code. 


Example

From the latestYear, count the number of students by district where districtCode=6114 and pivot by assignment status
Note, returning districtCode=true without other limiting factors will take longer to return (large file).  Developers are encouraged to cache this information to avoid latency issues.  


schoolCode


Adds district and school elements to the base table.  Use a school code to limit to a specific school, or use 'true' to return all schools.  Can be combined with districtCode parameter to return school level data from a specific district.

Added Element

 Elements  Type  Description

 DistrictCode

 INTEGER

OSPI's County/District Code.


 DistrictName

 STRING

The school district name.

 SchoolCode

 INTEGER

OSPI's school building code

 SchoolName

 STRING

The school name. 


Valid Values

 Value  Description

 true

Returns all information, but adds the school and district elements

 INTEGER

Adds the school and district elements and limits the return to only this school code. 


Example

Count the number of students by school where districtCode=6114 and pivot by assignment status


schoolLevel

Level includes definitions such as High School, Middle School, and Elementary School.  Add schoolCode=true to drill down to the actual schools. 
Note: This API is currently set to limit data to only High School (schoolLevel=high).

Added Element

 Elements Type Description

 DistrictCode

 INTEGER

OSPI's County/District Code.


 DistrictName

 STRING

The school district name.

 SchoolLevel

 STRING

The level associated with the school.

 SchoolName

 STRING

The school name. 


Valid Values

 Value Description

 true

Returns all information, but adds the school level and district elements

 high

Adds the school level and district elements and limits the return to only schools which have the lowest grade as 9th, 10th, 11th, or 12th. 


Example


courseCategory

The category, such as Music or Mathematics.

Added Elements

Course Category is returned by default.

Valid Values

Note: It is best practice to encode url parameters that contain spaces.

 Value Description

 art

 Returns only Art courses.

 drama

Returns only Drama courses.

 english language arts

Returns only English Language Arts courses.

 health and pe

 Returns only Health and PE courses.

 mathematics

 Returns only Mathematics courses.

 music

 Returns only Music courses.

 science

 Returns only Science courses.

 traffic safety

 Returns only Traffic Safety courses.

 world language

 Returns only World Language courses.


Example

From the latestYear, count the number of students by district where districtCode=6114 and pivot by assignment status

courseRigor


Rigor relates to whether the course may be offered as high school graduation credit.
Note, currently all queries from this API return only courseRigor = high. 

Added Element

 Elements Type Description

 CourseRigor

 STRING

OSPI's rigor code for this course


Valid Values

 Value Description

 true

Returns all information, but adds the rigorelements

 high

Adds the rigor element and limits the return to high school rigor courses.


Example



courseCode


Adds course elements to the base table.  Use a course code to limit to a specific course, or use true to return all courses.  Can be combined with the districtCode and schoolCode parameters  For instance, to return all course data for a specific district use

Added Element

 Elements Type Description

 StateCourseCode

 INTEGER

OSPI's state course code.


 StateCourseName

 STRING

The name of the course.


Valid Values

 Value Description

 true

Returns all information, but adds the course elements

 INTEGER

Adds the course elements and limits the return to only this course code. 



Example

Count the number of students by course where districtCode=6114 and pivot by assignment status
Count the number of students by course and school where districtCode=6114 and pivot by assignment status - return csv and download.


certificateNumber


Adds certificate numbers to the base table.  Use a specific certificate number, or use true to return all certificate numbers.  Can be combined with the districtCode, schoolCode, category, and courseCode parameters.  







Special Parameters

Special parameters work independently to extract helpful information.  If there is a special parameter included in your fetch it will ignore other parameters.  If you have multiple special parameters it will default to the first special parameter listed below.

getRules

Gets the rules used to generate the assignment report.

Example

By itself, getRules returns all rule data.  
Note, this is a large table and takes a few minutes to return, so we recommend caching.  

Limiting Parameters for getRules

These parameters can be added to the get rules parameter.

stateCourseCode

The State Course Code is a string of 5 digits. Sending a partial code returns all codes that contain the partial string. 

Example

Return all assignment rules currently associated with the course "English/Language Arts I (9th grade) 01001"

endorsementTypeCode

The Endorsement Type Code must be a positive integer. Codes are maintained by OSPI. 

Examples

Get all rules for endorsement code 3800 (English Language Arts Endorsement)


getAllDistricts

This special parameter returns all districts contained in the assignment data. 

Added Element

 Elements Type Description

 DistrictCode

 INTEGER

OSPI's County/District Code.


 DistrictName

 STRING

The school district name.


Valid Values

 Value Description

 true

Returns all information.


Example

Get all districts associated with this data

getAllSchools

This special parameter returns schools for a specific district.

Added Element

 Elements Type Description

 DistrictCode

 INTEGER

OSPI's County/District Code.


 DistrictName

 STRING

The school district name.

 SchoolLevel

 STRING

The level associated with the school.

 SchoolName

 STRING

The school name. 


Valid Values

 Value Description

 INTEGER

District Code


Example

Get all schools associated with 27403 (Bethel School District)





Return Type and Download

Select how data returns as well as an option to download using PESB's shared API parameters.