Reports: Ad-hoc database queries

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.
2834 sites
1k downloads
115 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.

Screenshots

Screenshot #0

Contributors

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
  • Panthers fan
    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.
  • Tim at Lone Pine Koala Sanctuary
    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.)
  • Tim at Lone Pine Koala Sanctuary
    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.)
  • Picture of Aditya Dubay
    Sat, Feb 1, 2020, 5:55 PM
    Hi is there any way to increase the row return limit from 5000
  • Tim at Lone Pine Koala Sanctuary
    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.
  • Picture of NOU Sotheany
    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?
  • Tim at Lone Pine Koala Sanctuary
    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.
  • Picture of Leslie Huang
    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.
  • Picture of Leslie Huang
    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.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Mar 5, 2020, 4:13 AM
    Can you check under Admin / ... / Scheduled tasks. Find the relevant task, and see what it says there.
  • Picture of Leslie Huang
    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.
  • Tim at Lone Pine Koala Sanctuary
    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.
  • Picture of Leslie Huang
    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.
  • Picture of Leslie Huang
    Tue, May 12, 2020, 11:24 PM
    Hi Tim,
    Just FYI I figured it out and made it work, by a minor change:
    In locallib.php send_email_notification function:
    changed $eventdata = new stdClass() to $eventdata = new core/message/message()
    Thanks.
  • MDinnovar
    Wed, Jun 3, 2020, 2:59 AM
    Excelent!!!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Please login to post comments