Creating Reports from a Database Example - Title II Assessment Data Report

Much of the impetus for structuring the assessment data is the need to complete reports such as Title II.  In the past we have depended on the vendor to compile the data and programs to verify the results.  This system has two main problems:
  1. Neither the vendor nor the programs are ultimately responsible for the reported data. (The current system requires the vendor to depend on PESB to approve changes submitted from programs.)
  2. Incentives for keeping good records are not set correctly.
    • The vendor is not incentivized to retain corrections from year to year (programs can just make the changes again).
    • Programs with efficient data systems have the capacity to note smaller errors and will likely feel obligated to correct the errors (more work).
    • Programs with less efficient data systems are less likely to double-check the vendor's data as long as the report is positive (less work).

For the 2012 Title II assessment data to be submitted to PESB:


The universe is the highest score for each test type attempted by an individual who:
  • Is currently enrolled 
  • Completed in 2011
  • Completed in 2010
  • Completed in 2009
Note - Setting the universe is the more challenging area of this report.  There are some detailed technical questions that need to be answered, such as, "Do we include records for people who are enrolled but are no longer pursuing that endorsement?" 


The computations for the Title II report are relatively simple.
  • Information about the scores
    • Lowest Score (MIN)
    • Highest Score (MAX)
    • Average Score (AVG)
  • Information about the number of individuals
    • Number of individuals that took the assessment (COUNT number of takers)
    • Number of individuals that passed the assessment (SUM number of passers)
    • Percent of people who passed the assessment  (SUM/COUNT)
Note - Computations can also be thought of as aggregated variables. 

Grouping (GROUP BY)

Computations require groupings (must define what groups need to be averaged or added)
  • Each vendor
  • Each subject
Note - Groupings can also be thought of as break variables in aggregations.


The Title II report is actually six individual queries that are stacked on one table, these are defined by "GROUP"  
  1. Currently enrolled and completed clinical coursework.
  2. Currently enrolled and have not completed clinical coursework.
  3. Completed in 2011
  4. Completed in 2010
  5. Completed in 2009
  6. Completed 2009 - 2011
Note - It is possible to think of these queries as just additional break variables, but because an individual is counted in more than one group it might be easier to build these as individual queries.