Your Moodle version

Reports: Ad-hoc database queries

report_customsql
Maintained by Tim at Lone Pine Koala Sanctuary Tim 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.
42k
1k
9
Moodle 2.0, 2.1

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

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

Show comments
  • Tim at Lone Pine Koala Sanctuary
    Thu, Sep 25, 2014, 4:13 AM
    The problem is the ?.

    ? is a special character in SQL in Moodle, and so you are not allowed it anywhere, even in string literals.

    The help text in the latest version makes this clear, and given the work-around. You can sort-of read it here: https://github.com/moodleou/moodle-report_customsql/blob/master/lang/en/report_customsql.php#L114
  • Picture of Joseph Spilatore
    Fri, Sep 26, 2014, 9:07 PM
    has anyone tried to incorporate color coding in custom sql module? I am trying to color code a field in one of my queries. The bold is displaying but not p style color, I am guessing that it may not work because of the CSS

    concat('

    Color

    ')


    Complete query

    Select
    c.id As cid,
    Date_Format(From_UnixTime(c.startdate), '%m/%d/%Y') As Start,
    c.shortname As Shortname,
    c.fullname As `Course Name`,
    Count(Distinct u.id) Enrolled,
    c.maxstudents As Max,
    c.maxstudents - Count(Distinct u.id) As Seats_available,
    Format(Count(Distinct u.id) / (c.maxstudents) * 100, 2) As Percentagefull,
    if(Format(Count(Distinct u.id) / (c.maxstudents) * 100, 2) <80 ,'Red', 'Green') Go,
    concat('

    Color

    ')

    From
    prefix_role_assignments ra,
    prefix_user u,
    prefix_course c Inner Join
    prefix_course_categories On c.category = prefix_course_categories.id,
    prefix_context cxt
    Where
    ra.userid = u.id And
    ra.contextid = cxt.id And
    cxt.instanceid = c.id And
    c.fullname Not Like '%cancel%' And
    From_UnixTime(c.startdate) >= CurDate() And
    DateDiff(Date_Add(CurDate(), Interval 30 Day), From_UnixTime(c.startdate)) >=
    4
    Group By
    c.id, prefix_course_categories.name
    Having
    Format(Count(Distinct u.id) / (c.maxstudents) * 100, 2) <= 99
    Order By
    c.startdate,
    Percentagefull
  • Tim at Lone Pine Koala Sanctuary
    Fri, Sep 26, 2014, 9:14 PM
    I think the problem is that you are generating invalid HTML. THe above SQL will output

    Color


    which of course does not work.

    (Note, these comments seem to be unreadable here. The HTML tags were readable in the notification email.)
  • Picture of Blair F.
    Tue, Sep 30, 2014, 12:08 AM
    I've created a query generates a complete report, runs every day and sends me an email. This weekend, I viewed the report on my mobile device and saw "Previously archived reports" (or something similar) at the bottom of the report. This is what's expected, based on the documentation, which states, "However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time." The weird thing is, when I viewed the same report this morning, to look back at what happened over the weekend, there's no list of archived reports at the bottom, anymore! I would like to be able to look back at these, but have no idea how to access them now.

    Any ideas why this would happen, and how I can see these archived reports? I'm baffled.

    Thanks,
    Blair
  • Picture of Blair F.
    Sat, Nov 8, 2014, 12:58 AM
    This has happened to me again. The report ran on Monday and I had Monday off. So, when I went to look at it on Tuesday, there were no results (because the nature of the report is that it identifies an event in the last 24 hours). So, I look at the report and it's empty. Last night, it ran and when I looked at it, the list of archives was there, so I was again able to access the report from Monday.

    I noticed that path to the archived query is in the format /report/customsql/view.php?id=__&timestamp=________.

    I could probably create another report to list the archives, but I'm not sure where to look for them. I checked the report_customsql_queries table and cannot find anything.

    Any tips?
  • Picture of Blair F.
    Sat, Nov 8, 2014, 3:56 AM
    I just realized that I could have the results emailed to me, rather than just the link (duh!). At least that way I can access them any day. smile
  • Picture of Barbara S.
    Fri, Feb 20, 2015, 3:26 PM
    hi!
    @tim: thanks for that great plugin it helps me a lot in the daily routine.

    I am new to SQL and have got a very basic question. Is it possible to retrieve the email of the trainer (Role A) and the email of the manager (role B) for a course in one single record (row) listed behind the course fullname?

    What is working is the following:
    SELECT c.id, c.fullname, u.email AS Role_A

    FROM prefix_course AS c

    JOIN prefix_context AS ctx ON c.id = ctx.instanceid

    JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
    JOIN prefix_user AS u ON u.id = ra.userid

    WHERE ra.roleid =15

    AND ctx.instanceid = c.id
    AND u.deleted=0

    I would like to have another column called Role_B which is looking up u.email WHERE ra.roleid =14.
    Is it generally possible to retrieve 'u.email' twice in the same record? And if yes, how? I would appreciate getting a link where this is explained, I could not even find a hint. Thanks a lot!! smile
  • Tim at Lone Pine Koala Sanctuary
    Fri, Feb 20, 2015, 9:59 PM
    You need to do something like

    SELECT c.fullname, traineruser.lastname AS trainer_name, manageruser.lastname AS manager_name

    FROM prefix_course AS c
    JOIN prefix_context AS ctx ON c.id = ctx.instanceid
    JOIN prefix_role_assignments AS trainerra ON trainerra.contextid = ctx.id
    JOIN prefix_user AS traineruser ON traineruser.id = trainerra.userid
    JOIN prefix_role_assignments AS managerra ON managerra.contextid = ctx.id
    JOIN prefix_user AS manageruser ON manageruser.id = managerra.userid

    WHERE trainerra.roleid = 15
    AND managerra.roleid = 17
    AND ctx.contextlevel = 50
    AND u.deleted=0
  • Picture of Barbara S.
    Fri, Feb 27, 2015, 8:33 PM
    Many, many thanks Tim!
    I am able now to retrieve the data required.
  • Picture of dirk meyer
    Sun, Mar 1, 2015, 12:03 AM
    Good day,

    I am having trouble with this on a 2.7 in that the following query only returns a few courses from a variety of categories. The same query works well for me on a 2.6. I noticed that in the 2.7 I have an extra box at the bottom of the edit query screen labelled 'Select category for this report' I am trying to generate a report of most active courses across all categories. Thank you for your time.

    SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
    FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
    GROUP BY courseId
    ORDER BY hits DESC
  • Picture of Blair F.
    Wed, May 6, 2015, 1:07 AM
    I LOVE this plugin and use it often. I often use named placeholders so that values can be input when the report is run, but I'm wondering if anyone knows of a way of doing what would basically amount to the following:

    1) entering multiple values in the named placeholder (for example, not just course id=1234, but course id=1234,2345,3456
    2) using a wildcard (for example, not just a specific course id, but ALL courses -- yet still giving the user the option of entering a specific course)

    If anyone has any ideas or solutions, I'd really appreciate it! It would save me a lot of time!
  • Tim at Lone Pine Koala Sanctuary
    Wed, May 6, 2015, 1:16 AM
    If you know exactly how many placeholders you want, you can do

    WHERE courseid IN (:id1, :id2, :id3)

    Or, you could do

    WHERE course.shortname LIKE :pattern

    and pattern would need to be somthing like %maths%

    Anything more than that would require a fair amount of development.
  • Picture of Benjamin Lesjak
    Wed, May 6, 2015, 6:06 PM
    Hi.

    We tried to install this plugin to our Moodle version 2.56. We installed the newest version 2.7 for Moodle 2.4+ (2014061800) but we ended with en error.

    Capability "report/customsql:view" was not found! This has to be fixed in code.
    line 389 of /lib/accesslib.php: call to debagging()
    line 1258 of /lib/adminlib.php: call to has_capability()
    line 3408 of /lib/navigationlib.php: call to admin_externalpage->check_access()
    ...

    Did anyone experienced this error? Thanks,

    Ben
  • Tim at Lone Pine Koala Sanctuary
    Wed, May 6, 2015, 6:08 PM
    After adding the plugin, did you got to Site administration -> Notifications to complete the install?
  • Picture of Bruce Nicholls
    Fri, May 15, 2015, 10:22 AM
    Hi Tim, I'm not to good on the SQL stuff, and I need to run a simple report to extract info on single users, for course/subject completion, dates and grades.
    Can you advise the best code for this please?
    Thanks
    Bruce
1 2 3 4 5
Please login to post comments