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

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

Comments

  • Tim at Lone Pine Koala Sanctuary
    Thu, Apr 19, 2018, 11:48 PM
    It is a path on the local server. Here is how the setting is used in the code https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L670. (That could be a file path that is a mount of a network disc, or similar.)
  • Sat, Apr 21, 2018, 3:00 AM
    Thank you very much for the help smile
  • Wed, Jun 20, 2018, 2:57 AM
    I have started to work with this plugin, which I found to be incredibly useful (thank you to the developers).

    However, I have had to make some small modifications on my version to get it working the way I need and I want to share my feedback.

    In the locallib.php file, I found that the subject line for the emails that get sent is not currently pulling the query name (display name) which it is coded to do. I modified these lines in mine by changing "report_customsql_plain_text_report_name($report->displayname)" in the code to "report_customsql_plain_text_report_name($report)" since the "report_customsql_plain_text_report_name()" function looks at the $report object as a whole and then returns the query name.

    Hope this is helpful,

    ~Adam Gogo
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jun 20, 2018, 9:03 PM
    Ah! We just foudn this bug ourselves last week, and our fix, which is the same as yours, is just going through testing.
  • Thu, Jun 21, 2018, 1:35 AM
    Glad to hear that it was found.

    I'm currently looking deeper into the code, and I am looking at some enhancements to meet my needs (maybe something you would be interested in). One is to have a flag to determine if emails are sent out or not if the result set is empty (helps with sending alerts).

    The other is to add an hourly option to the schedule to assist with hourly scheduled reports. This for me is useful when writing queries that will be used as alerts to specific users (mostly for teachers). I'm still working out the logic for this one.
  • Fri, Jun 29, 2018, 3:12 PM
    Why have I an error near 'Add_Discussion_Forum_Essentiels('%DCP98', '%Forum des Essentiels%', 123) LIMIT 0' at line 1 Add_Discussion_Forum_Essentiels('%DCP98', '%Forum des Essentiels%', 123) LIMIT 0, 2 [array egg]. My line contains only Add_Discussion_Forum_Essentiels('%DCP98', '%Forum des Essentiels%', 123). Why adding LIMIT to my line ???
  • Panthers fan
    Wed, Jul 4, 2018, 9:39 PM
    A couple of questions about changing run time of scheduled reports

    For reference, all my ad-hoc scheduled tasks run at 10 minutes after every hour and all reports have the "Scheduled, Daily" option.

    Question 1 - if a report is set to "Scheduled, daily" and it fails to run for some reason at, say, 7:10am, how would I "reset" it so that scheduled tasks can run it again later the same day?

    Question 2 - what is the latest time I can edit a report, and change the time, so that it is still run? For example, if I edit a scheduled report that was scheduled to run at 7:10am and save it at 7:08am, will it be run at 7:10am?

    Just wondering for debugging purposes. Thanks again for a great plugin that, quite frankly, my group could not do without.

    Richard
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jul 4, 2018, 10:30 PM
    Reports run when cron triggers the scheduled task. That is unlikely to be exactly on the hour.

    I am afraid that the only way to find out exactly how it works is to read the code starting at https://github.com/moodleou/moodle-report_customsql/blob/master/classes/task/run_reports.php. (It is not particularly complicated.)
  • Wed, Sep 26, 2018, 3:25 PM
    Hi is it possible to add a extra roll to report_customsql?
  • Tim at Lone Pine Koala Sanctuary
    Wed, Sep 26, 2018, 4:19 PM
    Well, technically you need to ask 'Is it possible to add extra capabilities ...'. The only way to do that is to edit the code. Specifically you would need to add them to this list: https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L221

    You also need to remember that these reports live in the System context, and only a few roles are assigned in that context. (E.g. Teacher role only exists inside a particular course, and these reports are outside all courses.)
  • Mon, Oct 29, 2018, 12:16 PM
    Since I updated to version 2018080900, I've noticed that the links don't display as links anymore, just as html text. For example I used the code (postgreSQL): select '' || c.fullname || '' "Course"
    Has some setting changed that I need to review?
  • Mon, Oct 29, 2018, 12:20 PM
    Oops sorry that didn't work I used the code without the spaces select ' < a h r e f = " % % WWWROOT % % / course / view . php % % Q % % id = ' || CAST (c . id AS VARCHAR) || '">' || c.fullname || '< / a >' "Course"
  • Tim at Lone Pine Koala Sanctuary
    Mon, Oct 29, 2018, 7:10 PM
    Sorry, known issue that we have not yet had time to fix. https://github.com/moodleou/moodle-report_customsql/issues/34
  • Fri, Nov 23, 2018, 3:27 PM
    Hi. Does this do paging for the query results html table?
  • Tim at Lone Pine Koala Sanctuary
    Fri, Nov 23, 2018, 4:41 PM
    No, by design. There is a hard limit of 5000 rows on what one query can return, so it is just simpler to run the query once and show all the results.
Please login to post comments