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:
4077 sites
1k downloads
154 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
  • Fri, Feb 25, 2022, 9:14 PM
    Hi, is there a way to make a parameter optional? I currently let the user filter by country but when they dont enter sth in the input it gives back no data, instead it should give back the list of all users, independetly from the country. I use the parameter like this WHERE :country = u.country
  • Tim
    Fri, Feb 25, 2022, 10:26 PM
    The simplest work-around for now is to make two queries, one with the parameter and one without.

    in the description for each query, you can link to the other one.
  • Tue, Mar 1, 2022, 9:28 PM
    Hi all,

    I created a scheduled report from ad-hoc data base query and I did not receive the report to my email. I noticed that the record for that report in the database retrieve my userid correctly but has a comma before that, probably that what stop the plugin to send me the email.

    Regards
    Motasem
  • Fri, Apr 29, 2022, 1:07 AM
    Hi, we run daily, weekly and annual course completion reports. This gives us the completions during those time frames for all courses. I am looking for a way to configure the report so that only specific courses appear on the report, not all courses. Is there a way to accomplish this?
  • Tim
    Fri, Apr 29, 2022, 1:12 AM
    I am sure you can do that if you work out the right SQL. In the report, try adding something like

    WHERE course.shortname LIKE '%2022%'

    (to work out the exact details, you will meed to know SQL, and which courses you want the report to include.)
  • Fri, Apr 29, 2022, 1:26 AM
    Thank you, I’ve made a few attempts at the SQL and was able to get one course but not multiple. If I use WHERE course.shortname LIKE '%2022%' can I add multiple courses with a comma between? Or am I limited to one course?
  • Tue, May 10, 2022, 10:47 PM
    Hi Sue, if your course shortnames don't follow a patter that allows you to use %keyword% you could try (depending on the length of the list for manageability) WHERE course.shortname IN('courseName1', 'CourseName2') etc etc listing each course shortname in ' ' with a comma seperator. Alternatively, if your courses are organised in categories, you could create a report for a category or allowing the user running the report to enter a category name as a parameter.
  • Tue, May 24, 2022, 3:21 AM
    Did anyone try to fetch query results via web service/pluginfile.php?
    Can I find any documentation or examples about this feature?
  • Wed, May 25, 2022, 12:38 AM
    Thank you Tim! This is very useful. smile

    My next problem, when I try to call the service:
    {"error":"Sorry, but you do not currently have permissions to do that (Change site configuration).","errorcode":"nopermissions","stacktrace":null,"debuginfo":null,"reproductionlink":null}

    Which permissions ("Webservice function") do I need?
    I have a webservice test user with several webservice functions and some other REST calls was successful.
  • Tim
    Wed, May 25, 2022, 12:40 AM
    When you create a report, you choose one of three capabilities that are required to access the report. You need whichever permission you selected there.
  • Wed, May 25, 2022, 12:52 AM
    Bingo! Thank you again!
    (I thought I need some webservice permission, but it was a report access problem)
  • Thu, Jul 21, 2022, 8:36 PM
    Hi Everybody,
    I created a report with a provided query. When i use this on demand it works fine. When I want to mail the results nothing happens.
    I can see that the CRON task has run
    Ad-hoc database queries: run scheduled reports task \report_customsql\task\run_reports Scheduled 21/07/22, 09:28 0.09 secs cron04 1626851 3 reads 0 writes Success
    but the report did not
    aa This query was last run on woensdag, 20 juli 2022, 16:18 . It took 0.005s to run. Available to Only administrators (moodle/site:config).
    What do I do wrong
    I'm working on a MoodleWorkplace 3.11.6 (build: 20220314)
    But also LMS 3.11.8 (build: 20220711)
  • Tue, Sep 27, 2022, 5:47 PM
    Hi. thanks for this plugin, is very usefully. I would like to know if is possible to schedule the queries and send email to teacher, not only the administrator. Thanks
  • Tim
    Thu, Sep 29, 2022, 8:29 PM
    At the moment, this is basically a tool by admins for admins.

    Ability to share selected queries with teachers would be a great feature, but we don't have any immediate plans to develop it ourselves. If someone else can develop this in a good, we will would be delighted to get a pull request on github.
Please login to post comments