Moodle’s built-in reporting rarely answers the question you actually have. The Configurable Reports block (block_configurable_reports) fills the gap by letting you run custom SQL against the Moodle database and present the result as a report. This guide covers writing SQL reports that are correct, safe, and survive being moved between environments, with a set of reusable recipes at the end.

This guide assumes you can install blocks and have site administrator access. Custom SQL reports in Configurable Reports run with database read access, so this is an admin-level capability, not something to hand to teachers.

Why custom SQL, and the trade-off

Configurable Reports offers several report types (Categories, Courses, Users, Timeline). The one that matters here is Custom SQL, which runs a query you write directly. It is the most powerful and the most dangerous: there is no query builder protecting you, and a heavy or unbounded query runs against your live database.

Two rules before you write a line:

  • Read-only mindset. Configurable Reports is for SELECT. Do not attempt data-modifying statements through it.
  • Bound your result set. A SELECT * across mdl_logstore_standard_log on a busy site can return millions of rows and stress the database. Always constrain with WHERE and an explicit LIMIT.

The table prefix: use prefix_, never hard-code mdl_

Every Moodle table carries a configurable prefix, mdl_ by default but frequently different (and deliberately randomized on security-conscious installs). Hard-coding mdl_ means your report breaks the moment it is moved to an environment with a different prefix.

Configurable Reports solves this with the prefix_ token. Write:

SELECT * FROM prefix_user WHERE deleted = 0

not:

SELECT * FROM mdl_user WHERE deleted = 0

The block substitutes the real prefix at runtime. This single habit is the difference between a report that is portable across test, staging, and production and one that has to be hand-edited every time.

Built-in placeholders

Configurable Reports exposes runtime values you can drop into a query so a single report adapts to context and user-supplied filters:

  • %%USERID%%: the id of the user viewing the report
  • %%COURSEID%%: the course the report is running in (when course-scoped)
  • %%STARTTIME%% / %%ENDTIME%%: bound values from a date-range filter (when the start/end-time filter is enabled)
  • %%FILTER_...%%: values from filter columns you define (see below)
  • %%WWWROOT%%: your site URL, for building links inside the SQL (see “Making columns into links”)

Example, a report that always shows the viewing user’s own activity:

SELECT c.fullname, cc.timecompleted
FROM prefix_course_completions cc
JOIN prefix_course c ON c.id = cc.course
WHERE cc.userid = %%USERID%% AND cc.timecompleted IS NOT NULL

Adding user-driven filters

Filters turn a static query into an interactive report. To let the viewer filter by, say, course, you embed a %%FILTER_COURSES:column%% token and enable the matching filter in the report’s configuration.

The critical detail that trips people up: the filter token expands to a fragment that already begins with AND (it becomes AND c.id = <value>). So you cannot put it directly after WHERE, because that produces WHERE AND c.id = ..., a syntax error. Seed the WHERE clause with an always-true condition first, then let the token append:

SELECT u.firstname, u.lastname, c.fullname, cc.timecompleted
FROM prefix_course_completions cc
JOIN prefix_course c ON c.id = cc.course
JOIN prefix_user u ON u.id = cc.userid
WHERE 1=1 %%FILTER_COURSES:c.id%%

The WHERE 1=1 seed is the standard pattern for any report using filter tokens; it makes the appended AND ... valid whether the filter is set or not. Then enable the Courses filter in the report’s configuration so the dropdown appears.

There is no “make this column a link” option in the column settings for a custom SQL report. Instead, build the link in the SQL itself, using the %%WWWROOT%% token (which expands to your site URL) inside a CONCAT:

SELECT CONCAT('<a href="%%WWWROOT%%/user/profile.php?id=', u.id, '">',
              u.firstname, ' ', u.lastname, '</a>') AS user,
       FROM_UNIXTIME(u.lastlogin) AS last_login
FROM prefix_user u
WHERE u.deleted = 0
LIMIT 200

The column renders the HTML, giving you a clickable profile link instead of a bare id. The same pattern links courses (/course/view.php?id=), quizzes, and so on. %%WWWROOT%% keeps the link correct across environments, the same way prefix_ keeps table names portable.

Recipes

All use prefix_ and an explicit LIMIT. Cast LIMIT values are literal integers, never bound parameters.

Course completions by cohort

SELECT u.firstname, u.lastname, c.fullname AS course, 
       FROM_UNIXTIME(cc.timecompleted) AS completed
FROM prefix_cohort coh
JOIN prefix_cohort_members cm ON cm.cohortid = coh.id
JOIN prefix_user u ON u.id = cm.userid
JOIN prefix_course_completions cc ON cc.userid = u.id
JOIN prefix_course c ON c.id = cc.course
WHERE coh.idnumber = 'YOUR_COHORT_IDNUMBER'
  AND cc.timecompleted IS NOT NULL
ORDER BY completed DESC
LIMIT 500

Users who have not logged in for 90 days

SELECT u.firstname, u.lastname, u.email,
       FROM_UNIXTIME(u.lastlogin) AS last_login
FROM prefix_user u
WHERE u.deleted = 0 AND u.suspended = 0
  AND u.lastlogin > 0
  AND u.lastlogin < (UNIX_TIMESTAMP() - (90 * 86400
ORDER BY u.lastlogin ASC
LIMIT 1000

Quiz attempts for a course

SELECT u.firstname, u.lastname, q.name AS quiz,
       qa.sumgrades, qa.state,
       FROM_UNIXTIME(qa.timefinish) AS finished
FROM prefix_quiz_attempts qa
JOIN prefix_quiz q ON q.id = qa.quiz
JOIN prefix_user u ON u.id = qa.userid
WHERE q.course = %%COURSEID%% AND qa.state = 'finished'
ORDER BY finished DESC
LIMIT 1000

Enrolment counts per course

SELECT c.fullname AS course, COUNT(DISTINCT ue.userid) AS enrolled
FROM prefix_course c
JOIN prefix_enrol e ON e.courseid = c.id
JOIN prefix_user_enrolments ue ON ue.enrolid = e.id
WHERE c.visible = 1
GROUP BY c.id, c.fullname
ORDER BY enrolled DESC
LIMIT 200

Performance and safety notes

  • Never query the log table without a tight time bound. mdl_logstore_standard_log is the largest table on most sites.
  • Test on a copy first. A query that is fine on a 200-user test site can lock up a 50,000-user production database. Validate against a recent clone.
  • Prefer JOIN over correlated subqueries for anything that runs across large tables; the planner handles joins far better under load.
  • Scheduled delivery (emailing a report on a schedule) is available but compounds the performance cost; make sure the underlying query is cheap before scheduling it to run unattended.

When Configurable Reports is the wrong tool

If you find yourself writing increasingly elaborate SQL to produce the same operational reports every week, that is a signal you have outgrown ad-hoc reporting. For recurring, role-scoped, scheduled compliance reporting, a purpose-built reporting layer is a better long-term fit than a growing library of hand-maintained SQL. (The plugin itself is actively maintained, with current releases through recent Moodle 5.x versions, so compatibility is not the constraint here; maintainability of a large SQL library is.)

Solin specializes in Moodle reporting, SQL, and data integration. Need help? Contact us.

Need help with a Moodle or Totara project?

Contact us