Attendly Vault

SAUSD ASP BSP Reporting Standardization

2 min read

2025-12-11: SAUSD ASP/BSP Reporting Standardization

For: SAUSD
Impact: Standardized three critical ASP/BSP reports to properly exclude Engage 360 programs and ensure waitlist accuracy, enabling district to track actual program demand and capacity across all 40+ elementary/middle school sites.

Context: SAUSD runs two types of after-school programs: regular ASP/BSP programs and comprehensive Engage 360 programs. Reports were showing inflated numbers because they included Engage 360 programs, and waitlist reports incorrectly showed students waiting for past/canceled programs or programs they were already enrolled in.

Implementation:

  • Program name matching: Expanded queries to match both abbreviated forms (ASP/BSP) and full names (After School/Before School Program) to catch all program naming variations
  • Engage 360 exclusion: Simplified exclusion pattern from multiple specific patterns to single NOT LIKE '%Engage 360%' to catch all variations regardless of year prefix (e.g., “2025-2026 ASP Engage 360 Full Program”)
  • Waitlist filtering: Added three-layer filter to ensure only valid waitlist entries:
    1. Status check using session-based logic (Current/Future programs only, excluding Past/Canceled)
    2. Enrollment exclusion using NOT EXISTS to remove students already enrolled in same program
    3. Engage 360 program exclusion
  • Grade-level bucketing: Maintained existing TK-K, 1st, 2nd-5th, and 6th-8th grade groupings using metadata→>‘grade’ field

Queries Updated:

  1. Enrollment Report - Current enrolled students by grade and program type
  2. Waitlist Report - Students waiting for current/future programs (excluding enrolled students)
  3. ADA Report - Average daily attendance calculations by grade band

Technical Details:

  • Program status logic uses session timestamps: programs with sessions ending before today AND starting today/later = Current; programs with no sessions starting before today = Future
  • Enrollment state check uses state = 'complete' from enrollment table
  • Grade field variations handled: metadata->>'grade' for numeric grades (-1, 0, 1-8) and metadata->>'displayGrade' for display values (TK, K, 1st-8th)

Related: sausd reporting asp bsp waitlist

Tags: reporting sausd asp-bsp enrollment waitlist

Graph View