HI,
I have been trying to find a course completion report I can run for a particular institution.
I want to know for all users in a given institution, for each course they are enrolled in, which activities and courses they have completed.
Does anyone know of this or a similar report I could try to modify?
Thanks in advance for any help.
There is a fairly easy report which provides most of what your asking...
SELECT u.firstname AS 'First Name'
,
u.lastname AS 'Last Name'
,
u.institution as 'Agency'
,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS 'Course'
,
hrs.data AS 'Course Hours'
,
IFNULL((SELECT COUNT(gg.finalgrade)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid=gi.id
WHERE gi.courseid=c.id
AND gg.userid=u.id
AND gi.itemtype='mod'
GROUP BY u.id,c.id),'0') AS 'Activities Completed'
,
IFNULL((SELECT COUNT(gi.itemname)
FROM prefix_grade_items AS gi
WHERE gi.courseid = c.id
AND gi.itemtype='mod'), '0') AS 'Activities Assigned'
,
/*show 100% complete and date if course completion record exists. show incomplete otherwise*/
(SELECT IF(cc.timecompleted<>0,
(SELECT CONCAT('100% completed ', FROM_UNIXTIME(MAX(cc.timecompleted),'%m/%d/%Y'))
FROM prefix_course_completions
WHERE prefix_course_completions.course=c.id
AND prefix_course_completions.userid=u.id), 'incomplete')) AS 'Date Completed'
,
(SELECT IF(cc.timecompleted<>0,hrs.data,'0')) AS 'Hours Earned'
FROM prefix_course AS c
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_user_enrolments ue ON ue.userid=u.id
JOIN prefix_enrol e ON e.id=ue.enrolid
RIGHT JOIN prefix_course_completions cc ON cc.course=c.id and cc.userid=u.id
JOIN prefix_role AS r ON r.id = e.roleid
WHERE
ra.roleid='5' ### "5" = student
GROUP BY u.id, c.id
ORDER BY u.lastname, u.firstname, c.fullname
I would add a WHERE clause for choosing the institution (I took mine out and a couple other things that might hang you up). This doesn't identify which activity they completed, just the number of graded activities and the number of completed graded activities.
I'll add another post with the report we developed that actually pulls specific activities (really not pretty).
Well, apparently I didn't keep the test report that pulls all activities. My objective was really to pull information about one activity and I scrapped it because I couldn't get what I wanted out of it... I used Business Objects instead for that, so...
