Hi,
I may have set this up incorrectly but I had a requirement that if a student enrolls in one of four courses they are automatically enrolled in another parent course. I was able to achieve this using meta-courses, however the method is skewing the enrollment stats. The report query is counting every enrollment instance, not the student number. For example, the reports shows 328 students but the actual number is 286. Any suggestions? Here is the report SQL:
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',course.id,'">',course.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/user/index.php',CHAR(63),'contextid=',context.id,'">Show users</a>') AS Users
, COUNT(course.id) AS Students
FROM prefix_role_assignments AS asg
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
JOIN prefix_user AS USER ON USER.id = asg.userid
JOIN prefix_course AS course ON context.instanceid = course.id
WHERE asg.roleid = 5
AND course.idnumber LIKE 'XX%' AND course.shortname LIKE '%(XXX)'
GROUP BY course.id
ORDER BY COUNT(course.id) DESC