Ad-hoc database queries

Reports ::: report_customsql
Maintained by Tim at Lone Pine Koala SanctuaryTim Hunt, at the OU (Perry building)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.
Latest release:
3793 sites
146 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.


Screenshot #0


Tim at Lone Pine Koala Sanctuary
Tim Hunt (Lead maintainer)
at the OU (Perry building)
Mahmoud Kassaei: Developer
Please login to view contributors details and/or to contact them

Comments RSS


  • Wed, Mar 27, 2019, 6:18 AM
    Hi Tim, are you accepting contributions to this plugin? We had a need for hourly reports at my institution, so I made a few additions to add a category of reports that are generated every time the CRON task runs. Would this be something you're interested in adding to the master version?
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 27, 2019, 7:03 PM
    Yes, contributions to this plugin are welcome.

    At least, that is what we hope to do. Sometimes I get too busy, and don't have time to review proposed changes as soon as they are permitted. You may have to be patient, in which case I apologies in advice. Still, do get your suggested changes onto github as a pull request.
  • Fri, Apr 12, 2019, 5:15 PM
    why i always get this error :
    The type of the file you selected (application/msword) does not match the type expected by this import format (application/vnd.openxmlformats-officedocument.wordprocessingml.document).

    i use .doc file type.
    really need help sad
  • Sat, Apr 13, 2019, 12:28 AM
    Hello Sharika,

    Without going into too much detail, the error you mentioned is probably caused because the Word version you're using doesn't use XML, so consider saving your Word file (.doc) as a more recent version (.docx) and try using it again.
  • Sat, Apr 13, 2019, 2:53 AM
    Is it possible to execute a stored procedure and pass in a variable? I am trying to implement some custom reports by querying the database. We have moodle 3.6 and using the Adaptable Theme. I have a NavBar tool menu named Reports, and my plan is to put the custom reports in as drop down options for the Teachers. I am successful with putting simple queries in the menu but I was thinking it would be better to put complex reports in using stored procedures. I am very comfortable with writing SQL scripts but new to Moodle.

    I am using: Exec [dbo].[usp_MoodleTimeReport] @userid=13. I tried removing the variable and hard coding it in the report for testing but I always get the below error.

    Error when executing the query: Error reading from database SQLState: 42000
    Error Code: 156
    Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'ORDER'.
    SQLState: 42000
    Error Code: 153
    Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.
    Exec [dbo].[usp_MoodleTimeReport] ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY [array egg]

    I don't see any way to attach a pic to this post.

    Any help would be appreciated!
  • Wed, May 1, 2019, 3:27 PM
    Hi Tim and Mahmoud,
    Thank you for providing this useful plugin, I created the below query to get number of students enrollment per course but it looks like the multi-lang filter is not applied so I got the course name as {mlang en} Hyperledger Fabric Chaincode {mlang} {mlang ar} الشفرة المسلسلة للـهايبرليدجر فابريك {mlang}

    SELECT c.fullname AS 'Course Name',
    ,COUNT(u.username) AS 'Number of Students'
    FROM {role_assignments} AS r
    JOIN {user} AS u on r.userid =
    JOIN {role} AS rn on r.roleid =
    JOIN {context} AS ctx on r.contextid =
    JOIN {course} AS c on ctx.instanceid =
    WHERE rn.shortname = 'student'
    GROUP BY c.fullname, rn.shortname

    So I created the below query which remove multilang tags and When I tried it in PHPMYADMIN and I got it work correctly but when I use it in the plugin I got a wrong course name as

    Hyperledger Fabric Chaincode {mlang} {mlang ar} الشفرة المسلسلة للـهايبرليدجر فابريك {mlang}

    الشفرة المسلسلة للـهايبرليدجر فابريك {mlang}

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(c.fullname, '{mlang en}',-1),'{mlang}',1) as 'Course Name EN',
    SUBSTRING_INDEX(SUBSTRING_INDEX(c.fullname, '{mlang ar}', -1),'{mlang}',1) as 'Course Name AR',
    c.shortname as 'Short Name'
    ,COUNT(u.username) AS 'Number of Students'
    FROM {role_assignments} AS r
    JOIN {user} AS u on r.userid =
    JOIN {role} AS rn on r.roleid =
    JOIN {context} AS ctx on r.contextid =
    JOIN {course} AS c on ctx.instanceid =
    WHERE rn.shortname = 'student'
    GROUP BY c.fullname, rn.shortname
  • Tim at Lone Pine Koala Sanctuary
    Wed, May 1, 2019, 8:22 PM
    It is intentional that the output is not passed through Moodle's text filters. Generally speaking, these are report for admins, to show the actual data in the database.

    So, doing what you tried: breaking up the string in the SQL query, is probably the best way to do this if that is what you want. I am surpised that the same SQL would work differently in PHPMyAdmin and in the report. The only think I can think of is that in the way that Moodle handles database queries, { and } are special characters. That is probably what is cusing the problem. Try replacing them with CHR(NN).
  • Wed, May 15, 2019, 11:50 AM
    Thanks for this useful and great plugin, however, I have one issues if you could help.
    CSV file exported from this plugin (unlike from Configurable Reports ) contains double quotes " in every data in the colum (For example, "", "Fred", "Smith"). Is there anywhere I can set it to omit that? The exported CSV wouldn't work with our SMS due to that.

    Thanks for your time responding to this.

    Kind Regards,
  • Tim at Lone Pine Koala Sanctuary
    Fri, May 24, 2019, 5:22 AM
    The CSV generated by this report is completely standard CSV format. If your SMS cannot handle that, well, you should contact the people who make that SMS.

    You may be able to work round the problem by loading the CSV into Excel (or similar) and re-saving it. Or, making a simple script to re-format the CSV after you have downloaded it.
  • Fri, May 24, 2019, 7:37 AM
    Thanks Tim.
    As the export and import is scheduled and pushed automatically in Ad Hoc the later sounds a viable solution.
    SMS vendor wouldn't change for just us.
    I shall also see if configurable report can be cofigured to svhedule download as it produces CSV format without double quote.

    Thanks for you response and time.
  • Fri, May 24, 2019, 3:19 PM

    Is it possible that an option to the limit of rows to be displayed can be added in the plugin? Or any other recommendation to view more than 5000 rows rather than changing it from the /reports/customsql/locallib.php

  • Fri, Jun 7, 2019, 5:29 PM
    shall you confirm this plugin offer the feature to send reports by night ? By example, every monring, i need to get the list of last created account (new subscribers).
    i read at the documentation and i can't see any information about email sending, setitng a date timer to send....
    ps : why so few screenshots of this plugin ?
    thank you
  • Mon, Jul 1, 2019, 4:23 PM
    Hello Creator,

    I was wondering if its possible to modify the schedule settings of the plugin to run every 5 minutes and save the csv somewhere in the server?
  • Tim at Lone Pine Koala Sanctuary
    Mon, Jul 1, 2019, 6:00 PM
    Well, you could do that by writing code to change how the scheduling works.
  • Tue, Jul 30, 2019, 8:25 PM
    Hello. LOVE AD-HOC plugin. Is there anyway to clear the old "Archived versions of this query"? We run it daily and we're not sure how to see the most recent because it looks like the archive is full and stopped archiving? Thanks
Please login to post comments