Introduction and Background
In this primer on reports, you will understand how to use data collected in the ODS for state reporting purpose. Sections 1 and 2 provide context and details on the Membership and Special Education use cases that are the focus of this kit. These two sections are followed by a step-by-step guide to generate reports for the focus use cases on your Quick Start Virtual Machine.
1. Membership / Student Count Use Case
| Rule | Membership Use Case for K–12 |
|---|---|
| 1 | Every student who is enrolled in a school district at least half time (FTE > 50%) as of October first will be counted at the school, rolled up to a district, and to the state level. No duplication at school, district, and state levels. Student should be counted only once per school/per district/per state. |
| 2 | Scenario 1 - Student is enrolled in two schools (School A & School B) in the same district.
2. If the student is 50% in School A and 50% in School B, then assign the student to School A. They assign the student to School Id = min (SchoolId), meaning the older school gets the student. The district has the ability to control the FTE by assigning one school to be 51% and the other to be 49%. |
| 3 | Scenario 2 - Student is enrolled in two schools but in two different districts.
2. If one district has more than 50% FTE, that district counts the student. |
| Rule | Membership Use Case for PreK |
|---|---|
| 1 | FTE does not play a role here. FTE can be of any value as the logic does not take FTE into consideration. |
| 2 | Scenario 1 - Student is enrolled in a school Student is counted if the student is enrolled on or before Oct 1st and the exit date is null or after Oct 1st. |
| 3 | Scenario 2 - Student is enrolled in multiple schools in the same district. Student will be counted twice. Duplication is permitted. Since FTE is not taken into consideration, the student can be in two different schools. |
| 4 | Scenario 4 - Student is enrolled in two schools in two different districts. Student will be counted twice. Duplication is permitted. Since FTE is not taken into consideration, the student can be counted by two different districts. |
| 5 | Duplication is allowed at the school level/district level and at the state level counts. |
| 6 | No mandatory attendance for these students. |
Below are the reports generated for this use case
- Membership By Grade, Race, and Gender (MembershipCountByGradeRaceGenderReport.xlsx)
- County Membership by Grade (MembershipCountByGradeReport.xlsx)
2. Special Education Student Count Use Case
| Data Definitions & Explanations |
|---|
Special Education Special Education services are specially designed instruction provided at no cost to the parent to meet the unique needs of a child with a verified disability. School districts must ensure that all children with verified disabilities, from birth to age 21, have available to them a free, appropriate public education which includes special education and related services to meet their unique needs. The Special Education report displays the percentage of students, ages 5-21 as of October 1st, who are receiving special education services divided by the number of all students in grades K–12 as reported in Fall membership. Part B School Age - The report titled Special Education - Part B School Age includes students with a verified disability, ages 6 to 21. These students are reported under 13 primary disability categories and across 8 setting categories.
Infants and toddlers with disabilities are reported under 13 by primary disability categories and across 3 setting categories. |
Logic for calculating the Special Education Student Count:
- The count is taken by counting the number of students in StudentSpecialEducationProgramAssociation.
- Then, those students are checked against StudentSchoolAssociation for an enrollment record. If one exists, check to see if the enrollment is on or before Oct 1st and if the exit date is null or the exit date is after Oct 1st.
- If the student record meets the above checks, then the FTE percentage in that school is checked to see if it is over 50%. If it is, then student is counted in the Special Education Student count.(Need confirmation - the FTE should be > 50%; not sure if the FTE is taken into consideration if the student has only one enrollment)
| Rule | Membership Use Case for K–12 |
|---|---|
| 1 | Special Education Student should be receiving special education services or should be in SPED in ONLY ONE district. Business validations will throw an error if a student is receiving special education services from two districts. |
| 2 | This kit captures StudentSpecialProgramAssociation information at the district level. Your state could be receiving at the district level or school level. |
| 3 | Scenario 1 - Student is enrolled in two schools in the same district. Use the same logic stated above except check to see which school has the higher FTE percentage. The student should be counted in that school. No duplication at the school level or at the district level. |
| 4 | Scenario 2 - Student is enrolled in two schools in two different districts. One district reports the SPED, hence the student should be reported by only one district. No duplication at the school level or at the district level. The student cannot be an active SPED student in two different districts. |
Below are the reports generated for this use case
- Special Education Report By Age (SpecialEducationByAgeReport.xlsx)
- Special Education Report By Disability (SpecialEducationPrimaryDisabilityReport.xlsx)
- Special Education Report By Special Education Setting (SpecialEducationSettingReport.xlsx)
Report Generation Step-by-Step Guide
Populate Database Tables
The reports are generated using the student data available in the EdFi_Ods_2022 database. The stored procedures shown below are used to insert data into report tables in the database.
- reporting.LoadStudentFallMembership - inserts data into reporting.StudentFallMembership table.
- reporting.LoadSpedChildCount - inserts data into reporting.SpecialEducationChildCount table.
You can find the database tables and stored procedures by following the below steps:
Step 1. Open Microsoft SQL Server Management Studio as Administrator using Windows search box with "ssms" keyword.
Step 2. Using Windows Authentication, click Connect.
Step 3. After connection, you will see EdFi_Admin, EdFi_Security and EdFi_Ods_2022 database for the 2022 school year in the Object explorer.
Step 4. Expand the EdFi_Ods_2022 database and then Tables to find the reporting tables under reporting schema.
Step 5. Run the stored procedures, also found under reporting schema, to populate the reporting tables.
Generate Reports
Now that the reporting tables have been populated using the above stored procedures you can use the provided SQL scripts to generate each report. These SQL scripts can be found under C:\Ed-Fi-Starter-Kit\ReportGenerationScripts folder. Look for your reports in the Reports folder on the desktop.
Now that you've generated some reports, you will need to know how to refresh them when there is an update in the student data. Updated reports can be generated by running the PowerShell script report.ps1 located in the same folder. The script performs all the tasks required to generate these reports, from executing the stored procedures to inserting data into the reporting tables as well as executing all the SQL scripts found in C:\Ed-Fi-Starter-Kit\ReportGenerationScripts folder. Additionally, it updates the XLSX files inside Reports folder on the desktop. Follow the steps below to run the PowerShell script.
Step 1. Open Windows PowerShell as Administrator using Windows search box with "powershell" keyword.
Step 2. Run the command below in PowerShell to navigate to the ReportGenerationScripts folder
cd C:\Ed-Fi-Starter-Kit\ReportGenerationScripts
Step 3. Run the following command in PowerShell to generate the updated reports
.\report.ps1
Step 4. The updated excel report files will be present under Reports folder on the desktop.
Reminder
These reports are generated out of the sample data included in this Starter Kit which includes limited number of student records for one school year. Some of these reports may show fields for multiple years but will only be populated with information for one school year.