Ad-hoc database queries

Reports ::: report_customsql
Maintained by Tim at Lone Pine Koala SanctuaryTim 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:
3836 sites
995 downloads
148 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
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them

Comments RSS

Comments

  • Tim at Lone Pine Koala Sanctuary
    Thu, Jan 19, 2017, 7:54 PM
    You mean for Moodle 3.2? I am afraid the answer is "when I get around to testing it". I am sure it works, but I don't like to mark it as compatible until I have acutally tested.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Jan 19, 2017, 7:55 PM
    If you are able to test it yourself (download the zip for 3.1, and install it manually), then let me know. That would be very helpful. Thanks.
  • Sat, Feb 11, 2017, 6:16 PM
    Hi Tim! We will do it and we will back to you. Thanks smile
  • Panthers fan
    Thu, Feb 23, 2017, 12:44 AM
    Thanks for a great plugin (use it ALL the time). Quick question - any way to tell when a report was last edited (changed)? Thanks again.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 23, 2017, 1:18 AM
    We don't track timemodified in the report_customsql_queries database table. That is probably bad practice. If anyone wants to code that addition, I would happily merge a pull request. I'm afraid I don't have time to do it myself just now.
  • Thu, Mar 16, 2017, 10:34 AM
    Hi everyone,
    Is anyone who knows how I can get the students results for all courses?
    I was wondering if I could create a report where I can see the students results for all courses the student is enrolled.

    The student has the OVERVIEW REPORT, where they can access the results of all courses they are enrolled.
    However, the teacher does not have this option to see the results of all courses the student is enrolled.
    Mostly cases, the student is enrolled in the same course as their trainer.

    If someone could help me with that I would be eternally grateful.

    Thank

  • Sat, Apr 1, 2017, 6:03 PM
    Hi, how to make a visible border table in email? The output table is without bordered lines.
  • Thu, Apr 6, 2017, 8:44 PM
    Dear Support Team,
    We can use sql by use Ad-hoc database queries plugin , but after download the result to csv file (refer to the link https://drive.google.com/file/d/0BxriXuT0xeaFRDVoamZLUGhWMDA/view?usp=sharing) , the file content show wrong language as shown as the link: https://drive.google.com/file/d/0BxriXuT0xeaFM1pUbk5naFNkZFE/view?usp=sharing
    How can we adjust the question, and thanks a lot.
  • Sat, May 13, 2017, 4:43 AM
    can i export .CSV without headers ?
  • Sat, May 13, 2017, 4:44 AM
    can i export .CSV file without double quotation on the strings ?
  • Tim at Lone Pine Koala Sanctuary
    Tue, May 16, 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.
  • Wed, May 17, 2017, 4:59 AM
    Agree. Post-processing the csv or changing the report code is not difficult.
  • Panthers fan
    Thu, May 25, 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, May 25, 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.
  • Fri, May 26, 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
Please login to post comments