Report of all quiz activities and their grade to pass

Report of all quiz activities and their grade to pass

by Fazzi Associates -
Number of replies: 9

Hi, 
We're looking to make a configurable report to check the passing grades of all our exams. 

It would just be as simple as 

Course Name | Quiz Name |  Grade_to_Pass  |  Maximum Grade.

using the values in Quiz Settings, and Edit Quiz.


Could someone help us with this?  I tried to figure out how to do this with a courses report, but there didn't seem to be applicable. I see there some SQL that uses gradepass, but don't know SQL to implement it.

Many thanks.

Average of ratings: -
In reply to Fazzi Associates

Re: Report of all quiz activities and their grade to pass

by Luis de Vasconcelos -
Maybe overkill for your needs, but try this:

SELECT
    mdl_user.firstname,
    mdl_user.lastname,
    mdl_user.idnumber,
    mdl_grade_items.idnumber AS examcode,
    mdl_grade_items.itemname AS examname,
    DATEADD(s, mdl_grade_grades.timemodified, '19700101 02:00:00:000') AS examdate,
    mdl_grade_items.gradepass AS pointstopass,
    ROUND(mdl_grade_grades.finalgrade, 0) AS pointsobtained,
    ROUND(mdl_grade_grades.finalgrade / mdl_grade_items.grademax * 100, 0) AS finalgradepercent,
    (CASE WHEN ROUND(mdl_grade_grades.finalgrade, 0) >= mdl_grade_items.gradepass THEN 'Passed' ELSE 'Failed' END) AS passorfail,
    mdl_course.fullname AS course
FROM mdl_grade_grades
INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
INNER JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
INNER JOIN mdl_course_categories ON mdl_course.category = mdl_course_categories.id
INNER JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
WHERE (mdl_grade_items.itemname IS NOT NULL)
AND (mdl_grade_items.itemtype = 'mod' OR mdl_grade_items.itemtype = 'manual')
AND (mdl_grade_items.itemmodule = 'quiz' OR mdl_grade_items.itemmodule IS NULL)
AND (mdl_grade_grades.timemodified IS NOT NULL)
ORDER BY mdl_user.lastname, mdl_user.firstname
Average of ratings: -
In reply to Luis de Vasconcelos

Re: Report of all quiz activities and their grade to pass

by Fazzi Associates -
Thanks for your help Luis. 

pasting your code, gives an error "no explicit prefix". Am I doing something wrong?

Thanks,



Average of ratings: -
In reply to Fazzi Associates

Re: Report of all quiz activities and their grade to pass

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
Hi,

You must replace all mdl_ prefix by prefix_ when using SQL queries with configurable report plugin.

HTH,
Séverin
Average of ratings: Useful (2)
In reply to Luis de Vasconcelos

This forum post has been removed

The content of this forum post has been removed and can no longer be accessed.
In reply to Deleted user

Re: Report of all quiz activities and their grade to pass

by Luis de Vasconcelos -
The DATEADD() function is used in SQL Server.  For MySQL try DATE_FORMAT().

Example:
DATE_FORMAT(FROM_UNIXTIME(prefix_table_name.column_name),'%y.%m.%d' ) AS something,

Like this:

SELECT
prefix_user.firstname,
prefix_user.lastname,
prefix_user.idnumber,
prefix_grade_items.idnumber AS examcode,
prefix_grade_items.itemname AS examname,
DATE_FORMAT(FROM_UNIXTIME(prefix_grade_grades.timemodified),'%y.%m.%d' ) AS examdate,
prefix_grade_items.gradepass AS pointstopass,
ROUND(prefix_grade_grades.finalgrade, 0) AS pointsobtained,
ROUND(prefix_grade_grades.finalgrade / prefix_grade_items.grademax * 100, 0) AS finalgradepercent,
(CASE WHEN ROUND(prefix_grade_grades.finalgrade, 0) >= prefix_grade_items.gradepass THEN 'Passed' ELSE 'Failed' END) AS passorfail,
prefix_course.fullname AS course
FROM prefix_grade_grades
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
INNER JOIN prefix_course_categories ON prefix_course.category = prefix_course_categories.id
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
WHERE (prefix_grade_items.itemname IS NOT NULL)
AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual')
AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL)
AND (prefix_grade_grades.timemodified IS NOT NULL)
ORDER BY prefix_user.lastname, prefix_user.firstname
Average of ratings: Useful (1)
In reply to Luis de Vasconcelos

Re: Report of all quiz activities and their grade to pass

by Fazzi Associates -
Hi Luis,
Thanks for your help on this report.

If I only need to see these columns, without the record of quiz attempts - what would that look like? The current report is listing all the thousands of attempts we have in our system.
Course Name | Quiz Name | Grade_to_Pass | Maximum Grade.

many thanks,
Average of ratings: -
In reply to Fazzi Associates

Re: Report of all quiz activities and their grade to pass

by Luis de Vasconcelos -
Well then that's a very different report. Try:

select
    c.fullname as course_name,
    q.name as quiz_name,
    gi.gradepass,
    gi.grademax
from prefix_quiz q
join prefix_course c on q.course = c.id
join prefix_grade_items gi on gi.courseid = c.id
Average of ratings: -
In reply to Luis de Vasconcelos

This forum post has been removed

The content of this forum post has been removed and can no longer be accessed.
In reply to Deleted user

Re: Report of all quiz activities and their grade to pass

by Luis de Vasconcelos -
Don't use %%COURSEID%%. Instead, put in the actual COURSE ID.

WHERE c.id = 123
(replace 123 with your course.id)
Average of ratings: Useful (1)