Ad-hoc database queries

Reports ::: report_customsql
Maintained by Tim at Lone Pine Koala SanctuaryTim Hunt, at the OU (Perry building)Mahmoud Kassaei, Anupama Sarjoshi
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.
Latest release:
3836 sites
147 fans
Current versions available: 10

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
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them

Comments RSS


  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 9, 2016, 3:49 PM
    Check directly in the database. Does this column actually contain integers, or blank (null)?

    Also, turn on Debugging to verify there is no error message.
  • Wed, Mar 9, 2016, 10:41 PM
    Re: Human-readable date strings not displaying

    Thanks for the quick response.
    The values for course_completions.timecompleted in my query result are indeed non-NULL integers. The WHERE clause I use in my query ensures that. I can execute the same query directly on the database (removing curly braces from and adding the 'mdl_' prefix back to the table names, of course), and I get integer values for that column.

    I do have the debug setting set to DEVELOPER. When I run the query from Moodle, I do not observe any error output on the page.

    Looks like the report_customsql_get_element_type() function is the starting point for my investigation. I will see where it leads.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 9, 2016, 10:51 PM
    Yes. Sorry, I think you will need to debug this one yourself.
  • Thu, Mar 10, 2016, 12:29 AM
    Re: Human-readable date strings not displaying

    I have discovered that when report_customsql_generate_csv() determines that a value should be formatted as a date string, it calls userdate(), providing it with a format string of '%F %T'. With that format string, the return is always a empty string.

    In contrast, if userdate() is called with no format string, then the return is a valid string such as “Tuesday, 8 March 2016, 1:17 PM”. Therefore, the format string of ‘%F %T’ is suspect.

    Perhaps a better alternative would be to use one of the format strings in “moodle\lang\[language code]\langconfig.php” ?
  • Fri, Mar 11, 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, Mar 11, 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?
  • Fri, Mar 11, 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.
  • Tue, Mar 29, 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?)
  • Wed, Jun 15, 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, Jun 15, 2016, 1:43 AM
    Not possible.
  • Thu, Jun 16, 2016, 9:50 PM
    Hi Tim,
    Will this officially support Moodle 3.1?
  • Tim at Lone Pine Koala Sanctuary
    Thu, Jun 16, 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.
  • Tue, Jun 21, 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.
  • Tue, Aug 23, 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, Aug 23, 2016, 12:21 AM
    You can link directly to any report. Just copy and paste the URL when you are viewing the report.
Please login to post comments