Ad-hoc database queries

Reports ::: report_customsql
Maintained by TimTim Hunt, at the OU (Perry building)Mahmoud Kassaei, Anupama Sarjoshi
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:
4359 sites
1k downloads
167 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 quickly set up ad-hoc reports, without having to create a whole new admin report plugin.

Screenshots

Screenshot #0

Contributors

Tim
Tim Hunt (Lead maintainer)
at the OU (Perry building)
Mahmoud Kassaei: Developer
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Tim
    Fri, Sep 8, 2023, 8:42 PM
    The short answer is that there is not an easy way to do this, because databases don't work like that.

    However, there is a way to fake it, which is this.

    Suppose you have variable cohort_ids with a value like 123,456,789. Then you can do

    WHERE CONCAT(',', :cohort_ids, ',') LIKE CONCAT('%,', cohort.id, ',%')

    This is not particularly efficient, but it works.
  • Alfredo Tassano
    Wed, Sep 20, 2023, 11:18 PM
    Hi Tim.

    This plugin is a must be on the box. I wonder to know if is compatible with Moodle 4.1, and 4.2

    Formally, my organization, demands that any software should be compatible to with the platform and products installed (we have Moodle 4.1).

    Sincerely

    Alfredo
  • Csaba Vágvölgyi
    Wed, Sep 20, 2023, 11:23 PM
    We use this awesome plugin in a PHP 8.1 environment on many Moodle 4.1 sites and it works great.
    Csaba
  • Giovanni Mancini
    Mon, Nov 6, 2023, 10:02 PM
    HI
    with 8.1.25 PHP version I have this alert "PHP Deprecated: Function strftime() is deprecated in /var/www/vhosts/elearning.formazionelavoratori.online/httpdocs/report/customsql/locallib.php on line 611"
  • Shirley Gregorczyk
    Wed, Nov 8, 2023, 5:01 AM
    Hello,
    I have been struggling to create the proper SQL statement to create a valid URL.
    I have tried several variations and reached out to the Moodle community.
    The statement below results in a plain text with [site www], course fullname
    CONCAT('%%WWWROOT%%/course/view.php%%c%%id=',c.fullname) AS "Link",
    Can someone please provide me with the proper SQL statement for a course URL?
    Kind Regards
  • Remmington Savoie
    Wed, Nov 8, 2023, 5:39 AM
    Hello Shirley,
    There may be a more succinct way but the following should work. You must create one column 'X' and then a second column with a title like 'X_link_url'. When the report is run it will combine both into one column.
    Here is an example that will print the course title and the hyperlink will take you to the course:

    SELECT
    c.fullname as course_name,
    CONCAT('%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id) as course_name_link_url
    FROM prefix_course c

    Hope this helps.
  • Shirley Gregorczyk
    Wed, Nov 8, 2023, 9:38 PM
    Hello Remmington,
    Thank you!!!!
    While this solution is written in the Notes section of the plugin, my attempts did not correctly write the statement. I over thought the instruction.
    Kind Regards,
    Shirley
  • Niklas Lovefall
    Fri, Nov 10, 2023, 5:20 AM
    Hi,
    I don’t know anything about SQL but desperately need a report that collect data about both course completion and information from two feedback activities.
    Can someone help me out?
    /Niklas
  • Prihantoosa Soepradja
    Tue, Nov 21, 2023, 12:23 PM
    Hi,
    When I've tried a complex query for just 1000 row, plugins works perfectly, but when I increased to 30K, I just got blank row.
    But when I tried a simple query (only 3 field), got 30K perfectly.
    What should I do ?

    Regards,
  • Tim
    Wed, Nov 22, 2023, 2:23 AM
    When you are getting a blank page, try turning on Debugging to see an error message.

    I guess that if the query is too big, then the server will run out of memeory. (But, that is just a guess.)
  • Stan Klapauszak
    Wed, Nov 22, 2023, 3:39 AM
    I'm seeking further documentation on the Ad-Hoc Queries plugin for Moodle. Specifically, it seems to allow for some non-standard SQL functions such as %%FILTER_USER%%, and I've heard rumors one can use statements like "u.id=:userid" to prompt for run-time entry of parameters. Where can I find those capabilities and their syntax documented? The Moodle Docs entry for this plugin doesn't include any of those details, nor does it provides link to them that I can find.
  • Tim
    Wed, Nov 22, 2023, 8:31 PM
    I think %%FILTER_USER%% is a feature of https://moodle.org/plugins/block_configurable_reports, not this plugin.

    "u.id=:userid" works for this report. There is a "More documentation on this plugin" link above. Also, when you are editing a query, there is a lot of information on the screen about what you can do.
  • Fabian Glagovsky
    Tue, Jan 23, 2024, 7:27 PM
    Hi Tim, is this plugin compatible with 4.1, 4.2 and 4.3?
    Will it be?
    Best,
    Fabian
  • Character Academy Academy
    Sat, Apr 20, 2024, 12:21 PM
    Hi Tim,
    Thank you for building this plugin. It really is a great tool and it's easy to use. In an attempt to contribute what little I can to the ongoing development of this project - I found a little update to one of the reports I can share.

    As written on https://docs.moodle.org/403/en/ad-hoc_contributed_reports - There is outdated table name reference in the report "User activity completions with dates"
    Moodle used to have a module called 'assignment' which has since been replaced by 'assign'. Once that table reference is renamed everything else works fine smile

    Apologies if if this isnt the appropriate place to share this, but I couldnt find anywhere else to put this. I hope this helps keep the project fresh, and once again thanks for your effort here. Cheers!
  • Tim
    Mon, Apr 22, 2024, 3:54 PM
    Thanks for thinking of contributing back. docs.moodle.org is a wiki. You can edit it yourself smile
Please login to post comments