Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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. 

Image Added

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

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

...

  1.  Look for the

...

  1. higher FTE between the two schools. Student is counted in that

...

  1. school.

...

  1.  If the student is 50% in School A and 50% in School B, then

...

  1. assign the student to

...

  1. School A.

...

  1. They assign the student to School

...

  1. ID = min(SchoolId), meaning

...

  1. the school with the lower ID counts the student.

...

  1. The district has the

...

  1. ability to control the FTE

...

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

...

  1. If both the districts report the student as 50% FTE, neither district can count the student.

...

  1. If one district has more than 50%

...

  1. FTE, that district counts the student.


RuleMembership Use Case for

...

Pre-K 

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.

5Duplication is allowed at the school

...

or district level and at the state level counts.
6No mandatory attendance for these students

...

  1.  User runs the Ed-Fi ODS/API application and authorizes

2. StudentSchoolAssociation Posts  (the names are fictitious)- 

John Smith, Kelly Logan, Jennifer Hart getting enrolled in a school within a district.  Choose different schools and its districts.  John is going to be enrolled in one school only.  Kelly will be enrolled in two schools with different FTEs.  Jennifer will be enrolled in one school but entry date will be beyond Oct 1st.   

a.  Do a GET on schools to show that the school exists

b.  Do a POST for John Smith.  Make sure the Entry Date is within Oct 1st of the current year.  Make sure his FTE is 1.0

...

.

...

c.  Do a POST for Kelly Logan.  Make sure the entry date is within OCT 1st of the current year.  Make sure her FTE is 0.49 

d.  Do a POST for Jennifer Hart.  Make sure the entry date is beyond Oct 1st of the current year.  Make sure her FTE is 1.0


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

...

Logic for calculating the Special Education Student Count - 

...

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

Image Modified

Step 2. Using Windows Authentication, click Connect.

Image Modified

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.

...

Image Added

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.

Image Added

Step 5.

...

Expand the EdFi_Ods_2023 → Programmability → Stored Procedures to view the stored procedures

...

Image Removed

Generate Reports

...

in reporting schema

  • reporting.LoadStudentFallMembership - Inserts data into reporting.StudentFallMembership table.
  • reporting.LoadSpedChildCount - Inserts data into reporting.SpecialEducationChildCount table.

Image Added

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.

Image Removed

...

Image Added

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.

Image Modified

Step 2. Run the command below in PowerShell to navigate to the ReportGenerationScripts folder

Code Block
languagepowershell
cd C:\Ed-Fi-Starter-Kit\ReportGenerationScripts

Image Modified

Step 3. Run the following command in PowerShell to generate the updated reports

Code Block
languagepowershell
.\report.ps1

Image Modified

Step 4. The updated

...

Excel report files will be present under Reports folder

...

on the desktop.

...

Image Added


Panel
titleBGColor#ffffff
borderStylenone

Image Added

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.