Hello - I am trying to customize the Learner report by learner with grades report I found here: http://docs.moodle.org/22/en/ad-hoc_contributed_reports#Site-Wide_Grade_Report_with_All_Items
I need to make the report filtered by either section or do the graded item was assigned, but I seem to be unable to find which relationships are necessary to tie all the quizes, assignments, workshops, posts, etc together with either the section name or ID, or the date the item was assigned. Unfortunately the date graded won't do. Here is what I have so far, added graded_items.timemodified but it isn't really what I need.
[code]
SELECT u.id AS 'User_id'
, CONCAT(u.firstname, ' ', u.lastname) AS 'Full Name'
, c.fullname AS 'Course'
, cc.name AS 'Category'
, CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name'
, ROUND(gg.finalgrade,2) AS Score
, ROUND(gg.rawgrademax,2) AS Max
, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage
, DATE_FORMAT(FROM_UNIXTIME(gi.timemodified), '%m-%d-%y') AS 'Time'
FROM mdl_course AS c
JOIN mdl_context AS ctx
ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra
ON ra.contextid = ctx.id
JOIN mdl_user AS u
ON u.id = ra.userid
JOIN mdl_grade_grades AS gg
ON gg.userid = u.id
JOIN mdl_grade_items AS gi
ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc
ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' AND u.id = 115
ORDER BY `Name` ASC
[/code]
Any help is appreciated. Thank you!!