Reports: Ad-hoc database queries

Maintained by Tim at Lone Pine Koala Sanctuary Tim 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.
Picture of Marcus Green
Marcus Green
Wednesday, 26 August 2015, 3:17 AM
General comments
This is a mature program that has been around since before Moodle 2.0 and has been downloaded tens of thousands of times. It was created by the UK Open University, who run one of the worlds largest Moodle installations. Because it is supported by the OU it is very likely to be kept up to date with future releases of Moodle.

To create new reports you will need to be familiar with the industry standard SQL (Structured Query Language), and for full advantage you will need to understand table joins.  This can be complex as a basic Moodle install includes around 300 tables and to make sense of the data you need to work out the relationship between the tables. I have created a diagram that can help with this

If that sounds daunting, there is a large collection of pre-written queries available at
Which at the time of writing has 117 reports divided into categories such as Users, Assignments and Activities. You could just copy and paste from there but you will probably want to customise the display, so it is a good reason to learn a bit of SQL. Be aware that the structure of Moodle can change with new releases so some reports will need tweaking before they will work. An example of this is that the logging system changed with Moodle 2.7.

Queries can have parameters that can be customised at runtime by the end user. For example could create a report that asked for all logins between two dates. When the report was run can be set to prompt for the start and end date.  You can use a special token to represent the userid of the person running the report. This is particularly useful for giving students the ability to get information only about themselves from Moodle. The reports are formatted for odd and even lines i.e. light then dark to make it easier to run your eye down the output. It allows the creation of report categories to organise the reports. You might for example setup categories for Assignments, Quizzes and Students, or by  departments

The visibility can be set using the Moodle permissions systems. With the default permissions set this this means they can be seen by admin only, teachers and admin or everyone (including students)

It is not a banded report writer and so doesn't support pretty reports with breakpoints, fonts or graphs but it is possible to create some interesting effects such as items in the output can be hyperlinks that take you to items within Moodle. For example a list of assignments can be created where clicking on the name will take you to the assignment within the course.  To do this type of work you need to be familiar with the string handling functions of your database, which for MySQL is described here

I sometimes use the Left function to truncate the display of long results to allow the clear display of more columns.  To prevent overloading of the database there is a limit of 50,000 rows being returned from any query.

It prevents the use of queries that change data. If you create an update query you get a message that says “You are not allowed to use the words ALTER, CREATE, DELETE, DROP, GRANT, INSERT, INTO, TRUNCATE, UPDATE in the SQL.” You can only use SQL that is valid for the database type you are working with. Most of the pre-created reports assume you are using MySQL. If you are using something else you might have to tweak the code. For example many of the examples use the from_unixtime function to convert dates to something human readable.

If you try this where you are using Microsoft SQL Server you will get an error that includes the message “'from_unixtime' is not a recognized built-in function name”. You should be able to convert to code that your database will understand but it is yet another argument to run the same database back end as most other Moodle users.