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.
1682 sites
908 downloads
40 fans

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
  • 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 -- email@email.org < /var/www/moodledata/admin_report_customsql/2/message.txt email2@email2.edu < /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..

    SELECT prefix_course.id,prefix_course.shortname,prefix_course.fullname, prefix_course_categories.id,prefix_course_categories.name
    FROM prefix_course, prefix_course_categories
    WHERE prefix_course.category = prefix_course_categories.id
    AND prefix_course_categories.parent=0
    order by prefix_course_categories.name, prefix_course.fullname


    thanks in advance
  • Picture of Roger Domínguez Argemí
    Thu, 19 Jan 2017, 7:53 PM
    Hi Tim,

    Do you know when the plugin will be updated? Thanks!

    Cheers,

    RD
  • Tim at Lone Pine Koala Sanctuary
    Thu, 19 Jan 2017, 7:54 PM
    You mean for Moodle 3.2? I am afraid the answer is "when I get around to testing it". I am sure it works, but I don't like to mark it as compatible until I have acutally tested.
  • Tim at Lone Pine Koala Sanctuary
    Thu, 19 Jan 2017, 7:55 PM
    If you are able to test it yourself (download the zip for 3.1, and install it manually), then let me know. That would be very helpful. Thanks.
  • Picture of Roger Domínguez Argemí
    Sat, 11 Feb 2017, 6:16 PM
    Hi Tim! We will do it and we will back to you. Thanks smile
  • Panthers fan
    Thu, 23 Feb 2017, 12:44 AM
    Thanks for a great plugin (use it ALL the time). Quick question - any way to tell when a report was last edited (changed)? Thanks again.
  • Tim at Lone Pine Koala Sanctuary
    Thu, 23 Feb 2017, 1:18 AM
    We don't track timemodified in the report_customsql_queries database table. That is probably bad practice. If anyone wants to code that addition, I would happily merge a pull request. I'm afraid I don't have time to do it myself just now.
1 2 3 4 5 6 7 8 9 10
Please login to post comments