report_customsql
Maintained by 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.

Contributors

• 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() • 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" • 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? • 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. • 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. • Wed, May 7, 2014, 1:24 AM I'm struggling with the placeholder functionality 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? • Wed, May 7, 2014, 3:44 PM Placeholders should not have single quotes. AND mgi.courseid = :course_id • 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. • 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.
• Fri, May 16, 2014, 8:23 PM
The previous comment is not correct.
• 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.
• 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.
• 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.
• 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. • 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.