Your Moodle version

Reports: Ad-hoc database queries

report_customsql
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.
1k
1k
32
Moodle 2.7, 2.8, 2.9, 3.0

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.

Screenshots

Screenshot #0

Contributors

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
  • 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.
  • Picture of Mike Finch
    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” ?
  • Picture of Mike Finch
    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 http://php.net/manual/en/function.strftime.php 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: https://github.com/moodleou/moodle-report_customsql/tree/eventfix. However, I don't have time to test this. Can you test?
  • Picture of Mike Finch
    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.
  • Picture of Laura Robinson
    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?)
  • Picture of Brandon Jimenez
    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.
  • Picture of Nicolas Dunand
    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.
  • Picture of Charles Fulton
    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.
  • Picture of David Manning
    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.
  • Picture of Ben Loudon
    Thu, Sep 1, 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, Sep 1, 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.
1 2 3 4 5 6 7 8 9 10
Please login to post comments