Ad-hoc database queries
Reports ::: report_customsql
Maintained by 
Tim Hunt, 
Mahmoud Kassaei, 
Anupama Sarjoshi
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.
Latest release:
3836 sites
1k downloads
147 fans
Current versions available: 10
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
Contributors
Tim Hunt (Lead maintainer)
Mahmoud Kassaei: Developer
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them
Awards
Comments 
Comments
Please login to post comments
Also, turn on Debugging to verify there is no error message.
Thanks for the quick response.
The values for course_completions.timecompleted in my query result are indeed non-NULL integers. The WHERE clause I use in my query ensures that. I can execute the same query directly on the database (removing curly braces from and adding the 'mdl_' prefix back to the table names, of course), and I get integer values for that column.
I do have the debug setting set to DEVELOPER. When I run the query from Moodle, I do not observe any error output on the page.
Looks like the report_customsql_get_element_type() function is the starting point for my investigation. I will see where it leads.
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” ?
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.
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?
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.
Will this officially support Moodle 3.1?
If you have a test server, try running the current code with Moodle 3.1. It might acutally work.