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.
38k
7
Moodle 2.4, 2.5, 2.6, 2.7

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, Jul 31, 2014, 10:56 PM
    Note that typically a teacher is only a teacher within a particular course. These reports are outside courses, at the system-wide level. That is the wrong way round for permission inheritance to work.

    You will need to make a new role "View reports" which give that capability, and which you assign at site-level.

    (Alternatively, you could call the role something more meaningful like "Teaching staff" in case you need to use it for other purposes in the future.)
  • Picture of Chris Hay
    Thu, Jul 31, 2014, 11:55 PM
    Thanks Tim - that's useful information to bear in mind.
  • Picture of Brian Evans
    Wed, Sep 24, 2014, 3:58 AM
    Tim,

    I will add my thanks to everyone else's. This is a fantastic plugin.

    I have one of suggestion: create an option to hide the "back to the list of available queries" link for those not allowed to manage/edit queries. Links to individual queries could then be placed in a URL resource, HTML block, custom menu, etc., without exposing the whole list of queries.

    Thanks again.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Sep 24, 2014, 10:19 PM
    Well, that is just security through obscurity. They could just sit there working through ?id=1, ... ?id=2, ... to access all the reports that they have permission to see.

    A better option might be a way to generate a token for a particular query, then if someone went to the URL ...?id=123&token=... then they would be allowed to see just that query.

    However, at the moment that is just an idea. I don't have time to implement that.
  • Picture of Joseph Spilatore
    Thu, Sep 25, 2014, 2:59 AM
    Greetings

    Continue to receive Error when attempting to include link in query results. Moodle Version 2.4.5 and lastest version of custom sql. Query tested directly through mysql workbench. Any insight is greatly appreciated

    Below is sample.

    Query SQLRequired field
    Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.

    SELECT
    u.id,
    u.firstname,
    concat('',u.firstname,'') AS User

    FROM prefix_cohort AS h
    JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
    JOIN prefix_user AS u ON hm.userid = u.id



    ORDER BY cohortid
  • 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
1 2 3 4 5
Please login to post comments