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.
3085 sites
1k downloads
121 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
  • 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!!!
  • Picture of Paweł Suwiński
    Fri, Jul 31, 2020, 11:54 PM
    Hi, I'm new to moodle 3.9 internals after moving from 1.9 version. I made there a quick hack by copying and twicking download.php to have a feature of public download of report csv file restricted by IP address (done .httaccess) using a simple GET query. Now I need this feature to the new instance. I can just copy and addapt old hacked file or other simply way without ugly hacking is just using customdir export feture and there some short index.php script, like below one and .htaccess.

    ------------------------------- <?php
    // report id
    $id = (int)($_GET['id'] ?? 0);
    // lastone or date, default - currend day
    $lastone = isset($_GET['lastone']) ? true : false;
    $date = (int)($_GET['date'] ?? date('Ymd'));
    $pattern = sprintf('%s-%s*.csv', $id, $lastone ? '' : $date.'-');
    if(!$files = glob($pattern)) {
    header("HTTP/1.0 404 Not Found");
    exit($pattern.': files not found!'.PHP_EOL);
    }
    $file = end($files);
    header('Content-Disposition: attachment; filename="'.$file.'"');
    header('Content-Type: text/csv; charset=UTF-8');
    readfile($file);
    --------------------------------------------------

    But I am wondering is it a way to do it better using some new/ native stuff of 3.9 version, maybe webservices or just simple extention of plugin by adding new options in edit_form.php and adjusting download.php (?). After all if this feature is considered usefull I can do some contrib to the plugin. Any advice what would be the finest way to achive this?

    --
    regards
    Pawel Suwinski
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Please login to post comments