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.

Use Cases

Use Case 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 1 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.

Look for the higher FTE between the two schools. Student is counted in that school.
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 school with the lower ID counts the student. The district has the ability to control the FTE assignment by setting one school's enrollment level to be 51% and the other to be 49%.

3

Scenario 2 - Student is enrolled in two schools but in two different districts.

If both the districts report the student as 50% FTE, neither district can count the student.
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)

Use Case 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	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.
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. The Starter Kit includes stored procedures that codify the membership and special education count rules described above. When run, the stored procedures load data into the report tables in the ODS database. These procedures are provided as examples. The business rules for your actual implementation may vary.

Worth noting is that 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 steps below:

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_2023 database for the 2023 school year in the Object explorer.

Step 4. Expand the EdFi_Ods_2023 → 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_2023 → 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

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 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.

Starter Kits

View Reports - Quick Start