How would I run a report on a Category listing the number of students in each course within that Category.
Cheers,
-Rob
Hi Rob,
I think you can try the following.
I think you can try the following.
SELECT
### course_categories
cat.name AS cat_name,
##course short name with hyper link
CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',mc.id,'">',mc.shortname,'</a>') as 'Course shortname',
CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',mc.id,'">',mc.fullname,'</a>') as 'Course fullname',
###Sub query to calculate the total number of students
TNr.totalStudentNumber AS 'Nr Registered Students',
FROM prefix_course_categories AS cat
JOIN prefix_course AS mc ON mc.category = cat.id
###sub query to calculate the total number of students in a course
LEFT JOIN (SELECT
COUNT(DISTINCT(mu.id)) AS 'totalStudentNumber',
mc1.id AS courseid
FROM prefix_course_categories AS cat1
JOIN prefix_course AS mc1 ON mc1.category = cat1.id
JOIN prefix_context AS context ON context.instanceid = mc1.id
JOIN prefix_role_assignments AS mra ON mra.contextid = context.id
JOIN prefix_user mu ON mra.userid = mu.id
WHERE context.contextlevel = 50
AND mra.roleid= 5
##you can add here the category path or name##
AND cat1.path LIKE '%----%'
GROUP BY mc1.id) AS TNr ON TNr.courseid = mc.id
####
WHERE
##you can add the category path or name here too##
cat.path LIKE '%----%'
GROUP BY mc.id
ORDER BY mc.shortname
Please try it and let me know if that works or if you need further help.
Best wishes
Mo
Hello Mo,
I am not sure what this means.
I have access and can use the graphical admin interface but I have no access to the backend or the data base.
Is it possible to do this using a bulk action?
Cheers,
-Rob
I am not sure what this means.
I have access and can use the graphical admin interface but I have no access to the backend or the data base.
Is it possible to do this using a bulk action?
Cheers,
-Rob
I thought you were talking about the Configurable Reports block. You can use this SQL code if you have access to this block.
Mo
Mo