Can anyone help me with a report that gives me the number of courses in a category that a user has completed?
for example:
John, Smith, Category name, 4/5 course complete, 80%
Any and all help is welcome.
In reply to Frankie Broadhead
Re: Amount of courses in a category a user has complete
by Thomas Stanley-Jones -
Haha, yeah, that's a good challenge. But not impossible. This is a similar report I made, but you'll need to figure out where the "completion" variable comes from. I haven't had to do that yet.
SELECT CONCAT(u.firstname," ",u.lastname) As "Student Name"
,CONCAT("%%WWWROOT%%/user/profile.php?id=",u.id) AS "Student Profile"
,COUNT(en.id) AS "Number of Courses"
,CONCAT('List Teachers') AS Teachers
FROM prefix_user as u
JOIN prefix_user_enrolments AS ue ON ue.userid = u.id
JOIN prefix_enrol AS en ON en.id = ue.enrolid
JOIN prefix_course AS c ON c.id = en.courseid
WHERE en.roleid=5 AND ue.status=0 AND c.visible=1 AND c.shortname LIKE "%2022%"
%%FILTER_SEARCHTEXT_username:u.username:~%%
Group BY u.id
ORDER BY u.lastname
SELECT CONCAT(u.firstname," ",u.lastname) As "Student Name"
,CONCAT("%%WWWROOT%%/user/profile.php?id=",u.id) AS "Student Profile"
,COUNT(en.id) AS "Number of Courses"
,CONCAT('List Teachers') AS Teachers
FROM prefix_user as u
JOIN prefix_user_enrolments AS ue ON ue.userid = u.id
JOIN prefix_enrol AS en ON en.id = ue.enrolid
JOIN prefix_course AS c ON c.id = en.courseid
WHERE en.roleid=5 AND ue.status=0 AND c.visible=1 AND c.shortname LIKE "%2022%"
%%FILTER_SEARCHTEXT_username:u.username:~%%
Group BY u.id
ORDER BY u.lastname
In reply to Thomas Stanley-Jones
Re: Amount of courses in a category a user has complete
by Frankie Broadhead -
Thanks for this Tomas!
I have been trying all day and still no luck.
I am no SQL genius and I usually manage by muddling my way through so if anyone can help would be amazing.
I have been trying all day and still no luck.
I am no SQL genius and I usually manage by muddling my way through so if anyone can help would be amazing.
In reply to Frankie Broadhead
Re: Amount of courses in a category a user has complete
by Frankie Broadhead -
Just an FYI I don't need a report that has completion just enrolled on.
So I need a report that tells me how many courses a user has enrolled on in a category.
Thanks, people!
So I need a report that tells me how many courses a user has enrolled on in a category.
Thanks, people!
In reply to Frankie Broadhead
Re: Amount of courses in a category a user has complete
by Emma Richardson -
You should be able to adjust this to work:
SELECT DISTINCT c.shortname, usr.firstname, usr.lastname, usr.username, usr.email, groups.name
FROM prefix_course AS c
INNER JOIN prefix_context AS cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN prefix_role_assignments AS ra ON cx.id = ra.contextid
INNER JOIN prefix_role AS r ON ra.roleid = r.id
INNER JOIN prefix_user AS usr ON ra.userid = usr.id
INNER JOIN prefix_groups AS groups ON c.id = groups.courseid
INNER JOIN prefix_groups_members AS members ON groups.id = members.groupid
WHERE members.userid = usr.id
AND r.id = '5'
AND c.category IN (your course category id here)
ORDER BY groups.name
SELECT DISTINCT c.shortname, usr.firstname, usr.lastname, usr.username, usr.email, groups.name
FROM prefix_course AS c
INNER JOIN prefix_context AS cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN prefix_role_assignments AS ra ON cx.id = ra.contextid
INNER JOIN prefix_role AS r ON ra.roleid = r.id
INNER JOIN prefix_user AS usr ON ra.userid = usr.id
INNER JOIN prefix_groups AS groups ON c.id = groups.courseid
INNER JOIN prefix_groups_members AS members ON groups.id = members.groupid
WHERE members.userid = usr.id
AND r.id = '5'
AND c.category IN (your course category id here)
ORDER BY groups.name
In reply to Thomas Stanley-Jones
Re: Amount of courses in a category a user has complete
by Randy Thornton -
Thomas,
If you were trying to limit this to only users enrolled as Students (5), then this doesn't return the correct results: en.roleid = 5 is not the user's role, it is the default setting for the enrol method instance. So, manual and self-enrol default to role 5, which is what you will find. You can verify this yourself by adding a Self-registration method to a course and settings its default role to, for example, Non-editing teacher and you will see it shows 4 in that table even with no users enrolled. It's just the method's setting.
The roles that the users are assigned are over in the role_assignments table. You will need to join that to get the actual roles for users. There's a bunch of examples how to do that over in the Ad-hoc contributed reports page, just search for prefix_role_assignments. Here's a good example: https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Hidden_Courses_with_Students_Enrolled
If you were trying to limit this to only users enrolled as Students (5), then this doesn't return the correct results: en.roleid = 5 is not the user's role, it is the default setting for the enrol method instance. So, manual and self-enrol default to role 5, which is what you will find. You can verify this yourself by adding a Self-registration method to a course and settings its default role to, for example, Non-editing teacher and you will see it shows 4 in that table even with no users enrolled. It's just the method's setting.
The roles that the users are assigned are over in the role_assignments table. You will need to join that to get the actual roles for users. There's a bunch of examples how to do that over in the Ad-hoc contributed reports page, just search for prefix_role_assignments. Here's a good example: https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Hidden_Courses_with_Students_Enrolled
In reply to Randy Thornton
Re: Amount of courses in a category a user has complete
by Frankie Broadhead -
I am still no further but thanks for all of your help.