Introduction and Background
In this primer on reports, you will familiarize yourself with data collected in the ODS for state reporting purposes. These reports are pre-regenerated and provided in Quick Start Virtual Machine for viewing.
Sections 1 and 2 provide context and details on the Membership and Special Education use cases that are the focus of this Starter Kit. These sections are followed by details on sample reporting procedures included in your virtual machine as well as a step-by-step guide to refresh and regenerate reports.
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 1st 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 only be counted once for each school, district, and 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 Pre-K |
|---|---|
| 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 or 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
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.
| Rule | Special Education Student Count Use Case for K–12 |
|---|---|
| 1 |
|
| 2 | 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. |
| 3 | This Starter Kit captures StudentSpecialEducationProgramAssociation information at the district level. Your state could be receiving at the district level or school level. |
| 4 | 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. |
| 5 | 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)
Reporting Database Artifacts
The reports are generated using the sample student data included in the Starter Kit virtual machine. Starter Kit includes stored procedures that codify the membership and special education count rules described above and when run, load data into the report tables in the ODS database. These procedures are provided as examples and your actual implementation may vary.
Note - There is no correlation between the reports in terms of total number of student in each report.
You can find these 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 → Tables to view the reporting tables data in these tables are used to generate the Excel reports you saw earlier.
Step 5. Expand the EdFi_Ods_2022 → Programmability → Stored Procedures to view the stored procedures in reporting schema
- reporting.LoadStudentFallMembership - Inserts data into reporting.StudentFallMembership table.
- reporting.LoadSpedChildCount - Inserts data into reporting.SpecialEducationChildCount table.
Report Generation Step-by-Step Guide
Now that you've seen both the reporting-related database artifacts and the pre-generated reports, you will need to know how to refresh the reports when there is an update in the student data. Your virtual machine has additional SQL scripts in C:\Ed-Fi-Starter-Kit\ReportGenerationScripts folder that generate Excel reports using the data from reporting tables.
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 reporting stored procedures to inserting data into the reporting tables as well as executing all the Excel generating 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
| Code Block | ||
|---|---|---|
| ||
cd C:\Ed-Fi-Starter-Kit\ReportGenerationScripts |
Step 3. Run the following command in PowerShell to generate the updated reports
| Code Block | ||
|---|---|---|
| ||
.\report.ps1 |
Step 4. The updated Excel report files will be present under Reports folder on the desktop.
| Panel | ||||
|---|---|---|---|---|
| ||||
Reminder These reports are generated from 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. |
