reports.yml.example

--- 
unique_class_requests:
  type: "stat"
  title: "Unique Class Requests"
  description: "Number of Unique Class Requests"
  help: Count of all class requests
  data: 
    number_of_classes: "Number of Class Requests"
  returns: single_row
  select: "SELECT COUNT(*) AS number_of_classes FROM courses c"

one_group_one_session: 
  type: "stat"
  title: "Single-session, Single-section Classes"
  description: "Number of Single-session, Single-section Classes"
  help: Subset of class requests which meet only once and in a single group
  data: 
    number_of_classes: "Number of Classes"
  returns: single_row
  select: "SELECT COUNT(*) AS number_of_classes FROM courses c"
  where: "session_count=1 AND section_count=1"
    
multiple_sessions_one_group:
  type: "stat"
  title: "Multiple-session Classes for One Group"
  description: "Number of Multi-session Classes"
  help: Subset of class requests which meet in multiple sections to consult the same materials, typically large classes
  data: 
    max_number_of_sessions: "Max Sessions"
    average_sessions: "Average Number of Sessions"
    std_dev: "std. dev."
    number_of_classes: "Number of Classes"
  returns: single_row
  select: "SELECT COUNT(*) AS number_of_classes, AVG(session_count) AS average_sessions, STDDEV(session_count) AS std_dev, MAX(session_count) AS max_number_of_sessions FROM courses c"
  where: "session_count>1 AND section_count=1"
    
multiple_groups_one_session:
  type: "stat"
  title: "Single Sessions, Multiple Sections"
  description: "Single Sessions, Multiple Sections"
  help: Subset of class requests which meet multiple times throughout a semester, typically to consult with different materials
  data: 
    max_number_of_sections: "Max Sections"
    average_sections: "Average Number of Sections"
    std_dev: "std. dev."
    number_of_classes: "Number of Classes"
  returns: single_row
  select: "SELECT COUNT(*) AS number_of_classes, AVG(section_count) AS average_sections, STDDEV(section_count) AS std_dev, MAX(section_count) AS max_number_of_sections FROM courses c"
  where: "session_count=1 AND section_count>1"

multiple_groups_multiple_sessions: 
  type: "graph"
  title: "Multiple Sessions, Multiple Sections"
  description: "Multiple Sessions, Multiple Sections"
  help: Subset of class requests which meet multiple times throughout a semester in multiple sections
  data_model:
    series: 
      -
        name: "All Courses"
        data: 
          x: "section_count"
          y: "session_count"
          z: "number"
  highchart_options:
    chart: 
      type: bubble
    title: 
      text: "Frequency of Class Sections and Sessions"
    xAxis: 
      title: 
        text: Sessions
      allowDecimals: false
    yAxis: 
      title: 
        text: Sections
      allowDecimals: false
  returns: multiple_rows
  select:   "SELECT section_count, session_count, COUNT(*) AS number FROM courses c"
  where:    "session_count>1 AND section_count>1"
  group_by: "GROUP BY section_count, session_count"

attendance: 
  type: "stat"
  title: Class Attendance
  description: "Class Attendance"
  help: Total headcounts
  data: 
    average_actual: "Average Actual Attendance"
    average_projected: "Average Projected Attendance"
    total_number_of_attendees: "Total Actual Attendance"
    number_of_classes: "Classes Counted"
  returns: single_row
  select: "SELECT COUNT(*) AS number_of_classes, AVG(number_of_students) AS average_projected, AVG(total_attendance) AS average_actual, SUM(total_attendance) AS total_number_of_attendees FROM courses c"

patron_distribution:
  type: "graph"
  title: Patron Data
  description: Patron Data
  help: Top requestors
  data_model:
    categories: "name"
    series:
      -
        name: "Number of Courses"
        data: 
          y: "course_count"
  highchart_options: 
    chart: 
      type: bar
    title: 
      text: "Patron Course Requests"
    xAxis:       
      title: 
        text: "Requesting Patron"
    yAxis: 
      title:
        text: "Classes Requested"
        align: 'high'
      allowDecimals: false
  returns: multiple_rows
  select:   "SELECT CONCAT(contact_first_name,' ',contact_last_name) AS name, COUNT(*) AS course_count FROM courses c"
  group_by: "GROUP BY name"
  order_by: "ORDER BY COUNT(*) DESC"
  limit:    "LIMIT 15"

staff_distribution:
  type: "graph"
  title: "Staff Data"
  description: Staff Data
  help: Top teachers assigned as primary and/or secondary staff  to class
  data_model:
    categories: "name"
    series:
      -
        name: "Primary Contact"
        data: 
            y: "as_primary"
      -
        name: "Assigned Staff"
        data: 
            y: "as_staff"
        
  highchart_options: 
    chart: 
      type: bar
    title: 
      text: "Course Staffing"
    xAxis:       
      title: 
        text: "Staff Member"
    yAxis: 
      title:
        text: "Classes Assigned"
        align: 'high'
      allowDecimals: false
    plotOptions:
      series:
        stacking: "normal"
  returns: multiple_rows
  select:   >
    SELECT CONCAT(u.first_name,' ',u.last_name) AS name, 
    (SELECT COUNT(u1.id) FROM users u1 JOIN courses c ON u1.id=c.primary_contact_id !WHERE_FILTER! GROUP BY (u1.id) HAVING u1.id=u.id) AS as_primary,
    (SELECT COUNT(u2.id) FROM users u2 JOIN courses_users cu ON u2.id=cu.user_id JOIN courses c ON cu.course_id=c.id !WHERE_FILTER! GROUP BY (u2.id) HAVING u2.id=u.id) AS as_staff
    FROM users AS u
  where:    "none"
  group_by: "GROUP BY u.id"