Grade and Course Completion Reports

Site-Wide Grade Report with All Items

Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports



SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',
CASE
  WHEN gi.itemtype = 'course'
   THEN c.fullname + ' Course Total'
  ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE  gi.courseid = c.id
ORDER BY lastname