PowerSchool SIS Tennessee
PowerSchool SIS Tennessee
Breadcrumbs

Student Course Transcript

Resource Overview

  • Ed-Fi endpoint: /data/v3/courseTranscripts

  • Published view/entity: Student Course Transcript

  • Purpose: The final record of a student's performance in their courses

  • Source query: CourseTranscript

  • Dependencies: studentAcademicRecords

courseTranscript

This resource is the final record of a student's performance in their courses by school year.

Primary PowerSchool Source Data

  • [StoredGrades]

  • [Students]

  • [Courses]

  • [S_TN_USR_X]

  • [S_TN_CRS_X]

Selection Criteria

Records are published if the following criteria are met:

  • The student was published in the Student category.

  • StudentUniqueId must be populated in PowerSchool.

  • The student must not be excluded from state reporting.

  • The student must be enrolled in the district during the school year for the term selected in the PowerSchool UI.

  • The school associated with the stored grades record must not be excluded from state reporting.

  • The section at the school where the grade was recorded must not be excluded from state reporting ("State_ExcludeFromReporting").

  • The stored grades record must not be excluded from transcripts ("excludefromtranscripts").

  • The course must have a valid state course code.

  • The course must not be excluded from printing ("sched_do_not_print").

  • The store code for the grade must be a final grade code for the course term where Storecode is either 'Y1' for a year grade, or matches a term record for the school where [StoredGrades]StoreCode = [Terms]Abbreviation.

  • If the stored grades SectionId is greater than 0, the record is the most recent for the section, student, and store code based on DateStored.

Resource Output

Data Element (Ed-Fi target)

Data View Column Name

Submission

[Table]Field (PowerSchool Source)

Business Rules

Comments

courseReference.educationOrganizationId

LEAOrgID

Y

Staging rule: courseTranscripts

'47'

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: LEAOrgID.

courseReference.courseCode

LocalCourseCode

Y

[Courses]DCID; [CST_PUBCRSTRANSCRIPT_TMP]course_numbe;

Transformation: CST_PUBWORKFLOW_LIB.formaLocalCourseCodeBase(p_profileid, crs.dcid, ct.course_number)

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: LocalCourseCode.

externalEducationOrganizationReference.educationOrganizationId

ExtEduOrgID

Y

[StoredGrades]Sectionid; [StoredGrades]Schoolid;

Transformation: CASE WHEN sg.Sectionid =0 AND sg.schoolid != p_schoolid THEN CST_PUBWORKFLOW.formatLEASchoolID(p_profileid, sg.schoolid) ELSE NULL END

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: ExtEduOrgID.

studentAcademicRecordReference.studentUniqueId

StudentUID

Y

[Students]DCID;

Transformation: EDFI_TN_LIB.getStudentUniqueId(p_profileid, s.dcid)

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: StudentUID.

studentAcademicRecordReference.educationOrganizationId

SubEduOrgID

Y

Staging rule: courseTranscripts

CST_PUBWORKFLOW.formatLEASchoolID(p_profileid, p_schoolid)

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: SubEduOrgID.

studentAcademicRecordReference.schoolYear

SchoolYear

Y

[CST_PUBCRSTRANSCRIPT_TMP]YEARID

Required by the production resource definition when the resource is published.

JSON type: number. Query alias: SchoolYear.

studentAcademicRecordReference.termDescriptor

Term_DX

Y

[CST_PUBCRSTRANSCRIPT_TMP]TERMDESCRIPTOR

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: Term_DX.

courseAttemptResultDescriptor

CourseAttemptResult_DX

Y

[CST_PUBCRSTRANSCRIPT_TMP]COURSEATTEMPTRESULT

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: CourseAttemptResult_DX.

attemptedCredits

AttemptedCredits

Conditional

[StoredGrades]Grade_level; [StoredGrades]Potentialcrhrs;

Transformation: case when sg.grade_level < 9 then '0' else rtrim(to_char(sg.potentialcrhrs,'FM99990.999'),'.') end

Submit when the source alias is populated and the parent structure is applicable.

JSON type: number. Query alias: AttemptedCredits.

earnedCredits

EarnedCredits

Y

[StoredGrades]Grade_level; [StoredGrades]Earnedcrhrs;

Transformation: case when sg.grade_level < 9 then '0' else rtrim(to_char(coalesce(sg.earnedcrhrs, 0),'FM99990.999'),'.') end

Required by the production resource definition when the resource is published.

JSON type: number. Query alias: EarnedCredits.

whenTakenGradeLevelDescriptor

GradeLevel_DX

Conditional

[StoredGrades]Grade_Level; [S_TN_STU_X]TransitionalGrade;

Transformation: EDFI_TN_LIB.getGradeLevelDescriptor(p_profileid, sg.Grade_Level,xts.TransitionalGrade)

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: GradeLevel_DX.

methodCreditEarnedDescriptor

MethodCreditEarnedType_DX

Conditional

[StoredGrades]SectionId; [StoredGrades]Storecode;

Transformation: case when sg.sectionid = 0 and sg.storecode != 'SS' then 'Transfer credit' else 'Classroom credit' end

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: MethodCreditEarnedType_DX.

finalLetterGradeEarned

FinalLetterGrade

Conditional

[StoredGrades]Grade;

Transformation: case when trim(sg.grade) IS NOT NULL and REGEXP_LIKE(trim(sg.grade), '^[[:digit:]]+(\.[[:digit:]]+)?$') then case when to_number(trim(sg.grade)) >= 90 then 'A' when to_number(trim(sg.grade)) >= 80 then 'B' when to_number(trim(sg.grade)) >= 70 then 'C' when to_number(trim(sg.grade)) >= 60 then 'D' else 'F' end else trim(sg.grade) end

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: FinalLetterGrade.

finalNumericGradeEarned

FinalNumericGrade

Conditional

[StoredGrades]Percent; [StoredGrades]Grade; transformation: case when sg.percent > 0 then to_char(sg.percent) when sg.grade is null then '0' else null end

Submit when the source alias is populated and the parent structure is applicable.

JSON type: number. Query alias: FinalNumericGrade.

courseTitle

CourseTitle

Conditional

[Courses]Course_name; [CST_Pubcourse]TITLE;

Transformation: nvl(trim(crs.course_name),trim(pc.TITLE))

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: CourseTitle.

alternativeCourseTitle

AlternativeCourseTitle

Conditional

[StoredGrades]Course_number; [Courses]Course_number; [Courses]Course_name; [StoredGrades]Course_name;

Transformation: case when trim(sg.course_number) != trim(crs.course_number) then crs1.course_name when trim(sg.course_name) != trim(crs.course_name) then trim(sg.course_name) else null end

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: AlternativeCourseTitle.

alternativeCourseCode

AlternativeCourseCode

Conditional

[StoredGrades]Course_number; [Courses]Course_number; [Courses]DCID;

[CST_PUBCRSTRANSCRIPT_TMP]ALT_COURSE_NUMBER;

Transformation: case when trim(sg.course_number) != trim(crs.course_number) then CST_PUBWORKFLOW_LIB.formaLocalCourseCodeBase(p_profileid, crs.dcid, sg.course_number) when ct.ALT_COURSE_NUMBER != trim(crs.course_number) then ct.ALT_COURSE_NUMBER when crs.course_number != CST_PUBWORKFLOW_LIB.formaLocalCourseCodeBase(p_profileid, crs.dcid, crs.course_number) then CST_PUBWORKFLOW_LIB.formaLocalCourseCodeBase(p_profileid, crs.dcid, crs.course_number) else null end

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: AlternativeCourseCode.

externalEducationOrganizationNameOfInstitution

Externalinstitution

Conditional

[StoredGrades]SchoolName

Submit when the source alias is populated and the parent structure is applicable.

JSON type: string. Query alias: Externalinstitution.

earnedAdditionalCredits[].additionalCreditTypeDescriptor

additionalCredit_DX

Y

[S_TN_CRS_X]QualityPoints;

Transformation: CASE WHEN crx.QualityPoints is not NULL then 'Quality Points' END

Required by the production resource definition when the resource is published.

JSON type: string. Query alias: additionalCredit_DX.

earnedAdditionalCredits[].credits

credits

Y

[S_TN_CRS_X]QualityPoints; transformation: CASE WHEN crx.QualityPoints is not NULL then crx.QualityPoints END

Required by the production resource definition when the resource is published.

JSON type: number. Query alias: credits.