Potential Ontario Scholars Report
Introduction
The Potential Ontario Scholars Report provides a list of students who may be eligible for the Ontario scholar award. The report will list students who have an average that is equal to or greater than the Average Threshold Mark. This is a school-level report that can not be run from the District Office.
Selection Criteria
Selection criteria determine which database records are used in the report. Refer to the selection criteria for analysis when the report does not return the correct records.
Student Selection
The report selects records from the Students table based on the following criteria:
The student must be enrolled in the school running the report.
The student must be enrolled in enough courses to earn a minimum of 6 credits.
If Diploma filter is selected, [S_ON_STU_X]DIPLOMA_DATE must not be null.
If 'Exclude Students with Scholarship Approval Date' filter is selected, [S_ON_STU_X]Scholarship_Date must be null.
The student's 'Exclude From Provincial Reporting' flag ([Students]State_ExcludeFromReporting) must be turned off.
A student is excluded if his/her attendance type (i.e. EnrollmentType field) = 'EX' (excluded) for the enrolment (i.e. Students or ReEnrollments record) associated with, in whole or in part, the run date (i.e. if there is any overlap between the enrolment start and end dates and the run date).
StoredGrades Selection
Note: for the purposes of this report only, if the student does not earn complete credit for a multi-credit course (i.e credit earned is less that the potential credit), the course is processed as if the potential credit = credit earned. In other words, if COURSES_CREDIT_HOURS > 1 and STOREDGRADES.EARNEDCRHRS > 0 but < COURSE.CREDIT_HOURS, then COURSE.CREDIT_HOURS is considered to be equal to STOREDGRADES.EARNEDCRHRS for the calculations described below.
The report selects records from the StoredGrades table based on the following criteria:
A record is excluded if any of the following are true:
interim marks are not to be used (i.e. an interim store code is not selected) and the [StoredGrades]EarnedCrHrs = 0
[CC]TermId and [CC]SectionId for the class associated with the midterm are negative and [StoredGrades]EarnedCrHrs = 0 (i.e. the student dropped the class and did not earn credit)
[StoredGrades]ON_Transcript_W is true (i.e. the full disclosure flag is on)
For a given student, the sum of CALCULATED_MARK/sum of CALCULATED_CREDIT >= selected average threshold, where CALCULATED_MARK and CALCULATED_CREDIT are defined as the accumulated values per SCHOOL/STUDENT/COURSE_NUMBER/TERM from the qualifying records in the STOREDGRADES table filtered by the ON_PS_LIMITEDCOURSES view* and any selected exclusions (with protection against divide by zero errors):
CALCULATED_CREDIT = STOREDGRADES.EARNEDCRHRS + CALCULATED_CREDIT (accumulated thus far)
CALCULATED_MARK = (STOREDGRADES.GRADE * STOREDGRADES.EARNEDCRHRS / COURSES.CREDIT_HOURS + CALCULATED_MARK) * CALCULATED_CREDIT
COURSES.EXCLUDEFROMGPA must not be equal to 1.
If Use Interim Marks is selected, the CALCULATED_CREDIT and CALCULATED_MARK values are derived from the StoredGrades record with a store code where [S_ON_TRB_X]Mark_Type = "M1" or "M2" (i.e. a mid-term mark). If both M1 and M2 marks are present, the one with the highest mark is used:
CALCULATED_CREDIT = COURSES.CREDIT_HOURS + CALCULATED_CREDIT (accumulated thus far).
CALCULATED_MARK = (STOREDGRADES.GRADE * (COURSES.CREDIT_HOURS - CALCULATED_CREDIT)/ COURSES.CREDIT_HOURS + CALCULATED_MARK) * CALCULATED_CREDIT.
ON_PS_LIMITEDCOURSES view selects all columns from STOREDGRADES (including COURSE_CODE, which gets pulled from ALT_COURSE_NUMBER, unless that value is null, in which case COURSE_NUMBER is used). The records are limited by COURSE_CODE, where that value must fulfill the following requirements:
Characters 4 and 5 must be one of the following:4C, 4M, 4U, 4E, 4O, 4T, 4Y, 4Z, 43, 45, 46, 47, 48, OR,
One of the following 4 patterns must apply: 'L_DO', 'L_DU', 'LV_CU', 'LN_EO'.
Report Input
For help with navigation and running the report, refer to How to Find and Generate a Report.
Field | Description |
|---|---|
Select Report | Select one of two choices: "Summary Report" or "Detailed Report". The summary report provides information about each student and the calculated average for that student. The detailed report provides breakdown by course code, including actual and calculated percent and credit, as well as whether the course contains interim marks rather than final marks. |
Include Interim Marks | Check if interim final marks should be used. An interim final is one where the [S_ON_TRB_X]Mark_Type is "M1" or "M2" (mid-term 1 or mid-term 2). The default value is unchecked. If a class has both M1 and M2 marks posted, the one with the highest value is used for course selection and average calculation. |
Sort By | Select from: Student Name, Student Average (descending), or Homeroom and Name. |
Diploma Option | Select whether the report should filter for Students With Diploma Dates, Without Diploma Dates, or all students. |
Exclude Students with Scholarship Approval Date | When checked, the report will exclude students with a Scholarship Approval Date. |
Average Threshold Mark | Should contain a numerical value that will be used to filter out students whose total mark/total credits fall below this threshold. |
Current Selection Students | Choose whether you wish to run the report for the current selection of students or have the report search for students based on current school. |
Include Future Potential Credits | Check if Potential Credits (Current Courses with no marks and future courses) to be considered in the calculations. The default value is unchecked. This flag is to handle Quad/Octo Scheduling Students with a Future Potential of 6 Credits. |
Report Output
Each of the fields displayed in the output of the report are described below. Refer to Understanding the Report Output Table for a definition of each column in the table.
Data Element | Description | [Table]FieldName |
|---|---|---|
Header | ||
Date | This is the date the Potential Ontario Scholars Report was printed. | Entered at Report Runtime in Date of Issue field. (Format: YYYY/MM/DD) |
School District | This is the name of the School District. | [Prefs]value where [Prefs]name = 'DISTRICTNAME' |
School Name | This is the name of the School. | [S_ON_SCH_X]Alt_School_Name if defined, |
Report Type | The "Summary Report" or "Detailed Report". | [PS_Common_Code]description where category='report_type' and code = selected report type from parameter |
Diploma Option | Describes the Diploma Option Selection. | [PS_Common_Code]description where category='diploma_option' and code = selected diploma option from parameter |
Semester | The Current Semester. | abbreviatedCurrentAcademicYear (Powerschool System Variable) |
School Year | The Current School Year. | selectedYear (Powerschool System Variable) |
Sort By | Describes the Sort By Selection. | [PS_Common_Code]description where category='report_sort' and code = selected sort order code from parameter |
Average Threshold Mark | The value against which a student is measured, where sum(CALCULATED_MARK)/sum(CALCULATED_CREDIT) is a larger value than this number. When that value is smaller than this number, the student is excluded from the report. | Not stored in DB. This value is whatever value was entered in the input parameter of the same name. |
Summary Report Data | ||
Student Name | The student's Legal Name. Format: Last, First Middle | [Student]last_name, [Student]first_name [Student]middle_name |
Student Number | The student's student number. | [Students]student_number |
Homeroom | The student's homeroom teacher last name. | [Students]home_room |
Address | The student's address. | [Students]street [Students]city [Students]state [Students]zip |
OEN | The student's Ontario Education Number. | [Students]state_studentNumber |
Gender | The student's gender. Valid values:
| [Students]gender getStudentGenderReportValues function is used to get the student's gender type from the code sets tables. Genders that are Excluded from Reporting are not printed. 'I' is printed for invalid genders |
Diploma Met | The name of diploma or certificate earned by student. | [PS_Common_Code]description where ps_group = 'ON_DropDown_Student' and category = 'Diploma_Type' and code = [S_ON_STU_X]Diploma_Type |
Scholarship Received | The date of scholarship received. | [S_ON_STU_X]Scholarship_Date |
Status in Canada | The student's residency status, or "N/A". | [PS_Common_Code].description WHERE category = 'Residence_Status' and code = [S_ON_STU_X]Residence_Status or "N/A" if that value is null |
Calculated Average | sum(CALCULATED_MARK)/sum(CALCULATED_CREDIT) | sum(CALCULATED_MARK)/sum(CALCULATED_CREDIT) where CALCULATED_MARK and CALCULATED_CREDIT are defined as the accumulated values per SCHOOL/STUDENT/COURSE_NUMBER/TERM from the qualifying records in [StoredGrades] filtered by [ON_PS_LimitedCourses] and any selected exclusions (with protection against divide by zero errors): CALCULATED_CREDIT = [StoredGrades]EARNEDCRHRS + CALCULATED_CREDIT (accumulated thus far) CALCULATED_MARK = ([StoredGrades]GRADE * [StoredGrades]EARNEDCRHRS / [Courses]CREDIT_HOURS + CALCULATED_MARK) * CALCULATED_CREDIT If Use Interim Marks is selected, the CALCULATED_CREDIT and CALCULATED_MARK values are derived from the StoredGrades record with a store code where [S_ON_TRB_X]Mark_Type = "M1" or "M2" (i.e. a mid-term mark). If both M1 and M2 marks are present, the one with the highest mark is used: CALCULATED_CREDIT = [Courses]CREDIT_HOURS + CALCULATED_CREDIT (accumulated thus far) CALCULATED_MARK = ([StoredGrades]GRADE * ([Courses]CREDIT_HOURS - CALCULATED_CREDIT)/ [Courses]CREDIT_HOURS + CALCULATED_MARK) * CALCULATED_CREDIT If Include Future Potential Credits is selected, future potential credit courses will be listed in the report but will not be considered in the Calculated Average calculations. |
Detail Report Data | ||
Course Number | Course Number. | [Courses].course_number |
Actual Mark | Value is calculated by looping through the records returned from STOREDGRADES filtered by the ON_PS_LimitedCourses view. For each record with like SCHOOL/STUDENT/COURSE/TERM the value is derived from percent * earned credits / total credits + previous value. If the marks are not final, the value is derived from percent * (course credits - actual credits) / (course credits + the previous value). Prior to calculating the course average however, records with the same student and course but different marks are examined. From these duplicates, the record with the lower mark is deleted. Also, any course duplicates with identical marks are identified, and only the latest is retained. Finally, actual marks for each student are ordered from highest to lowest and only 6 actual credits worth of these records are retained for calculating the final average. | Value is calculated by looping through records with like SCHOOL/STUDENT/COURSE/TERM data and applying the following appropriate formula (protected from divide by zero errors): IF not interim: [StoredGrades]grade * [StoredGrades]earnedcrhrs / [Courses]credit_hours + previous total ELSE [StoredGrades]grade * ([Courses]credit_hours - ACTUAL_CREDIT) / [Courses]credit_hours + ACTUAL_MARK Actual Mark is blank for potential credits courses ( current courses with no Marks or future courses) |
Actual Credit | Value is determined by adding up the earned credit hours for each STOREDGRADES record with like SCHOOL/STUDENT/COURSE/TERM. If the marks are not final, the value is derived from course credits. | IF not interim: sum([StoredGrades]earnedcrhrs) per SCHOOL/STUDENT/COURSE/TERM ELSE [Courses]credit_hours Actual Credit is blank for potential credits courses ( current courses with no Marks or future courses) |
Calculated Mark | Value is calculated by looping through the records returned from STOREDGRADES filtered by the ON_PS_LimitedCourses view. For each record with like SCHOOL/STUDENT/COURSE/TERM the value is derived from percent * earned credits / total credits + previous value. If the marks are not final, the value is derived from percent * (course credits - actual credits) / (course credits + the previous value). This value is then multiplied by CALCULATED_CREDIT. Prior to calculating the course average however, records with the same student and course but different ACTUAL MARKS are examined. From these duplicates, the record with the lower mark is deleted. Also, any course duplicates with identical ACTUAL MARKS are identified, and only the latest is retained. Finally, ACTUAL MARKS for each student are ordered from highest to lowest and only 6 actual credits worth of these records are retained for calculating the final average. If a partial credit is left over, the calculated mark for the last record is updated as a result of this step. IF [StoredGrades]Grade is null, the mark is considered to be 0 for the purposes of calculation. | Value is calculated by looping through records with like SCHOOL/STUDENT/COURSE/TERM data and applying the following appropriate formula (protected from divide by zero errors): IF not interim: [StoredGrades]grade * [StoredGrades]earnedcrhrs / [Courses]credit_hours + previous total ELSE [StoredGrades]grade * ([Courses]credit_hours - CALCULATED_CREDIT) / [Courses]credit_hours + CALCULATED_MARK The final value is then multiplied by CALCULATED_CREDIT Calculated Mark is blank for potential credits courses |
Calculated Credit | Value is determined by adding up the earned credit hours for each STOREDGRADES record with like SCHOOL/STUDENT/COURSE/TERM. If the marks are not final, the value is derived from course credits. Finally, if a partial credit is left over, the calculated credit for the last record is updated as a result of the max credit adjustment step. | IF not interim: sum([StoredGrades]earnedcrhrs) per SCHOOL/STUDENT/COURSE/TERM ELSE [Courses]credit_hours For potential credits is [Courses]credit_hours |
*Interim Mark | Indicates whether marks and credits for a course represent the final marks or an interim mark based on whether Use Interim Marks. | Derived value based on [StoredGrades]storecode where [S_ON_TRB_X]Mark_Type = "M1" or "M2". If both M1 and M2 marks are present, the one with the highest mark is used. |
** Potential Credits | Indicates potential credits | Derived from [Courses]credit_hours |
Footer | ||
Page Number | Printed as 'XX of YYY' where XX is the current page number and YYY is the total number of pages in the report. | |