Your Moodle version

Reports: Ad-hoc database queries

Maintained by Tim at Lone Pine Koala Sanctuary Tim 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.
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.


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

Show comments
  • Picture of tim nixon
    Fri, Nov 20, 2015, 1:27 AM
    also thanks for trying
  • Picture of tim nixon
    Sat, Nov 21, 2015, 2:55 AM
    I sorted out my issues with reports not running.
    1) single entry for cron in crontab, not in cron.hourly
    2) it will only run once a day, so if the custom query ran at 8:00 today and you reschedule it for 10:00 it will not run again today (unless you set lastrun to 0 in mdl_report_customsql_queries)

    Stumbled across this..
  • Picture of Michael Milette
    Thu, Nov 26, 2015, 3:11 AM
    Hi Tim and Mahmoud,

    I just added a few pull requests.

    Any idea when a Moodle 3.0 compatible version might become available?

    Best regards,

    Michael Milette
  • Picture of Michael Milette
    Tue, Dec 1, 2015, 2:47 AM
    Hi Tim and Mahmoud,

    Just added one more pull request. This one puts the list of reports in a table to make it easier to read the list of reports, especially when combined with the expandable/collapsible list feature I added last week.

    Let me know if you have any questions or concerns.

    Best regards,

  • Picture of A Smiles
    Wed, Dec 9, 2015, 10:44 PM

    I am looking at the available report options but don't see one that counts currently enrolled users within a time period e.g. How many users were enrolled between 1 month or some other time period (1 week, 30 days). I am just looking for the number of enrolled users across the whole site that are enrolled between dates and not course specific. Could someone point me to which one would carry that out please? Or perhaps help with the query that I would need to use or change?


  • Picture of Tapajyoti Pal
    Thu, Dec 10, 2015, 3:03 PM
    I have added a query and its working fine in admin. Now i want to give that access to that report to all Student. I checked the permission and gave permission all ad-hoc allow for students . But still i am not able to see that report links.

    Can anyone help me regarding this ?
  • Tim at Lone Pine Koala Sanctuary
    Tue, Dec 15, 2015, 11:02 PM
    The latest version works fine in Moodle 3.0.
  • Picture of Claudio Bartoloni
    Mon, Dec 21, 2015, 3:05 AM
    I'm trying to make a query accessible to the students.
    I defined the query as report/customsql:view and changed the student role to make this report/customsql:view permitted to the students.
    If I report privilege for this report/customsql:view it looks that the student role is enabled to see it.
    But users with the student role do not appear to be allowed to access to it.

    Would you please suggest where to check to make it work?

  • Tim at Lone Pine Koala Sanctuary
    Mon, Dec 21, 2015, 8:19 PM
    I think the issue comes from how the report page is linked into the navigation. It means that in practice you cannot give permission to see the reports to people who cannot see the list of all reports. It should not be like that, but that is the area of code that needs to be fixed (the calls to admin_externalpage_setup).
  • Picture of graham green
    Thu, Dec 24, 2015, 4:20 AM
    hello - this looks exactly what we need terms of reporting. it works fine in our moodle 3.0 installation. one thing, i tried a couple of the user contributed reports and they work - however, the reports that we specifically want, "user logins per week and activity for a specific course" does not work. i think this contributed SQL was written for pre 2.7 or legacy reporting.

    but we have no idea on writing or amending our own SQL and therefore have no idea on what to do to create our own reports.

    does anyone have any ideas or recommendations how we can quickly learn or obtain the reports we need.
  • Picture of Claudio Bartoloni
    Sun, Dec 27, 2015, 3:31 AM
    For Tim Hunt.... Thank you for your suggestion. I don't know how to link the report page to the navigation... I supposed that enabling the students to see the report should make it visible somewhere when they log in. What I make is:
    - defined a report as visibile to anyone (report/customsql:view)
    - allowed "View custom query report" in Student role
    - Student role is assigned to context Course and Activity module but not System, User, Category and Block
    - got an overview from the "capability overview" menu item that shows "Allow" for "View custom query report" and "Student" (but it looks for System)

    Thank you for any suggestion on how to solve it or where to look and check.

  • Picture of Ronald Balhan
    Wed, Jan 20, 2016, 3:06 PM
    I have the same issue.
    - I created two reports for viewing by Non-Editing Teachers.
    - I gave the Non-Editing Teachers the report/customsql:view role.

    Result as others: No report entry (in Essential theme) to run the reports.

    - I created a HTML block in a specific course
    - I added the report links in this HTML block with /report/customsql/view.php?id=2
    - I logged in and access the course with Non-Editing Teacher role

    Result when clicking the link:
    - Navigation jumps to Home
    - Error message displayed: Sorry, but you do not currently have permissions to do that (View custom queries report)
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jan 20, 2016, 8:47 PM
    The explanation is in how the Moodle roles and permissions system works.

    Users only have the teacher role within one particular course. These reports are outside of any course, in the systems context. Users do not have the teacher role in the system context, so the permissions on the teacher role are irrelevant.
  • Picture of Ronald Balhan
    Wed, Jan 20, 2016, 9:24 PM
    Ok, I understand, thank you.
    As I don't want the Non-Editing Teacher role on system context, I created a new role (based on no archetype) on system context with only the report/customsql:view permission and assigned that role to the user(s) who are permitted to run the ad-hoc database queries in any course where I added the HTML block and all seems to works fine now.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jan 20, 2016, 9:25 PM
    Yes. That is the right way to do it. smile
1 2 3 4 5 6 7 8
Please login to post comments