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	Description	Location
Date Time Generated	Reports the date and time the extract was generated. MM/DD/YYYY HH:MM	N/A
End Year	Reports the end year for a reported school session. XXXX 4-digit number (e.g. 2022 for SY 21-22)	Calendar Information > School Year > End Year
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 Integer	Section Information > SectionID
District Number	Reports the unique number or alphanumeric code assigned to the reporting district. XXX 3-digit number	District Information > State District Number
District Name	Reports the name of the reporting district. Alphanumeric	District Information > Name
School Number	Reports the unique number or alphanumeric code assigned to the reporting school. XXX 3-digit number	School Information > Location Number
School Name	Reports the name of the reporting school. Alphanumeric	School Information > Name
State Classification	Reports the state-assigned school classification. See Appendix A for possible values. Code (e.g. A1)	School Information > State Classification
Calendar Name	Reports the name of the calendar. Alphanumeric	Calendar Information > Name
Schedule Structure Name	Reports the calendar’s schedule structure name. Alphanumeric	Calendar Information > Schedule Structure > Name
Last Instructional Day	Reports the last instructional day of the calendar and structure name. MM/DD/YYYY	Calendar Information > Days
Low Grade	Reports the lowest grade of the calendar and structure name. Reports 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. Code (e.g. 01)	Calendar Information > Grade Levels
High Grade	Reports the highest grade of the calendar and structure name. Reports 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. Code (e.g. 12)	Calendar Information > Grade Levels
Course Number	Reports the local course number. Char(13) (e.g. 0456V)	Course Information > Number
Course Name	Reports the course name. Alphanumeric	Course Information > Name
Section Number	Reports the section number for the course. Integer	Course Information > Section > Number
StateCourse Code	Reports the state’s course code. XXXXXX Char(6)	Course Information > State Code
Core Content	Reports section level content indicator. Code (e.g. 101)	Course Information > Section > Core Content
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 20: Blended Learning 21: Work-based Learning When the Teaching Method is NULL, reports 01. Code (e.g. 01)	Course Information > Teaching Method
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 21: Offsite workplace Instructional Setting is determined by the value set on the section in the Instructional Setting (Override) field. When the Instructional Setting (Override) is NULL, Instructional Setting is determined by the value for Instructional Setting on the course. When course Instructional Setting is NULL, reports 01. Code (e.g. 01)	Course Information > Section > Instructional Setting (Override) WHEN NULL: Course Information > Instructional Setting
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 When Special Type is NULL, reports 01. Code (e.g. 01)	Course Information > Section > Special Type
KTS Exchange	Indicator that the course is part of the KTS Data Exchange. Possible values are: Has Value = 1 NULL = 0 Numeric, 1 or 0	Refer to Appendix E for details on where data is pulled for this element.
Cross-Site Section	Indicates the course is a Cross-Site Enrollment course in the participating school. Possible values are: Has Value = 1 NULL = 0 Numeric, 1 or 0	Section Information > Cross-Site Enrollment
Responsive	Reports the course’s Responsive checkbox value. Possible values are: Checkbox checked = 1 Checkbox unchecked = 0 Numeric, 1 or 0	Course Information > Responsive
Type	The course type. Options include: ABV: Attendance-based Virtual PB: Performance-based V: Virtual/Online Leave blank if Null Code (e.g. PB)	Course Information > Type
Difficulty Level	The honors code associated with the course. Options include: AP: Advanced Placement CAI: Cambridge Advanced International DC: Dual Credit IB: International Baccalaureate Leave blank if Null or not AP, CAI, DC or IB Code, (e.g. AP)	Course Information > Difficulty Level
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 When 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. When multiple staff are returned by the above logic, it reports the staff whose End Date is NULL. When all have an End Date, it returns the staff with the latest (most recent) Start Date. Reports as 0 when no staff are returned. Alphanumeric	Demographics > Person Information > PersonID
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. Alphanumeric	Demographics > Identities > First Name
Primary Teacher Last Name	Reports the preferred last name of the course’s active or last primary teacher’s personID. Reports the preferred last name associated with the Primary Teacher PersonID. Alphanumeric	Demographics > Identities > Last Name
Section Start Date	Reports the section’s start date. Use sectionPlacement to determine the starting term for the section and report that term.startdate. MM/DD/YYYY	Course Information > Section
Section End Date	Reports the section’s end date. Use sectionPlacement to determine the ending term for the section and report that term.enddate. MM/DD/YYYY	Course Information > Section
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 When 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.	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.	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.	Demographics > Identities > Identity Information > Last Name
Gender	Report the student’s Gender. Options are: Male = M Female = F	Identities > Identity Information > Gender
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 Code (e.g. 1)	Identities > Identity Information > Race/Ethnicity
Roster Start Date	Reports the section’s start date based on the roster start date. When Roster.StartDate is NULL, reports BLANK. MM/DD/YYYY	Course Information > Section > Roster
Roster End Date	Reports the section’s end date based on the roster end date. When Roster.EndDate is NULL, reports BLANK. MM/DD/YYYY	Course Information > Section > Roster
Primary Disability in Section	Report the primary disability for a student on the last day of a section. See Appendix B for possible values. Selects the IEP from the latest (most recent) End Date that is locked and has a date range that is valid at whichever is earlier, run time or section end date. When multiple SPED plans have the same End Date, then of those plans, selects the plan with the latest (most recent) Start Date. When multiple SPED plans still exist after the above, selects the plan with the highest planID. When Enrollment End Date is NULL, uses 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. Code (e.g. 01)	Special Ed Documents > KY IEP > Enrollment Status > Primary Disability
EnrollmentID	Reports the last enrollment in the calendar’s schedule structure for the student on the roster. Reports the latest (most recent) active enrollment. Alphanumeric	Enrollments > Enrollment Editor > Enrollment ID
Grade	Report the grade level of the student. Possible values are all grade levels 95 through 20. Code (e.g. 01)	Enrollments > Enrollment Editor > Grade
Service Type	Reports the service type of the student’s enrollment. Possible values are: P: Primary S: Partial N: Special Ed Services Code (e.g. P)	Enrollments > Enrollment Editor > Service Type
Enrollment Start Date	Reports the start date of the student’s last enrollment. MM/DD/YYYY	Enrollments > Enrollment Editor > Start Date
Enrollment End Date	Reports the end date of the student’s last enrollment. When the Enrollment End Date is NULL, reports blank. MM/DD/YYYY	Enrollments > Enrollment Editor > End Date
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 Code (e.g. E01)	Enrollments > Enrollment Editor > State Start Status
Enrollment End Status	Reports the end status of the student’s last enrollment. See Appendix C for possible values. When the Enrollment End Status is NULL, reports blank. Code (e.g. W22)	Enrollments > Enrollment Editor > State End Status
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 valid at an earlier run time or the last instructional day. When multiple SPED plans have the same End Date, then of those plans, selects the plan with the latest (most recent) Start Date. When multiple SPED plans still exist after the above, selects the plan with the highest planID. When the 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	Special Ed Documents > KY IEP > Learner Characteristics Inventory
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 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 the 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. When the Enrollment End Date is NULL, uses the earliest of Last Instructional Day or the Current Date. Reports as 1 when the student is an EL student. Reports as 0 when they are not an EL student.	Student Information > Program Participation > English Learners (EL)
Gifted	Indicates the student is Gifted. A student is considered Gifted & Talented for enrollment when one or more eligible records from the same district exist. When the Enrollment End Date is NULL, the Gifted Start Date must be <= current date to be considered gifted. When the Enrollment End Date is NOT NULL, the Enrollment End Date must be >= Gifted Start Date to be considered gifted. When the Grade in the reported year is <= 03, the Category must be 12. Otherwise, when the Grade is > 03, the Category must be something besides 12. Reports as 1 when the student is Gifted. Reports as 0 when they are not Gifted.	Program Participation > Gifted & Talented
Migrant	Indicates the student is a Migrant. A student is considered migrant for an enrollment when 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 AND Eligibility Expiration Date >= 9/1 of End Year – 1 Qualifying enrollments: When the Enrollment End Date >= Last Qualifying Arrival Date AND Enrollment Start Date <= Eligibility Expiration Date When the Enrollment End Date is NULL, use the earliest of Last Instructional Day or Current Date Reports as 1 when the student is a Migrant. Reports as 0 when they are not a Migrant.	Program Participation > Migrant
Economically Disadvantaged	Indicates the student is Economically Disadvantaged. A student is considered Economically Disadvantaged when at least one record exists where: FRAM Start/End Dates overlap with the student’s Enrollment End Date. Only uses records in the same district as the enrollment When the Enrollment End Date is NULL, uses the earliest of Last Instructional Day or Current Date Eligibility = ‘F’ (Free), State Code 4103 or ‘R’ (Reduced), State Code 4104 When multiple FRAM records overlap the Enrollment End Date, select the record with the most recent Start Date When there are 2+ records with same Start Date, check if any of those records have eligibility = ‘F’ (Free) or ‘R’ (Reduced) Reports as 1 when the student is Economically Disadvantaged. Reports as 0 when they are not Economically Disadvantaged.	FRAM > Eligibility
Homeless	Indicates the student is Homeless. When any homeless record from the same district overlaps the student’s enrollment record, the student is homeless for the enrollment. Reports as 1 when the student is Homeless. Reports as 0 when they are not Homeless.	Program Participation > Homeless
Foster Care	Indicates the student is in Foster Care. To determine when 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. When 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. Reports as 1 when the student is in Foster Care. Reports as 0 when they are not in Foster Care.	Program Participation > Foster Care
Military Connected	Indicates the student is Military Connected. To determine when a student is Military Connected: Has at least 1 Military Connected record from the reported district with Start Date before 7/1 of the reported End Year, AND End Date after 6/30 of reported Start Year OR End Date is NULL AND Status = “Active Duty, Deployed” or “Active Duty, Not Deployed” Reports as 1 when the student is Military Connected. Reports as 0 when they are not Military Connected.	Military Connections > Military Connection Editor
Home Hospital Attendance Group	Indicate the student is in Home/Hospital. To determine when a student is in Home/Hospital: When 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. Reports as 1 when the student is in Home/Hospital. Reports as 0 when they are not in the Home/Hospital.	Attendance Group
Alternative Education ILPA	Indicates the student has an active locked ILPA. To determine when a student has an Alternative Education Plan (ILPA): When one or more records exist in the Plan and PlanType tables for the student where the following four conditions are met, the student has an active ILPA. PlanType.Module = ‘alp’ Plan.Locked = 1 The Plan record overlaps enrollment at any time (Plan.startDate is <= enrollment.endDate and Plan.endDate >= enrollment.startDate). For the overlap determination, when enrollment.endDate is null, uses the earliest of the following: a) last instructional day for the calendar and schedule structure associated with the enrollment b) current date Reports as 1 when the student has an active locked ILPA. Reports as 0 when the student does not have an active locked ILPA.	ILPA Documents
Case Manager PersonID	When the student is actively enrolled and has an IEP Primary Disability Enrollment, reports the student's Case Manager's Person ID. When the Enrollment End Date is not NULL, OR the Enrollment is within a CTE School where the State Classification is A2 or C2, reports as NULL. For actively enrolled students who have a value for IEP Primary Disability Enrollment: A student with multiple active case managers reports one row per case manager per sectionID. The student has an active case manager when: TeamMember PersonID = Enrollment PersonID TeamMember Module = SpecialEd TeamMember Role = Case Manager TeamMember End Date is NULL OR >= Today TeamMember.StaffPersonID is NOT NULL When no active case manager exists, and the student’s enrollment end date is NULL, reports as 0. and the student’s enrollment end date is NOT NULL, reports as NULL. When a student does not have an IEP Primary Disability Enrollment, return NULL. Alphanumeric	Demographics > Person Information > PersonID
Case Manager First Name	The preferred first name of the case manager. Reports the preferred first name associated with the Case Manager PersonID. Alphanumeric	Demographics > Identities > First Name
Case Manager Last Name	Reports the preferred last name of the case manager. Reports the preferred last name associated with the Case Manager PersonID. Alphanumeric	Demographics > Identities > Last Name

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)