report_customsql
Maintained by Tim Hunt, 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.
2834 sites
116 fans
Current versions available: 9

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

Mahmoud Kassaei: Developer

### Awards

• Fri, Jan 3, 2020, 6:59 AM
Thanks for a great plugin. Does this plugin have a dedicated forum to ask for assistance with writing custom reports? If not, where would I post to ask for some guidance with a rather complicated course completion report (well, at least for me it is)? Thanks.
• Mon, Jan 6, 2020, 7:25 PM
People tend to discuss queries in threads like this: https://moodle.org/mod/forum/discuss.php?d=244574 (either the configurable reports forum, of the General developer forum.)
• Tue, Jan 7, 2020, 12:59 AM
@Jose, that is not possible. Sorry. (Might be a nice feature suggestion, if anyone has time to develop it.)
• Sat, Feb 1, 2020, 5:55 PM
Hi is there any way to increase the row return limit from 5000
• Sat, Feb 1, 2020, 6:05 PM
Yes. In the latest version, you can change the 5000 limit. The setting is on Admin -> Plugins -> Report -> Add-hoc DB queries.
• Wed, Mar 4, 2020, 11:28 AM
Hello all,
I would like to ask some questions related to Ad-hoc report. After admin create any query for report, can we allow other users to see those report beside admin? and how?
• Wed, Mar 4, 2020, 4:18 PM
There is very limited ability to control this. When you create the report, you can choose a capability for who can access the report from three options. Then, you can think about customising a role (but it has to be a site-wide role) to give it those permissions.

Or for a scheduled report, you can get the results sent out by email.

I realise that there are situations where none of this is good enough, but we have never needed it ourselves, so we have not yet developed anything better.
• Thu, Mar 5, 2020, 3:59 AM
@Tim, can you please advise: I set up a report, select 'Scheduled, daily' at 10:10 for run, and entered an username for 'Automatically email to'. However nothing happened, even after cron job ran. Did I miss anything? Thanks.
• Thu, Mar 5, 2020, 4:00 AM
@Tim, can you please advise: I set up a report, select 'Scheduled, daily' at 10:10 for run, and entered an username for 'Automatically email to'. However nothing happened, even after cron job ran. Did I miss anything? Thanks.
• Thu, Mar 5, 2020, 4:13 AM
Can you check under Admin / ... / Scheduled tasks. Find the relevant task, and see what it says there.
• Thu, Mar 5, 2020, 4:39 AM
Thanks Tim for your quick response! Under Admin / server / Scheduled tasks, nothing is there, no entries. Is there any other place the tasks should be set?
On the DB table mdl_report_customsql_queries, I do see the report with runable 'daily' and time etc.
• Thu, Mar 5, 2020, 5:01 AM
That page should list a lot of tasks, including report_customsql / run_reports. If you don't see the task there, there must be some problem with how Moodle installed the plugin. I have never seen that page be blank.

... Ah! if that page is blank, then perhaps a fatal error is happening. Check your logs, perhaps in conunction with turning on Debugging.
• Mon, Mar 30, 2020, 8:53 PM
Hi Tim,
Thanks for pointing that out. We did find the errors of value on the table, and once they are corrected the Scheduled Tasks page shows all the entries.
My question is still with the Ad-hoc report itself:
For the ‘Automatically email to’ field, if enter an email, it prompts with message of User with the username 'lehuang@ssw.rutgers.edu' does not exist
If enter an username, and ran the corn job, below error was received:
“Exception - Argument 1 passed to message_send() must be an instance of core\message\message…”
We have Moodle 3.6.2. Thanks.
• Tue, May 12, 2020, 11:24 PM
Hi Tim,
Just FYI I figured it out and made it work, by a minor change:
changed $eventdata = new stdClass() to$eventdata = new core/message/message()