I am still confused.
Are you using the Moodle grade book? What are you cross-referencing?
If "Course Total" is the Moodle grade book, and you are looking for it being blank, when you export the grade book to Excel, might you just sort this by Grade Total?
Or are you looking at final grade across many courses?
I see what you're saying about exporting the the grade book and sorting it by Course Total (with what is blank). That's certainly a solution.
I was just hoping there would be a quicker, more efficient way to show such a report. I have been attempting to use the Configurable Reports plugin, and trying to generate a SQL report. So far it has been very complicated and difficult.
The query below is the one that I use to show me all students and their final grades. I actually have this in my Configurable Reports. I will give you the CR (prefix_) form of this query. You might need to add one more WHERE clause to filter on gg.finalgrade. Notice that I am also filtering on final grade IS NOT NULL. Maybe you would want "IS NULL" to find students who do not yet have a final grade. In my case, if the student had received a grade on any item in the grade book, they will have a final grade. If you are using Activity Completion, you would need to add something in the query to filter on it.
Well, this is a start. I began this by looking at some of the contributed queries first, and then modified it. I cannot guarantee that it is 100% accurate, but it works well for my needs.
SELECT DISTINCT c.shortname AS CourseName,
u.firstname AS FirstName,
u.lastname AS LastName,
u.email AS Email,
gg.finalgrade AS FinalGrade,
c.idnumber AS CourseIDNum,
gi.courseid AS CourseNumber,
gg.userid AS StudentID,
3 AS moodleversion
FROM prefix_grade_items gi
JOIN prefix_grade_grades gg ON gi.id = gg.itemid
JOIN prefix_course c ON c.id = gi.courseid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_context ct ON ct.instanceid = gi.courseid
JOIN prefix_role_assignments ra ON ra.userid = gg.userid
JOIN prefix_user_enrolments ue ON u.id = ue.userid
JOIN prefix_enrol e ON e.courseid = gi.courseid
WHERE gi.itemtype = "course"
AND gg.finalgrade IS NOT NULL
AND u.suspended = 0
AND u.deleted = 0
AND ct.contextlevel = 50
AND ra.roleid = 5
AND ct.id = ra.contextid
AND ue.status = 0
AND e.status = 0
AND e.id = ue.enrolid
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
Hey Rick -
This is really helpful. I really appreciate it. With my limited knowledge of SQL, this gives me something to begin to build off of.
Quick question - is there a way to make this report specific to one course? I tried typing in a line reading:
AND c.shortname like "%Refresher%"
The above line gives me no results displayed on the report. I do know that there are some grades in the grade book that are null though.
Again though, thank you for the help.
Yes, you are correct for "the course."
You should be able to verify the SQL query by going into the Moodle course and seeing for yourself it the SQL query seems to be correct.
A quick test would be:
select *
from mdl_course
where shortname = "the courses short name"
If you get nothing, your course's shortname must be wrong or the course doesn't exist.
I am not sure where you are experimenting. I tend to do this using phpMyAdmin. In this tool, I can try as many queries as I like. Then, when my query is working, I put it into Configurable Reports.
Good Morning Rick -
I've been attempting to work through it further. So far I have:
SELECT c.fullname AS CourseName, u.firstname AS FirstName, u.lastname AS LastName, u.email AS Email, gg.finalgrade AS FinalGrade
FROM prefix_grade_items gi
JOIN prefix_grade_grades gg ON gi.id = gg.itemid
JOIN prefix_course c ON c.id = gi.courseid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_context ct ON ct.instanceid = gi.courseid
JOIN prefix_role_assignments ra ON ra.userid = gg.userid
JOIN prefix_user_enrolments ue ON u.id = ue.userid
JOIN prefix_enrol e ON e.courseid = gi.courseid
WHERE gi.itemtype = "course"
AND gg.finalgrade IS NULL
AND c.fullname like "%Refresh%"
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
Something to note - I changed c.shortname to c.fullname when referring to a course. With the line "AND c.fullname like "%Refresh%"" I get No Records Found when running the report. Clearly I have something wrong with my syntax here.
With the SELECT * mdl_course test you mentioned, I was able to pull the Refresher course via the fullname.
Do you see any red flags in my above report?
Just want to follow up with where I am at so far. It seems like in my case the course final grade is not the measurement I need for this particular course. I need to explore how to pull Activity Completion and/or Course Total as being NULL.
From what I can tell the closes thing I can get to Activity Completion in the database is the timecompleted column in the mdl_course_completions table.
As of now I cannot find where Course Total is stored in the Moodle database.
The current query I have is:
SELECT Distinct c.fullname, u.firstname, u.lastname, u.email, gc.timecompleted
FROM prefix_course as c, prefix_user as u, prefix_grade_items gi, prefix_course_completions gc
WHERE u.id = gc.userid
AND gi.itemtype = "course"
AND gc.timecompleted IS NULL
AND c.fullname LIKE "%Refresher%"
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
The issue here is that some of the names in the report have in fact completed the course. Meaning there are a handful of students in this list who have 100% Activity Completion as well as Course Total of 80 - 100.
I don't use activity completion, so I can't help with that. But my guess is that there is a table, or tables, that contain that information. I see a number of tables in my Moodle 3.6 that have "completion" in their names.
The only help that I can be is to suggest to you to search the contributed reports.
hi can you suggest me sql query for displaying grade category screenshot is attached
