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:
4497 sites
1k downloads
165 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
  • Michael Howard
    Thu, 31 Aug 2023, 8:46 PM
    Hi. I am using the 3.9 for Moodle 3.5+ (2020062800) version. I have setup a report using a query and this is working great "on-demand". But when I switch the setting to email me daily, and choose "include results in the body of email", I am not receiving an email. I've confirmed notifications and outgoing email settings are okay. I note that other people as far back as 2021 were having this issue and a bug fix was implemented to resolve this issue. Am I missing something obvious? Are there any troubleshooting steps I can follow? Thank you.
  • Remmington Savoie
    Thu, 7 Sep 2023, 2:06 AM
    Hello. Is it possible to use comma'd lists (like say a list of cohort ids) when setting up parameters (like say :cohort_id) for this plugin? My attempts to do so seem to result in an SQL error.
  • Tim
    Fri, 8 Sep 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, 20 Sep 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, 20 Sep 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, 6 Nov 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, 8 Nov 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, 8 Nov 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, 8 Nov 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, 10 Nov 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, 21 Nov 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, 22 Nov 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, 22 Nov 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, 22 Nov 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, 23 Jan 2024, 7:27 PM
    Hi Tim, is this plugin compatible with 4.1, 4.2 and 4.3?
    Will it be?
    Best,
    Fabian
Please login to post comments