Your Moodle version

Reports: Ad-hoc database queries

report_customsql
Maintained by Tim at Lone Pine Koala Sanctuary Tim Hunt
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.
Moodle 2.2, 2.3, 2.4, 2.5

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

Contributors

Tim at Lone Pine Koala Sanctuary
Tim Hunt (Lead maintainer)
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Picture of Perry Way
    Wed, Mar 5, 2014, 3:22 AM
    Hello, I tried installing this module and got the following error:

    Default exception handler: Plugin "mod_customsql" is installed in incorrect location "$CFG->dirroot/mod/customsql", expected location is "" Debug:
    Error code: detectedmisplacedplugin
    * line 583 of \lib\upgradelib.php: plugin_misplaced_exception thrown
    * line 395 of \lib\upgradelib.php: call to upgrade_plugins_modules()
    * line 1584 of \lib\upgradelib.php: call to upgrade_plugins()
    * line 431 of \admin\index.php: call to upgrade_noncore()
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 5, 2014, 6:18 AM
    The error message is confusing. It should read

    Default exception handler: Plugin "report_customsql" is installed in incorrect location "$CFG->dirroot/mod/customsql", expected location is "$CFG->dirroot/report/customsql"
  • George
    Wed, Apr 16, 2014, 5:54 AM
    I need a query to count the number of students and Non-editing teachers who are enrolled per course. The final report must have Country, course's name, Number of students, Number of Non editing teachers. That's all. Can someone help me?
  • Picture of Helen Moraes
    Mon, May 5, 2014, 9:15 PM
    Hi, and thank you for this great plugin!
    A stupid question maybe. I make a query on course completion dates, and give me results in english lang (like "monday 5th May"): is there any way to have results in other languages, such as mine? My DB, as far as i know, and my Moodle have NON ENGLISH lang settings, so I can't figure out how to solve this problem.
  • Tim at Lone Pine Koala Sanctuary
    Tue, May 6, 2014, 5:58 AM
    It uses Moodle's usedate function to display dates, which should display them in your own language. I don't know why it is not working.
  • Picture of Jon Bolton
    Wed, May 7, 2014, 1:24 AM
    I'm struggling with the placeholder functionality sad

    This query works, and returns results for courseid=32...

    SELECT DISTINCT firstname, lastname
    FROM prefix_user m
    INNER JOIN prefix_grade_grades_history mgh
    ON m.id =mgh.userid
    INNER JOIN prefix_grade_items mgi
    ON mgi.id = mgh.itemid
    WHERE m.deleted = '0'
    AND mgh.source = 'userdelete'
    AND mgi.courseid = '32'
    ORDER BY m.lastname, m.firstname

    But if I try to put a placeholder so that the person running the report can choose the courseid, by replacing

    AND mgi.courseid = '32'

    with

    AND mgi.courseid = ':course_id'

    it does give me a placeholder text box on the edit screen, but it returns an error when the query is executed:

    Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.
    Debug info:
    Error code: queryfailed
    Stack trace:
    line 476 of /lib/setuplib.php: moodle_exception thrown
    line 102 of /report/customsql/view.php: call to print_error()

    So, what am I missing/doing wrong?
  • Tim at Lone Pine Koala Sanctuary
    Wed, May 7, 2014, 3:44 PM
    Placeholders should not have single quotes.

    AND mgi.courseid = :course_id
  • Picture of Jon Bolton
    Wed, May 7, 2014, 9:12 PM
    Yes, I originally tried without the single quotes. Just tried again and got same error message...

    Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.
    Debug info:
    Error code: queryfailed
    Stack trace:
    line 476 of /lib/setuplib.php: moodle_exception thrown
    line 102 of /report/customsql/view.php: call to print_error()

    Using Moodle 2.5.2+ (Build: 20131101), plugin version 2013062400, running on MAMP (PHP Version 5.3.28) and MySQL 5.0.8.
  • Picture of Lester Cunningham
    Fri, May 16, 2014, 8:16 PM
    Problem getting reports emailed resolved by correcting file locallib.php. Variable $USER is used in upper and lowercase throughout. Change to uppercase on lines: 433, 439, 547, 548. (5 changes in all). Maybe noticeable only on Linux servers.

    I am using customsql 2014020700.
  • Tim at Lone Pine Koala Sanctuary
    Fri, May 16, 2014, 8:23 PM
    The previous comment is not correct.
  • Picture of Lester Cunningham
    Fri, May 16, 2014, 9:51 PM
    Hi Tim. Whether you think this is incorrect or not, this was picked up by debugging and changing the case has fixed the problem. Now I can get scheduled reports emailed out. This may not be an issue on Windows servers where case is ignored. Fixed my problem and I would recommend this to others using Linux server.
  • Tim at Lone Pine Koala Sanctuary
    Fri, May 16, 2014, 10:04 PM
    PHP syntax works the same on both Windows and Linux. It is case sensitive, on all platforms. Replacing $user with $USER throught a method will make no difference to how the code works. I don't know why making that change makes the code work. Are you sure you did not change something else at the same time? Try changing them all back ot lower case. I bet it will still work.
  • Picture of Lester Cunningham
    Fri, May 16, 2014, 10:39 PM
    Debugging error code says 'cant find $USER at line xxx'. This is because it is written as '$user'. Unlike Windows, Linux thinks this is a different variable. Updating the code fixes the problem. I raise this to help other users of this brilliant module who may have similar problems. Everyone knows that Linux files are case sensitive and Windows files are not. This file needs updating in future release.
  • Picture of Jeff King
    Wed, May 21, 2014, 4:27 AM
    I agree with Lester that changing all to upper case $USER in file: locallib.php allows emailed reports, cant set as email report otherwise. Tim, you said it yourself, PHP is case sensitive, on all platforms. You have to have proper case for it to work.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jun 18, 2014, 11:23 PM
    Right, I now see the problem. Someone added an upper-case $USER. Changing that one occurrence ot lower-case was the correct fix. Fix pushed to github and I am about to release it here.
1 2 3 4
Please login to post comments