Moodle | SQL report example in Configurable Reports plugin

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.