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.
1674 sites
34 fans
Moodle 2.4, 2.5, 2.6, 2.7

This report, created by The Open University, lets Administrators set up arbitrary SQL select queries that anyone with the appropriate permissions can then run. Reports can be set to be runnable on-demand, or automatically run weekly or monthly.

The results are displayed as a fairly plain HTML table, and can also be downloaded as CSV.

The idea is that this lets you quicly set up ad-hoc reports, without having to create a whole new admin report plugin.


Screenshot #0


Tim at Lone Pine Koala Sanctuary
Tim Hunt (Lead maintainer)
at the OU (Perry building)
Mahmoud Kassaei: Developer
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Picture of Mike Finch
    Fri, 11 Mar 2016, 12:17 AM
    Re: Suggested fix for null context

    In each of the 3 event classes that this plugin has, the get_url() function wants to use $this->context. However, sometimes that context is null, and I get notice messages like the following on my page.

    Notice: Trying to get property of non-object in C:\moodle\report\customsql\classes\event\query_edited.php on line 51

    The fix is to use $this->get_context(), which will create a context if it does not exist yet.
  • Tim at Lone Pine Koala Sanctuary
    Fri, 11 Mar 2016, 1:04 AM
    Moodle's userdate is a wrapper around strftime, and says both %F and %T are fine. I don't know why it does not work for you. Anyway, that is the date format we want for this report.

    Thanks for finding the event problem. I just pushed what I think is the right fix to and eventfix branch in git: However, I don't have time to test this. Can you test?
  • Picture of Mike Finch
    Fri, 11 Mar 2016, 3:23 AM
    Re: Suggested fix for null context

    I tested the changes in commit 5d35b97. I did not experience any error or notice messages on my pages. I created, edited, and deleted a query via the "Ad-hoc database queries" report page, and did not experience any problems.
  • Picture of Laura Robinson
    Tue, 29 Mar 2016, 10:46 PM
    I'm running Moodle 3.0.2 and am experiencing issues with the automatic email element of the plugin. I have tested with two different users, but as yet neither user has received a scheduled report email. I can see from the plugin interface that the daily query is running successfully and I can view the output within Moodle, but what's happened to the email is a mystery. Any tips on how to track down where the problem might lie (i.e. within Moodle/the plugin vs my mailserver?)
  • Picture of Brandon Jimenez
    Wed, 15 Jun 2016, 1:42 AM
    quick question that probably has been answered although i couldn't find it on the documentation: is it possible to create a paginated report? let's say i have 1000 records, i don't want to have an immense scroll. Thanks
  • Tim at Lone Pine Koala Sanctuary
    Wed, 15 Jun 2016, 1:43 AM
    Not possible.
  • Picture of Nicolas Dunand
    Thu, 16 Jun 2016, 9:50 PM
    Hi Tim,
    Will this officially support Moodle 3.1?
  • Tim at Lone Pine Koala Sanctuary
    Thu, 16 Jun 2016, 9:52 PM
    In due course, yet. Right now, when I ran the automated tests in 3.1, something failed. I don't remember what, and since then I have not had time to fix it. I don't think it will be anything very serious.

    If you have a test server, try running the current code with Moodle 3.1. It might acutally work.
  • Picture of Charles Fulton
    Tue, 21 Jun 2016, 12:27 AM
    I just ran the automated tests with 3.1.0 and didn't see any behat/phpunit problems. We're doing some manual testing at the CLAMP Hack/Doc now and we'll report back if there are problems.
  • Picture of David Manning
    Tue, 23 Aug 2016, 12:20 AM
    Nice plugin! Is it possible to see just a report in a block on-screen without showing the list of reports first? (I'm trying to make a "High Scores" display on the page which will be a report of grade table data.)
  • Tim at Lone Pine Koala Sanctuary
    Tue, 23 Aug 2016, 12:21 AM
    You can link directly to any report. Just copy and paste the URL when you are viewing the report.
  • Picture of Ben Loudon
    Thu, 1 Sep 2016, 6:55 PM
    Are there any plans to email the results in a scheduled reports as an attachment rather than in the body of the email? I have created a report with questionnaire results but am having problems automating the process of saving the data which I need to load into another system. Thanks Ben
  • Tim at Lone Pine Koala Sanctuary
    Thu, 1 Sep 2016, 7:31 PM
    No plans for us to implement that. If anyone else implemented it, we would probably be happy to merge the functionality in.
  • Picture of Daniel Wolff
    Tue, 4 Oct 2016, 9:14 PM
    We were able to replicate this functionality (emailing specific reports) by setting up a cron job using mutt. If you look in the moodledata directory the report filenames are named in a predictable way. Thought I would share in case it was useful for others, in this example we are emailing a report created at 3:00 AM everyday at 5:00 AM:
    00 05 * * * root mutt -s "Subject of Email" -a /var/www/moodledata/admin_report_customsql/2/`date +\%Y\%m\%d`-030000.csv -- < /var/www/moodledata/admin_report_customsql/2/message.txt < /var/www/moodledata/admin_report_customsql/2/message.txt
  • Picture of Mohd Radzi Abdul Kadir
    Tue, 11 Oct 2016, 11:20 AM
    I have problem to figure out how to get teacher/teachers name for each course. can anyone give a clue.. here is my simple coding to get list courses..

    FROM prefix_course, prefix_course_categories
    WHERE prefix_course.category =
    AND prefix_course_categories.parent=0
    order by, prefix_course.fullname

    thanks in advance
1 2 3 4 5 6 7 8 9 10
Please login to post comments