Tool Search: KECS LEAD Extract

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.

When the Exclude Cross-Site Data checkbox is marked, the report does not include cross-site homeroom data. Cross-site must be enabled at the district level for this checkbox to display. This checkbox defaults to marked.

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

KECS LEAD Extract Detail Report (Kentucky)