Reports: Ad-hoc database queries

Maintained by Tim at Lone Pine Koala SanctuaryTim Hunt, at the OU (Perry building)Mahmoud Kassaei
This report plugin allows Administrators to set up arbitrary database queries to act as ad-hoc reports. Reports can be of two types, either run on demand, or scheduled to run automatically. Other users with the right capability can go in and see a list of queries that they have access to. Results can be viewed on-screen or downloaded as CSV.

Ad-hoc database queries 2.2.1 for Moodle 2.2

Picture of Gavin Henrick
Gavin Henrick
Friday, January 20, 2012, 1:16 AM
General comments

This admin report provides a way to create completely custom reports by enabling the user to enter in SQL queries which the report engine then runs.


This module was released for Moodle 1.9 back in October 2009.  It was written by Tim Hunt of the Open University. It was later upgraded for Moodle 2 by Derek Woolhead also of the Open University.  For those unfamiliar with Tim’s work, he is the maintainer of the Quiz activity module in Moodle. It has recently had another nice feature added with variables possible now too!

There are a few community options for implementing extra reporting in Moodle and this is one of the  interesting ones. Some of the code for this plugin was used to in the Configurable reports plugin that I reviewed too.

What does it do?

The report basically provides a very simple set of options

  • create a report
  • run an existing report
  • define who has rights to see the report

When it is first installed there are no reports configured so you immediately need to add a report which means if you don’t know SQL or have a known good query, this is probably not the report generator for you.

You immediately are faced with entering in SQL and deciding who can access the report.

As already mentioned there are a number of examples on the documentation page and the contributed queries page that you could use or base your query on.

There are three options for setting who can access the reports based on 3 capabilities.

Queries can also either run on a schedule or be ran on demand. The used of scheduled queries differ from on-demand queries in one essential way; you can set it up that when you just create one line of results – the report builds up over time if you so choose it. This way you can see how the results change whereas with the on demand report you see the results as they are then when the report is ran.


This report would get  the “Ronseal” seal of approval in that it does exactly what it says on the page. Assuming you have the correct skill set and the technical understanding it is simple to set up, simple to use and helps provide reporting on your Moodle site that would otherwise require development. The community nature of the available reports really help provide a lot of examples to the novice user although I would love to see that page audited so that they are checked for hardcoded mdl and even if they are correct – just to be sure, to be sure.



Is it simple to install?

Yes. After downloadined eeded to  extracted the folder and rename to customsql. I uploaded this into the moodle\report  folder of my Moodle site.

When it was installed through the notifications page, the plug-in caused no errors and brought up no extra global settings.

Is there documentation for it?

The plug-ins page has a brief overview on the module. The Moodle Docs page is very detailed with a good number of example reports. There is also a list of 45 other reports which the community have contributed to a Moodle Docs page. There are also links to the Bug Tracker entry and a discussion forum for questions.


Is it easy for the teacher/admin to use?

Once the reports have been added this is very easy for users to run and download the results of reports. A teacher will only see the reports they are supposed to see, so they can see the report, run it and download the data if they so wish. They cannot add queries and will need to ask their administrator to do this.

For the admins, this is a very simple tool to use as long as they are comfortable with SQL. Obviously some queries are more simple than others. I do recall from a few years back of someone having a 400 line SQL report which generated some really cool statistics on a course, but I haven’t tried anything on that scale with this mod.

Is it easy for the learner/student to use?

Yes. If the student has access to the report they can run it. This is quite nice as you can create site-wide reports which use the userid of the user looking at the report using %%USERID%% in the query so personalising the report to that user.