Grades for all user and for all courses placed horizontally

Grades for all user and for all courses placed horizontally

by John Smith -
Number of replies: 3

Hi Colleagues, I saw many of custom SQL-query reports developed in/for Configurable Reports plugin (and even modified some of them for own purposes) but they always lists courses vertically, e.g. if we want to get all final grades for each course a specific user enrolled then report lists vertically 1) userlogins and then 2) courses for each user. As a result we have a long report with repeating userlogins.

But I never ever saw such report with courses placed horizontally, e.g. like this:

                                                          Course1               Course2             Course3                  All passed

Userlogin1                                     FinalGrade11    FinalGrade12    FInalGrade13                   V

Userlogin2                                     FinalGrade21    FinalGrade22    FInalGrade23                   V

where FinalGrade is max grade received by a user, "All passed" mark placed when FinalGrades for all Courses (Course1,2,3) are greater than threshhold (e.g. 80 out of 100).

Is it possible to create such a report?

Average of ratings: -
In reply to John Smith

Re: Grades for all user and for all courses placed horizontally

by Mathew Gancarz -
Picture of Core developers

The challenge with SQL queries is you have to pre-define the columns, but you can do that and de-duplicate using 'group by' https://www.w3schools.com/sql/sql_groupby.asp
If you have finite number of courses you need to report on and know the ID numbers,  you could build a report. A dynamic one would require something like Excel Pivot tables or another move advanced reporting package available from some Moodle partners.

Below is an example pulling some additional user profile fields together and excluding based on completion data from two courses and grouping together to remove duplicates.

SELECT DISTINCT uenr.userid, u.`firstname`, u.`lastname`, u.`email`, udata.`data` AS "Type", udata2.`data` AS "Profession", udata3.`data` AS "Portal Unique ID", udata4.`data` AS "Phone Num", udata5.data AS "Postal Code",
DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(uenr.`timecreated`),'+00:00','-05:00'), '%Y-%m-%d %H:%i') AS "Created Account On",
DATEDIFF(NOW(), CONVERT_TZ(FROM_UNIXTIME(uenr.`timecreated`),'+00:00','-05:00')) AS "Account age in days",
CONCAT("%%WWWROOT%%/report/outline/user.php%%Q%%id=",uenr.userid,"&course=29&mode=outline") AS "Link to English User Report",
CONCAT("%%WWWROOT%%/report/outline/user.php%%Q%%id=",uenr.userid,"&course=40&mode=outline") AS "Link to French User Report"
FROM prefix_enrol AS enr, prefix_user_enrolments AS uenr
JOIN prefix_user AS u ON u.id = uenr.userid
JOIN prefix_user_info_data as udata ON udata.userid = u.id
JOIN prefix_user_info_field AS uif ON udata.fieldid = uif.id
JOIN prefix_user_info_data AS udata2 ON udata2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON udata2.fieldid = uif2.id
JOIN prefix_user_info_data AS udata3 ON udata3.userid = u.id
JOIN prefix_user_info_field AS uif3 ON udata3.fieldid = uif3.id
JOIN prefix_user_info_data AS udata4 ON udata4.userid = u.id
JOIN prefix_user_info_field AS uif4 ON udata4.fieldid = uif4.id
JOIN prefix_user_info_data AS udata5 ON udata5.userid = u.id
JOIN prefix_user_info_field AS uif5 ON udata5.fieldid = uif5.id
WHERE 
enr.id = uenr.enrolid AND enr.status = uenr.status AND enr.courseid IN (29,40) AND uif.shortname = 'professiontype' AND uif2.shortname = 'Profession' AND uif3.shortname = 'PortalUniqueID' AND uif4.shortname = 'PhoneNum' AND uif5.shortname = 'PostalCode' AND uenr.userid NOT IN (12,15,17,18,19,20,21,25,26,28,29,31,33,34,35,37) 
AND DATEDIFF(NOW(), CONVERT_TZ(FROM_UNIXTIME(uenr.`timecreated`),'+00:00','-05:00'))>6
AND NOT EXISTS 
(SELECT *
FROM prefix_course_modules_completion AS cmc
JOIN prefix_course_modules cm ON (cmc.coursemoduleid = cm.id AND cm.module = 5)
JOIN prefix_modules m ON cm.module = m.id
WHERE cmc.userid = uenr.userid)
GROUP BY uenr.userid
Average of ratings: Useful (1)
In reply to Mathew Gancarz

Re: Grades for all user and for all courses placed horizontally

by John Smith -
Hello Mathew, well, to pre-define columns in my case is not a problem - we have only 15 courses, ids are known thus a static table.
The problem is that your example doesn't work in my environment so I cannot practise the idea with GROUP BY statement you described.
May I ask you to demonstrate the idea on some less complex example which would definitely work in my standard Moodle 3.9 installation?
Ideally - on report where we count a number of attempts made by each user (listed vertically) to pass each course (listed horizontally) smile
Average of ratings: -