Reports: Ad-hoc database queries

report_customsql
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.
1784 sites
748 downloads
54 fans
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.

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
    Tue, 16 May 2017, 10:07 PM
    .CSV format recommends double-quoting all the data. Surely it is not hard to remoev the quotes when you load the file? Similarly, if you don't want the headers, just delete the first line.

    I expect that if you really want to make these changes, it is not hard to change the code of this report.
  • Picture of Daemon Hunt
    Wed, 17 May 2017, 4:59 AM
    Agree. Post-processing the csv or changing the report code is not difficult.
  • Panthers fan
    Thu, 25 May 2017, 9:16 PM
    Tim,

    Question - is it possible in report 1 to call report 2 via a hyperlink? If so (pressing my luck), would it be possible to pass arguments to report 2? Thanks again for a great plugin!

    Richard
  • Tim at Lone Pine Koala Sanctuary
    Thu, 25 May 2017, 10:52 PM
    1. Yes. Just make the value to display in one column of the report look like a URL, and it will automatically be clickable (and read the text on the edit query screen. You probably want to use %%WWWROOT%%.)

    2. Passing parameters to reports probably should work, but does not. E.g. suppose your query has a paramenter called course_name, then a URL like
    https://learn2.open.ac.uk/report/customsql/view.php?id=51&queryparamcourse_name=AA100-16J should work. Unfortunately it does not. We should fix that.
  • Picture of Nicola Ribatti
    Fri, 26 May 2017, 7:33 PM
    Hi to all. I have a question. I Have created a database activity in wich student insert their name, activities and hours of activity. How (with a sql query) can I get the amount of the hours for each student? Thank you very much
  • Panthers fan
    Wed, 21 Jun 2017, 11:46 PM
    Tim,
    I'm writing a report that has a username column that I would like to make into a hyperlink to loginas the particular user. Question - any way to get the required sesskey using SQL commands?
    Thanks again,
    Richard
  • Tim at Lone Pine Koala Sanctuary
    Wed, 21 Jun 2017, 11:54 PM
    I think the reason sesskey exists is precisely to stop that sort of dangerous direct linking.

    However, it would be possible to add another of the %%...%% tokens. E.g. we could have %%SESSKEY%% that gets replaced by the current user's sesskey in the output code. E.g. about here: https://github.com/moodleou/moodle-report_customsql/blob/master/view.php#L153 add a line

    $value = str_replace('%%SESSKEY%%', sesskey(), $value);

    However, I am a bit reluctant to add that to the official version of the plugin.
  • Panthers fan
    Thu, 22 Jun 2017, 12:04 AM
    Re: Adding %%SESSKEY%%

    Tim,
    No worries. I can change in my version. But your answer got me to thinking - Is there a better, safer way to accomplish what I want? Basically, we have a situation where an administrator has to loginas hundreds of users to change some data. The link in the report is just an attempt to save them a few mouse clicks for each user. If there is another way via the plugin to do this, I'm all ears.

    Richard
  • Panthers fan
    Thu, 22 Jun 2017, 12:06 AM
    Re: Adding "Expand all"

    Tim,
    I have written over a hundred reports that I have put into several categories. Question - how would someone add an "Expand all" to the top of the form (like other forms)?

    Richard
  • Tim at Lone Pine Koala Sanctuary
    Thu, 22 Jun 2017, 12:23 AM
    Expand all would be a JavaScript change. Probably not too hard to do.

    Since all the data behind Moodle is in a database, it is quite easy to change things if you can acess the database directly. It is also possible to screw up the data directly, if you make a mistake. So, be careful.
  • Picture of Chris Khoo
    Sat, 1 Jul 2017, 8:16 PM
    I am using Moodle 3.3 (Build: 20170515).

    I am getting this error. Is it because my Moodle version is not supported?

    Validating report_customsql ... Error
    [Error] Write access check [/opt/bitnami/apps/moodle/htdocs/report]
    Installation aborted due to validation failure
  • Tim at Lone Pine Koala Sanctuary
    Mon, 3 Jul 2017, 5:32 PM
    Read the error message: "Write access check [/opt/bitnami/apps/moodle/htdocs/report]"

    It looks like the file permissions are not right on your server.
  • Picture of Chris Khoo
    Mon, 3 Jul 2017, 6:50 PM
    Validating report_customsql ... Error
    [Error] Write access check [/opt/bitnami/apps/moodle/htdocs/report]
    Installation aborted due to validation failure

    For the error above, if you execute the commands below, the installation will go through.
    I wondering why need to run the command below though.

    sudo chown -R bitnami:daemon /opt/bitnami/apps/moodle/htdocs/report
    sudo chmod -R g+w /opt/bitnami/apps/moodle/htdocs/report
  • Picture of Leon Vincent
    Tue, 25 Jul 2017, 3:36 AM
    Guys, would appreciate some help on this... whilst trying to install...

    Plugin "report_customsql" is installed in incorrect location "$CFG->dirroot/blocks/customsql", expected location is "$CFG->dirroot/report/customsql"

    More information about this error

    ×Debug info:
    Error code: detectedmisplacedplugin
    ×Stack trace:
    line 835 of /lib/upgradelib.php: plugin_misplaced_exception thrown
    line 446 of /lib/upgradelib.php: call to upgrade_plugins_blocks()
    line 1754 of /lib/upgradelib.php: call to upgrade_plugins()
    line 699 of /admin/index.php: call to upgrade_noncore()
  • Picture of Jon Bolton
    Tue, 25 Jul 2017, 1:02 PM
    So the clue is in the message... you've installed the plugin in the blocks folder but it should be in the reports folder smile
1 2 3 4 5 6 7 8 9 10 11
Please login to post comments