SQL Report in ‘Configurable Reports’ plugin provides us an easy UI to write SQL Query and draw results immediately without involving any code complications.
Suppose I want to generate my custom grades report based on some specific activities from course – Let’s say these are PreTest 1, Pretest 2 and Final Test (which can be any type of graded activity type – quiz, assignment etc) which are identified by ‘idnumber’ column from ‘mdl_grade_items’’ table.
Now my target is to find the average of grades from these activities. So I need this kind of output:
This type of report requires virtual columns and tables created on the fly with SQL subqueries. Go through the SQL query below and read the comments to understand how this is achieved:
SELECT Course as "Course Name", User, ROUND(PreTest1,0) AS "PRE TEST 1", ROUND(PreTest2,0) "PRE TEST 2", ROUND(FinalTest,0) AS "FINAL TEST ", ROUND(((PreTest1+PreTest2+FinalTest)/3),0) AS "AVERAGE TOTAL" //“AVERAGE TOTAL” for “Average Grade calculation” generated from the Virtual columns “PreTest1”,”PreTest2”,”FinalTest” FROM ( SELECT fullname AS Course, username AS User, (SELECT gg.finalgrade FROM prefix_grade_grades AS gg JOIN prefix_grade_items AS I WHERE gg.itemid=I.id AND I.courseid=c.id AND I.idnumber="P1" AND gg.userid=u.id) AS "PreTest1", // This is Column alias name “pretest1” for column generated from the Subquery. This query finds the Final Grade Value from grades table for the activity with idumber “P1” from grade_items table. (SELECT gg.finalgrade FROM prefix_grade_grades AS gg JOIN prefix_grade_items AS I WHERE gg.itemid=I.id AND I.courseid=c.id AND I.idnumber="P2" AND gg.userid=u.id ) AS "PreTest2", // This is Column alias name “pretest1” for column generated from the Subquery (SELECT gg.finalgrade FROM prefix_grade_grades AS gg JOIN prefix_grade_items AS I WHERE gg.itemid=I.id AND I.courseid=c.id AND I.idnumber="FT" AND gg.userid=u.id ) AS "FinalTest" // This is Column alias name “finaltest” for column generated from the Subquery. 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 IN(5) ) AS new_TB // This is temporary table generated from the Subquery.