I'm running Moodle 3.6. Our reports worked in Moodle 2.2. Now they are not working. It's showing for this sql, but I don't want all the courses listed. I just want the field trip. #1 is the one that is working for all courses. #2 is the one I want to work for just the course Field Trip. What am I doing wrong?
1-All Course Pass Fail for all Users - Works and I see all users
SELECT u.firstname AS 'Name',
u.lastname AS 'Surname',
u.idnumber AS 'Employee ID',
c.fullname AS 'Course',
CAST(ROUND(gg.finalgrade,2) as numeric(8,2)) AS Score,
CASE WHEN gi.itemtype LIKE '%Course%'
THEN CAST(ROUND(gi.grademax,2) as numeric(8,2))
ELSE CAST(ROUND(gg.rawgrademax,2) as numeric(8,2))
END AS Max,
CAST(ROUND(gg.finalgrade / gi.grademax * 100 ,2) as numeric(8,2)) AS Percentage,
case when gg.finalgrade >= gi.gradepass then 'Yes'
else 'No' end AS Pass,
(dateadd(S, gg.timemodified, '1970-01-01 17:00:00')-1) as DateModified
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
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance'
%%FILTER_USERS:u.idnumber%%
ORDER BY Surname , Name , Course ASC
#2- Field Trip Query - No Records Found
SELECT u.firstname AS 'Name',
u.lastname AS 'Surname',
u.idnumber AS 'Employee ID',
c.fullname AS 'Course',
CAST(ROUND(gg.finalgrade,2) as numeric(8,2)) AS Score,
CASE WHEN gi.itemtype LIKE '%Course%'
THEN CAST(ROUND(gi.grademax,2) as numeric(8,2))
ELSE CAST(ROUND(gg.rawgrademax,2) as numeric(8,2))
END AS Max,
CAST(ROUND(gg.finalgrade / gi.grademax * 100 ,2) as numeric(8,2)) AS Percentage,
CASE WHEN gg.finalgrade >= gi.gradepass THEN 'Yes'ELSE 'No'END AS Pass,
(dateadd(S, gg.timemodified, '1970-01-01 17:00:00')-1) as Date
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
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' AND c.id = 2
ORDER BY Pass DESC, Surname , Name , Course ASC