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

  • Fri, Nov 23, 2018, 8:25 PM
    Thanks Tim.
  • Thu, Nov 29, 2018, 5:38 AM
    Hello, I want to setup automatic reports to show a list of students and their activity completion for a specific course, from a specific group or cohort. I know you can run a completion report on a course in moodle but I can't see a way to filter by cohort of group therefore I see 1000's of activity completion records. Will this plugin provide me with a solution to generating custom reports? Appreciate the advice. Tim
  • Fri, Nov 30, 2018, 11:24 PM
    Hello. I'm a new user trying to schedule a report to export to a shared drive path/directory. I am receiving an error that it does not exist. Is there any documentation that exist. Also, is there any hope that the hard limit of 5000 on the rows could be raised?
  • Wed, Jan 9, 2019, 1:31 AM
    Has anyone written or used a query to search for a string *anywhere* in Moodle? I just can't figure out how to do it.
  • Wed, Jan 16, 2019, 4:19 AM
    Is there a way to pick the day I want to have an automatic report run? I have reports that I need every wednesday, others that are needed on the 5th and 20th of each month, etc. Currently it looks like I can only pick every day, 1st of week, 1st of month. Running Moodle 3.5 with Ad-hoc 2018080900.
    Please update so we can schedule automatic running of reports but we can pick the different days to run. It is ok if I have to schedule the same report twice to run once on the 5th and the second copy on the 20th (for example).
  • Tim at Lone Pine Koala Sanctuary
    Fri, Jan 18, 2019, 10:44 PM
    We don't particuarlly need that functionality, so we are unlikely to develop it ourselves. However, if you can find someone to develop it, we are always grateful to receive pull requests.
  • Thu, Feb 21, 2019, 7:57 AM
    We've been giving staff access to a system "Report Viewer" role so they can access the ad-hoc reports. Under 3.1, the link showed up under Site Administration for those users. When we upgraded to 3.5, the site admin link is no longer visible for these staff; though the direct url still works for them. How do we make the link to ad-hoc reports available to staff?
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 21, 2019, 3:16 PM
    Gregor, that is not something we changed in this plugin. It is something that changed in Moodle core, or at least in the standard Moodle theme. You will need to raise it elsewhere. I don't know.
  • Thu, Feb 21, 2019, 4:18 PM
    Hi. Apparently this creates UTF8 csv files with the byte order mark, which is not recommended and creates compatibility issues. (I know, also *not* having it apparently does... :/ ). However, I didn't find adding it explicitly in the source code. Any way to remove it from csv output? Thanks.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 21, 2019, 5:06 PM
    I agree with your analysis. There is nothing in the code to write a byte-order mark. It just does fopen (https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L95) then (https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L458) it calls fwrite (https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L455).

    Sorry. I have no idea at the moment.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 21, 2019, 5:08 PM
    Oh! one thing to check: the report writes the files to moodledata/customsql (or something like that) then apache serves the file from there. Can you check the files in moodledata. That would tell us if the mark is being added by moodle writing the file, or by apache serving it.
  • Fri, Mar 1, 2019, 7:22 PM
    Thanks, Tim.
    Yes, the bom was produced elsewhere than moodle in the end.
  • Tim at Lone Pine Koala Sanctuary
    Fri, Mar 1, 2019, 7:24 PM
    Thanks for confirming that.
  • Wed, Mar 27, 2019, 6:18 AM
    Hi Tim, are you accepting contributions to this plugin? We had a need for hourly reports at my institution, so I made a few additions to add a category of reports that are generated every time the CRON task runs. Would this be something you're interested in adding to the master version?
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 27, 2019, 7:03 PM
    Yes, contributions to this plugin are welcome.

    At least, that is what we hope to do. Sometimes I get too busy, and don't have time to review proposed changes as soon as they are permitted. You may have to be patient, in which case I apologies in advice. Still, do get your suggested changes onto github as a pull request.
Please login to post comments