I was in need of a few features that were not available in the 'standard' version of this excellent plugin, so I've created a new report, component and filter type to achieve what I was looking for.
A modified copy of your plugin, with these additions, can be found here:
https://github.com/davosmith/moodle-block_configurable_reports
Overview of the changes:
- The 'SQL Drilldown' report type allows you to create an SQL query that shows statistics about all the top-level categories on your site, and then 'drill down' into each category (by clicking on the link inserted into each row of the table) to get further information about that category.
- You can also include and unlimited number of independent SQL queries on a single page, by separating them with '###' - this I have used this to display a table with statistics about courses within the selected category, as well as a table of subcategories, on the same page.
- There is a breadcrumb trail to easily keep track of where you have got to and to go back again.
- There is also a new 'role' filter, that allows you to filter the results by role.
SELECT tca.name AS "Category name", r.name AS Role, count(distinct(ra.userid)) AS "User count"FROM
( SELECT name, id, path FROM prefix_course_categories WHERE id = %%DRILLDOWN_PARENT_CAT%% ) tca,
prefix_role_assignments ra
JOIN prefix_context cx ON ra.contextid = cx.id
JOIN prefix_role r ON ra.roleid = r.id
JOIN prefix_course c ON cx.instanceid = c.id
JOIN prefix_course_categories ca ON c.category = ca.id
WHERE cx.contextlevel = 50
AND (ca.id = tca.id OR ca.path LIKE CONCAT(tca.path, '/%'))
AND ca.id %%DRILLDOWN_ALLOWED_CATS%%
%%FILTER_ROLES:ra.roleid%%
GROUP BY tca.name, r.name
###
SELECT tca.id AS "drilldownid", tca.name AS "Category name", r.name AS Role, count(distinct(ra.userid)) AS "User count"
FROM
( SELECT name, id, path FROM prefix_course_categories WHERE parent = %%DRILLDOWN_PARENT_CAT%% ) tca,
prefix_role_assignments ra
JOIN prefix_context cx ON ra.contextid = cx.id
JOIN prefix_role r ON ra.roleid = r.id
JOIN prefix_course c ON cx.instanceid = c.id
JOIN prefix_course_categories ca ON c.category = ca.id
WHERE cx.contextlevel = 50
AND (ca.id = tca.id OR ca.path LIKE CONCAT(tca.path, '/%'))
AND ca.id %%DRILLDOWN_ALLOWED_CATS%%
%%FILTER_ROLES:ra.roleid%%
GROUP BY tca.id, tca.name, r.name
###
SELECT c.shortname AS "Course name", r.name AS Role, count(distinct(ra.userid)) AS "User count"
FROM prefix_role_assignments ra
JOIN prefix_context cx ON ra.contextid = cx.id
JOIN prefix_role r ON ra.roleid = r.id
JOIN prefix_course c ON cx.instanceid = c.id
WHERE cx.contextlevel = 50
AND c.category = %%DRILLDOWN_PARENT_CAT%%
AND c.category %%DRILLDOWN_ALLOWED_CATS%%
%%FILTER_ROLES:ra.roleid%%
GROUP BY c.shortname, r.name
Which, basically shows a count of:
- The total number of users assigned to each role (student/teacher/etc.) for the courses within the current category
- The same details broken down by sub-category
- The same details again, shown against any courses directly within the current category