Tool Search: KECS LEAD Extract
KECS LEAD Extract Detail Report
Detail Report Logic
The report can be run against a district’s active year as well as any prior year in District Edition. The report will be produced with a custom name in the following format: KECS_LEAD_ReportName.csv/html. Enrollments and Courses must be active to be included in the report. Students must be rostered on the last day of the section to be included in the report. Calendar year should overlap the Extract Editor date ranges. Schools must have a State Classification of A1, A2, A3, A4, A5, A6, A7, A8, B1, B2, C1, C2, or D1 to be included in the report. Calendars, Grade Levels, and Enrollments marked as 'exclude' are included in this report. Enrollments marked as 'no show' and calendars marked as 'summer school' are NOT included. If a student has multiple enrollments, the most recent will be used. If there are multiple active enrollments with the same start dates and same end dates (or all end dates null), look at Service Type P, then highest enrollmentID.
Detail Report Layout
| Data Element | Requirement/Rules | Path | Format |
|---|---|---|---|
| Date Time Generated | Reports the date and time the extract was generated. | N/A | MM/DD/YYYY HH:MM |
| End Year | Reports the end year for a reported school session. | System Administration > Calendar > School Year > End Year | XXXX 4-digit number (e.g. 2022 for SY 21-22) |
| Section ID | Reports the ID for a reported school section. Identifying course sections within a calendar and schedule structure: • Course must be active • Section must be in the active trial • Section must have section placement • Include sections even if the roster is empty • Exclude section if the Term Start Date or Term End Date is NULL | Search > Course/Section > Section > SectionID | Integer |
| District Number | Reports the unique number or alphanumeric code assigned to the reporting district. | System Administration > Resources > District Information > District > State District Number | XXX 3-digit number |
| District Name | Reports the name of the reporting district. | System Administration > Resources > District Information > District > Name | Text |
| School Number | Reports the unique number or alphanumeric code assigned to the reporting school. | System Administration > Resources > School > Select School > Location Number | XXX 3-digit number |
| School Name | Reports the name of the reporting school. | System Administration > Resources > School > Select School > Name | Text |
| State Classification | Reports the state assigned school classification. See Appendix A for possible values. | System Administration > Resources > School > Select School > State Classification | Code (e.g. A1) |
| Calendar Name | Reports the name of the calendar. | System Administration > Calendar > Calendar > Name | Text |
| Schedule Structure Name | Reports the calendar’s schedule structure name. | System Administration > Calendar > Schedule Structure > Schedule Structure Detail > Name | Text |
| Last Instructional Day | Reports the last instructional day of the calendar and structure name. | System Administration > Calendar > Calendar > Days | MM/DD/YYYY |
| Low Grade | Reports the lowest grade of the calendar and structure name. Report the lowest defined state grade level for the selected calendar’s schedule structure where excludeEnrollment = 0. Order from lowest to highest: 95, 96, 97, 98, 99, 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 14, 20. | System Administration > Calendar > Calendar > Grade Levels | Code (e.g. 01) |
| High Grade | Reports the highest grade of the calendar and structure name. Report the highest defined state grade level for the selected calendar’s schedule structure where excludeEnrollment = 0. Order from lowest to highest: 95, 96, 97, 98, 99, 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 14, 20. | System Administration > Calendar > Calendar > Grade Levels | Code (e.g. 12) |
| Course Number | Reports the local course number. | Search > Course/Section > Course > Number | Char(13) (e.g. 0456V) |
| Course Name | Reports the course name. | Search > Course/Section > Course > Name | Text |
| Section Number | Reports the section number for the course. | Search > Course/Section > Section > Number | Integer |
| State Course Code | Reports the state’s course code. | Search > Course/Section > Course > State Code | XXXXXX Char(6) |
| Core Content | Reports section level content indicator. | Search > Course/Section > Section > Core Content | Code (e.g. 101) |
| Teaching Method | Reports the course’s teaching method. Possible values are: • 01: Direct Instruction • 02: 3rd Party Contract • 10: Digital Learning Provider • 11: Dual Credit – District Offered • 12: Dual Credit – College Offered • 13: Credit Recovery – Direct Instruction • 14: Credit Recovery – Digital Learning Provider • 19: District Provided Self Study If Teaching Method is NULL, report 01. | Search > Course/Section > Course > Teaching Method | Code (e.g. 01) |
| Instructional Setting | Reports the course’s instructional setting. Possible values are: • 01: Onsite Classroom • 02: Offsite CTE • 03: Offsite College • 04: Home/Hospital • 05: Online • 06: Blended Learning • 11: Spanish Immersion • 12: French Immersion Instructional Setting is determined by the value set on the section in the Instructional Setting (Override) field. If Instructional Setting (Override) is NULL, Instructional Setting is determined by the value for Instructional Setting on the course. If course Instructional Setting is NULL, report 01. | Scheduling > Courses > Course > Section > Instructional Setting (Override) IF NULL: Scheduling > Courses > Course > Instructional Setting | Code (e.g. 01) |
| Section Special Type | Reports the section’s special classroom type. Possible values are: • 01: None • 02: Alternative Classroom • 03: ESL Classroom • 04: Gifted Classroom • 05: Special Education Classroom • 06: Speech Therapy If Special Type is NULL, report 01. | Search > Course/Section > Section > Special Type | Code (e.g. 01) |
| KTS Exchange | Indicator that the course is part from the KTS Data Exchange. Possible values are: • Has Value = 1 • NULL = 0 | Refer to Appendix E for details on where to pull the information for this element. | 1 or 0 |
| Responsive | Reports the course’s Responsive checkbox value. Possible values are: • Checkbox checked = 1 • Checkbox unchecked = 0 | Search > Course/Section > Course > Responsive | 1 or 0 |
| Primary Teacher PersonID | Reports the course’s active or last primary teacher’s personID. Select Staff Section History records for the section where: • Staff Type = Primary Teacher (P) • Start Date is NULL or before or between the calculated Section Start Date and Section End Date • End Date is NULL or between the calculated Section Start Date and Section End Date • If End Date is between calculated Section Start Date and Section End Date report Staff if Report Run date is less than or equal to Staff End Date. If multiple staff are returned by the above logic, return the staff where End Date is NULL. If all have an End Date, then return the staff with the latest (most recent) Start Date. Report as 0 if no staff are returned. | Census > People > Demographics > Person Information > PersonID | Text |
| Primary Teacher First Name | Reports the preferred first name of the course’s active or last primary teacher’s personID. Report the preferred first name associated with the Primary Teacher PersonID. | Census > People > Demographics > Identities > First Name | Text |
| Primary Teacher Last Name | Reports the preferred last name of the course’s active or last primary teacher’s personID. Report the preferred last name associated with the Primary Teacher PersonID. | Census > People > Demographics > Identities > Last Name | Text |
| Section Start Date | Reports the section’s start date. Use sectionPlacement to determine the starting term for the section and report that term.startdate. | Search > Course/Section > Section | MM/DD/YYYY |
| Section End Date | Reports the section’s end date. Use sectionPlacement to determine the ending term for the section and report that term.enddate. | Search > Course/Section > Section | MM/DD/YYYY |
| State ID | Reports the student’s State ID. Only report students who are enrolled on the last day of the section: • Roster End Date is NULL or • Roster End Date = Section End Date or • Roster End Date = Last Instructional Day If no students are active on the last day of the section, include one row for the section with NULL for StateID and all other student fields. | Census > People > Demographics > Person Identifiers > Student State ID | |
| Student First Name | Reports the student’s preferred first name. Report the preferred first name associated with the StateID. | Census > People > Demographics > Identities > Identity Information > First Name | |
| Student Last Name | Reports the student’s preferred last name. Report the preferred last name associated with the StateID. | Census > People > Demographics > Identities > Identity Information > Last Name | |
| Gender | Report the student’s Gender. Report as the following: • Male = M • Female = F | Census > People > Identities > Identity Information > Gender | M or F |
| Race Ethnicity | Report the student’s Race Ethnicity. Report as the following: • 1 = Hispanic or Latino • 2 = American Indian or Alaska Native • 3 = Asian • 4 = Black or African American • 5 = Native Hawaiian or Other Pacific Islander • 6 = White • 7 = Two or More Races | Census > People > Identities > Identity Information > Race/Ethnicity | Code (e.g. 1) |
| Roster Start Date | Reports the section’s start date based on the roster start date. If Roster.StartDate is NULL, report BLANK. | Search > Course/Section > Section > Roster | MM/DD/YYYY |
| Roster End Date | Reports the section’s end date based on the roster end date. If Roster.EndDate is NULL, report BLANK. | Search > Course/Section > Section > Roster | MM/DD/YYYY |
| Primary Disability in Section | Report the primary disability for a student on the last day of a section. See Appendix B for possible values. Select the IEP from the latest (most recent) End Date that is locked and has a date range valid at any time between the Section Start Date and Section End Date when the Section Start Date <= Today OR between the active Enrollment Start Date and Enrollment End Date when Section Start Date is in the future. • If multiple SPED plans have the same End Date, then of those plans, select the plan with the latest (most recent) Start Date. • If multiple SPED plans still exist after the above, select the plan with the highest planID. • If Enrollment End Date is NULL, use the earliest of Last Instructional Day or Current Date when the section is in the future. Once the qualifying plan is selected using the above criteria, all the following must be true for the student to be counted as SPED: • Primary Disability is NOT NULL • Special Ed Status is NOT NULL | Student Information > Special Ed > General > Documents > KY IEP > Enrollment Status > Primary Disability | Code (e.g. 01) |
| EnrollmentID | Report the last enrollment in the calendar’s schedule structure for the student on the roster. Report the latest (most recent) active enrollment. | Student Information > General > Enrollments > Enrollment Editor > Enrollment ID | Text |
| Grade | Report the grade level of the student. Possible values are all grade levels 95 through 20. | Student Information > General > Enrollments > Enrollment Editor > Grade | Code (e.g. 01) |
| Service Type | Report the service type of the student’s enrollment. Possible values are: • P: Primary • S: Partial • N: Special Ed Services | Student Information > General > Enrollments > Enrollment Editor > Service Type | Code (e.g. P) |
| Enrollment Start Date | Report the start date of the student’s last enrollment. | Student Information > General > Enrollments > Enrollment Editor > Start Date | MM/DD/YYYY |
| Enrollment End Date | Report the end date of the student’s last enrollment. If Enrollment End Date is NULL, report blank. | Student Information > General > Enrollments > Enrollment Editor > End Date | MM/DD/YYYY |
| Enrollment Start Status | Report the start status of the student’s last enrollment. Possible values are: • E01 • E02 • E03 • E98 • NS • R01 • R02 • R06 • R20 • R21 | Student Information > General > Enrollments > Enrollment Editor > State Start Status | Code (e.g. E01) |
| Enrollment End Status | Report the end status of the student’s last enrollment. See Appendix C for possible values. If Enrollment End Status is NULL, report blank. | Student Information > General > Enrollments > Enrollment Editor > State End Status | Code (e.g. W22) |
| Primary Disability Enrollment | Report the primary disability for a student’s enrollment. See Appendix B for possible values. Select the IEP from the latest (most recent) End Date that is locked and has a date range valid at any time between the active Enrollment Start Date and Enrollment End Date.• If multiple SPED plans have the same End Date, then of those plans, select the plan with the latest (most recent) Start Date. • If multiple SPED plans still exist after the above, select the plan with the highest planID. • If Enrollment End Date is NULL, use the earliest of the Last Instructional Day or Current Date. Once the qualifying plan is selected using the above criteria, all the following must be true for the student to be counted as SPED: • Primary Disability is NOT NULL • Special Ed Status is NOT NULL • Special Ed Setting is NOT NULL | Student Information > Special Ed > General > Documents > KY IEP > Learner Characteristics Inventory | Code (e.g. 01) |
| English Learner | Report if a student is an English Learner. A student is considered an English Learner if: • Home Primary Language is NOT 0400 AND is NOT NULL • AND Student has at least one EL record from the district of the enrollment where EL Status is “EL” or “Exited EL” • AND EL Identified Date <= Enrollment End Date • AND EL Exit Date > Enrollment Start Date OR is NULL. • AND student has at least one EL Service record from the district of the enrollment • AND EL Service Start Date <= Enrollment End Date AND EL Service End Date > Enrollment Start Date OR is NULL • AND Student has at least one EL Accommodation record from the district of the enrollment with Accommodation Type = ‘I: Instructional’ • AND EL Accommodation Start Date <= Enrollment End Date AND EL Accommodation End Date > Enrollment Start Date OR is NULL If Enrollment End Date is NULL, use the earliest of Last Instructional Day or Current Date Return 1 if the student is an EL. Return 0 if they are not an EL. | Student Information > Program Participation > English Learners (EL) | 1 or 0 |
| Gifted | Report if the student is Gifted. A student is considered Gifted & Talented for enrollment if one or more eligible records from the same district exist. • If the Enrollment End Date is NULL, then Gifted Start Date must be <= current date to be considered gifted. • If Enrollment End Date is NOT NULL, then Enrollment End Date must be >= Gifted Start Date to be considered gifted. • If the Grade in the reported year is <= 03, then Category must be 12. Otherwise, if Grade is > 03, Category must be something besides 12. Return 1 if the student is Gifted. Return 0 if they are not Gifted. | Student Information > Program Participation > Gifted & Talented | 1 or 0 |
| Migrant | Report if the student is a Migrant. A student is considered migrant for an enrollment if the enrollment dates overlap a migrant record that is active for the migrant education year (9/1 – 8/31). • Qualifying migrant records: - Last Qualifying Arrival - Date <= 8/31 of End Year o AND Eligibility Expiration Date >= 9/1 of End Year – 1 • Qualifying enrollments: - If Enrollment End Date >= Last Qualifying Arrival Date AND Enrollment Start Date <= Eligibility Expiration Date - If Enrollment End Date is NULL, use the earliest of Last Instructional Day or Current Date Return 1 if the student is a Migrant. Return 0 if they are not a Migrant. | Student Information > Program Participation > Migrant | 1 or 0 |
| Economically Disadvantaged | Report if the student is Economically Disadvantaged. A student is considered Economically Disadvantaged if at least one record exists where: • FRAM Start/End Dates overlap student’s Enrollment End Date - Only use records in the same district as the enrollment - If Enrollment End Date is NULL use the earliest of Last Instructional Day or Current Date • Eligibility = ‘F’ (Free), State Code 4103 or ‘R’ (Reduced), State Code 4104 • If there are multiple FRAM records that overlap the Enrollment End Date, select the record with the most recent Start Date - If there are 2+ records with same Start Date, check if any of those records have eligibility = ‘F’ (Free) or ‘R’ (Reduced) Return 1 if the student is Economically Disadvantaged. Return 0 if they are not Economically Disadvantaged. | FRAM > Eligibility | 1 or 0 |
| Homeless | Report if the student is Homeless. If any homeless record from the same district overlaps the student’s enrollment record, the student is homeless for the enrollment. Return 1 if the student is Homeless. Return 0 if they are not Homeless. | Student Information > Program Participation > Homeless | 1 or 0 |
| Foster Care | Report if the student is in Foster Care. To determine if a student is Foster Care: • Must have a Foster Care record where DistrictID is NULL (State created records only) • Enrollment must overlap with the Foster Care record - If multiple Foster Care records overlap the enrollment, then use the record with the latest (most recent) End Date or where the End Date is NULL. Return 1 if the student is in Foster Care. Return 0 if they are not in Foster Care. | Student Information > Program Participation > Foster Care | 1 or 0 |
| Military Connected | Report if a student is Military Connected. To determine if a student is Military Connected: • Has at least 1 Military Connected record from the reported district with Start Date before 7/1 of reported End Year • AND End Date after 6/30 of reported Start Year OR End Date is NULL AND • AND Status = “Active Duty, Deployed” or “Active Duty, Not Deployed” Return 1 if the student is Military Connected. Return 0 if they are not Military Connected. | Census > People > Military Connections > Military Connection Editor | 1 or 0 |
| Home Hospital Attendance Group | Report if a student is in the Home/Hospital. To determine if a student is in the Home/Hospital: • If AttendanceGroupKY has a record for the student on the latest enrollment: - With groupCategory = HH AND - End Date is NULL OR > the earliest of the Last Instructional Day or Current Date Return 1 if the student is in the Home/Hospital. Return 0 if they are not in the Home/Hospital. | Student Information > General > Attendance Group | 1 or 0. |
| Alternative Education ILPA | Report if a student has an active locked ILPA. To determine if a student has an Alternative Education Plan (ILPA): If one or more records exists in the Plan and PlanType tables for the student where the following four conditions are met then the student has an active ILPA. 1. PlanType.Module = ‘alp’ 2. Plan.Locked = 1 3. The Plan record overlaps enrollment at any time (Plan.startDate is <= enrollment.endDate and Plan.endDate >= enrollment.startDate) For the overlap determination, if enrollment.endDate is null use the earliest of the following: a) last instructional day for the calendar and schedule structure associated with the enrollment b) current date Return 1 if the student has an active locked ILPA. Return 0 if the student does not have an active locked ILPA. | Student Information > ILPA > General > Documents | 1 or 0. |
| Case Manager PersonID | Select the Case Manager Person ID from the latest (most recent) IEP with an End Date that is locked and has a date range valid at any time between the active Enrollment Start Date and Enrollment End Date. • If multiple SPED plans have the same End Date, then of those plans, select the Case Manager PersonID from the plan with the latest (most recent) Start Date. • If multiple SPED plans still exist after the above, select the plan with the highest planID to pull Case Manager PersonID from. • If Enrollment End Date is NULL, use the earliest of the Last Instructional Day (DTL.DE.11) or Current Date Once the qualifying plan is selected using the above criteria, all the following must be true for the student to be counted as SPED: • Primary Disability is NOT NULL • Special Ed Status is NOT NULL • Special Ed Setting is NOT NULL | Census > People > Demographics > Person Information > PersonID | Text |
| Case Manager First Name | Reports the preferred first name of the case manager. Report the preferred first name associated with the Case Manager PersonID. | Census > People > Demographics > Identities > First Name | Text |
| Case Manager Last Name | Reports the preferred last name of the case manager. Report the preferred last name associated with the Case Manager PersonID. | Census > People > Demographics > Identities > Last Name | Text |
Appendix A: State Classification Values
The possible values for State Classification are pulled from the locked dictionary within Campus. The current values in this locked dictionary are:
- A1: Principal or head teacher controlled school
- A2: District operated CTE Center (CTC)
- A3: District operated - special ed school
- A4: District operated-preschool program
- A5: District operated alternative program
- A6: KECSAC funded programs
- A7: Miscellaneous: Home/Hospital, Summer
- A8: District operated full-time enrolled online virtual and remote learning program
- B1: Laboratory/training school operated by college/university
- B2: Dual enrollment postsecondary/college-level course program operated by college/university
- C1: University Operated CTE Center
- C2: State Operated CTE Center (ATC)
- D1: State Dept. of Ed operated (Blind & Deaf)
- F1: Fed. Dependent School (Ft. Knox & Ft. Campbell)
- F2: Federal Job Corps
- F3: Federally funded stand-alone Head Start
- J1: Roman Catholic
- M1: Other religion
- M2: 7th Day Adventist
- R1: Private, non-public schools/programs
- C3: State operated vocational program-college
- C4: State operated-voc. health occupation school
- C5: State vocational program
Appendix B: Primary Disability Values
The possible values for Primary Disability are pulled from the locked dictionary within Campus. The current values in this locked dictionary are:
- 01: Mild Mental Disability
- 02: Functional Mental Disability
- 04: Hearing Impairment
- 05: Speech or Language Impairment
- 06: Visual Impairment
- 07: Emotional-Behavioral Disability
- 08: Orthopedic Impairment
- 09: Other Health Impairment
- 10: Specific Learning Disability
- 12: Multiple Disabilities
- 13: Autism
- 14: Traumatic Brain Injury
- 15: Developmental Delay
Appendix C : Enrollment End Status Values
The possible values for Enrollment End Status are pulled from the locked dictionary within Campus. The current values in this locked dictionary are:
- CO1: Close of Year
- G01: Graduated in less than four (4) years
- G02: Graduated in four (4) years
- G03: Graduated in five (5) years
- G04: Graduated in six (6) or more years
- W01: Transferred to another enrollment in same school
- W02: Transfer to a school within the district
- W07: Withdrawn for health reasons
- W08: Withdrawn due to death
- W12: Withdrawn under jurisdiction of court
- W17: Withdrawn, age 5 immaturity or parent choice
- W20: Transfer to home school
- W21: Transfer to a nonpublic school
- W22: Moved, re-enrolled in another Kentucky public district
- W23: Withdrawn, second drop in school year
- W24: Moved, whereabouts unknown
- W25: Dropout – above minimum age for withdrawal
- W26: Completed GED Program/GED Certificate
- W27: Withdrawal – Received GED
- W28: Reached maximum age for services, no diploma
- W29: Moved, out of state or out of United States
- W30: Mid-year withdrawal Grade 14 with prior diploma
Appendix E : KTS Exchange Logic
The logic to pull KTS Exchange is as follows. A course at the home school from the KTS Data Exchange will have:
- CourseCatalog.name = Calculated Value composed of the following:
- Last two digits of calendar.endYear minus 1
- hypen (-)
- Last two digits of calendar.endYear
- space
- KTS Course Catalog (HIGH SCHOOLS ONLY)
- Example: 22-23 KTS Course Catalog (HIGH SCHOOLS ONLY)
- CourseMaster and CourseCatalog LEFT JOIN on catalogID
- KTSTechID is defined for the corresponding course
- customCourse.value is NOT NULL for CampusAttribute.object = ‘Course’ AND CampusAttribute.element = ‘KTSTechID’
- CustomCourse LEFT JOIN to Course on coursed and to CampusAttribute on attributeID
