Need Help Creating Admin Course Report (Moodle 3.9)

Need Help Creating Admin Course Report (Moodle 3.9)

by Marvin Castro -
Number of replies: 3

We just completed the semester and I needed a faster way to generate a report of courses with enrolled students and their course total (final grade). We have more than a hundred courses in different categories so it'd take a long time to go to individual course and print a report. I have installed Configurable Reports Block plugin and looked into Ad-Hoc Database queries but I can't find the right SQL query for this particular report. I just needed a report that looks like this:

Course   Student Name    Course Total

It'd be better if I could filter by course and/or course category.

By the way, our school just installed Moodle last August due to lockdown and this is just our first semester. As a teacher and administrator, I find it overwhelming to explore Moodle functionalities.  

Average of ratings: -
In reply to Marvin Castro

Re: Need Help Creating Admin Course Report (Moodle 3.9)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Everyone's situation is a little different. Here is the query that I run for this purpose.  You might need to make adjustments in the "where" grouping.  Also, you might find something that I skipped.  Let me know.

SELECT c.shortname AS CourseName,
       u.lastname AS LastName,
       u.firstname AS FirstName,
       u.email AS Email,
       gg.finalgrade,
       c.idnumber AS CourseIDNum,
       c.id AS CourseNumber,
       ue.userid AS StudentID
FROM mdl_course c
JOIN mdl_enrol e ON e.courseid = c.id
JOIN mdl_user_enrolments ue ON ue.enrolid = e.id
JOIN mdl_context con ON con.instanceid = c.id
JOIN mdl_role_assignments ra ON (ra.contextid = con.id
                                 AND ra.userid = ue.userid)
JOIN mdl_user u ON u.id = ue.userid
JOIN mdl_grade_items gi ON gi.courseid = c.id
JOIN mdl_grade_grades gg ON (gg.itemid = gi.id
                             AND gg.userid = ue.userid)
WHERE e.status = 0
  AND ue.status = 0
  AND ra.roleid = 5
  AND gi.itemtype = "course"
  AND ue.userid > 0
ORDER BY c.idnumber DESC,
         u.lastname,
         u.firstname
Average of ratings: Useful (2)
In reply to Rick Jerz

Re: Need Help Creating Admin Course Report (Moodle 3.9)

by Marvin Castro -
Thank you Rick. At first the code gave me some error messages, but after some minor adjustments and changing the mdl_ to prefix_ before the table name, I was able to generate the report I need.
Average of ratings: -
In reply to Marvin Castro

Re: Need Help Creating Admin Course Report (Moodle 3.9)

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
You did well, Marvin. Yes, that mdl versus prefix depends upon how you set up your tables, or if you are using Configurable Reports. You can also send the results to csv or Excel, then do anything that you wish.

Also, do some manual double-checking of your results from the query versus what you see in a few courses.
Average of ratings: Useful (1)