report_customsql
Maintained by Tim Hunt, 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.
41k
8
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.

### Contributors

Mahmoud Kassaei: Developer

• 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
• 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
• 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.

• 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
• 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?
• 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.
• 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!!
• 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
• Fri, Feb 27, 2015, 8:33 PM
Many, many thanks Tim!
I am able now to retrieve the data required.
• 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
• 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!
• 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.
• 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()