## Reports: Ad-hoc database queries

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.
1732 sites
37 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.

### Contributors

Tim Hunt (Lead maintainer)
Mahmoud Kassaei: Developer
Please login to view contributors details and/or to contact them

### Awards

• Wed, 15 Jun 2016, 1:42 AM
quick question that probably has been answered although i couldn't find it on the documentation: is it possible to create a paginated report? let's say i have 1000 records, i don't want to have an immense scroll. Thanks
• Wed, 15 Jun 2016, 1:43 AM
Not possible.
• Thu, 16 Jun 2016, 9:50 PM
Hi Tim,
Will this officially support Moodle 3.1?
• 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.
• 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.
• 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.)
• 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.
• 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
• 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.
• 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
• 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

• Thu, 19 Jan 2017, 7:53 PM
Hi Tim,

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

Cheers,

RD
• 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.
• 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.
• Sat, 11 Feb 2017, 6:16 PM
Hi Tim! We will do it and we will back to you. Thanks