Review: Admin Report – Custom SQL queries for Moodle 2

The Module I am reviewing this time is the Admin Report Custom SQL queries for Moodle. 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.

Background

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.

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 earlier.

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.

Ad Hoc query – Add screen

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:

access

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.

Is it simple to install?

Yes. Like a lot of GitHub hosted plugins, using the link from the plugin page for the Moodle 2 download, you get a zip called  timhunt-moodle-report_customsql-90d5492.zip so I needed to  extracted the folder and rename to customsql. I uploaded this into the moodle\admin\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.

As with Github hosted plugins the source code is also available to browse.

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.

** Update – at present all the contributed report queries have been fixed to correctly use the prefix_ instead of hardcoding mdl_ for table names. When creating you own queries be sure to use prefix_ .

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.

Example Report output

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.

Does it do what it promises?

This report would “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.

Keep up the good work!

Stars: 5 out of 5 stars for this Admin Report.

**Standard Reminder**

These reviews check out the plugin for usability not for security. If you are considering installing any module on your site you should also check that is secure and does not impact the server performance.