Question about quiz responses report

Question about quiz responses report

by Jan Montano -
Number of replies: 4

Hi,

I'm looking at the Quiz > Results > Responses report and most of the data that I need to generate a report for is here which is great.

I need to generate a report that contains the responses report data, + I need  the Quiz name, and the course name it belongs to, for all users.
SO basically, a Quiz responses report which contains all quizzes per course per user attempt. Is there a plugin that can generate this?

Another option would be to query it from the database but to do that I need to know which table/s the responses report gets the data from.

Table mdl_quiz_attempts contains the attempts per user but it doesn't tell the responses per question.

Table mdl_question_attempts looks like the one but not sure what the questionusageid is for or what mdl_question_usages even contain.

I'd appreicate it If someone can point me in the right direction.

Thank you in advance.

Average of ratings: -
In reply to Jan Montano

Re: Question about quiz responses report

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

https://docs.moodle.org/dev/Overview_of_the_Moodle_question_engine#Detailed_data_about_an_attempt should help.

Average of ratings: Useful (2)
In reply to Tim Hunt

Re: Question about quiz responses report

by Jan Montano -

Thank you for the link Tim. I'll check it out.

Average of ratings: -
In reply to Tim Hunt

Re: Question about quiz responses report

by Jan Montano -

Hi Tim and others,


With the link that Tim sent and some research, I was able to create this query


SELECT

    quiza.userid,

    usr.username,

    usr.email,

    usr.firstname,

    usr.lastname,

    course.id AS 'Course ID',

    course.fullname AS 'Course Name',

    quiz.id AS 'Quiz ID',

    quiz.name AS 'Quiz Name',

    quiza.attempt,

    qa.slot as 'Question Number',

    q.id as 'Question ID',

    q.questiontext,

    qa.questionsummary,

    qa.rightanswer,

    qa.responsesummary

FROM mdl_quiz_attempts quiza

JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid

JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id

JOIN mdl_question q ON qa.questionid = q.id

JOIN mdl_quiz quiz ON quiza.quiz = quiz.id

JOIN mdl_course course ON quiz.course = course.id

JOIN mdl_user usr on quiza.userid = usr.id

ORDER BY quiza.id


If you look at the attached results, you'll see that there were 4 quiz attempts made for a quiz, with 3 questions each.

How would I know if a user got the answer right or wrong? This is the only data missing from my query.


I can compare rightanswer vs responsesummary but I feel like there should be another field that I can get it from.

I was trying to get my head around mdl_question_attempt_steps but I can't map it properly.


Any help would be greatly appreciated.


Attachment QueryResults2.png
Average of ratings: -
In reply to Tim Hunt

Re: Question about quiz responses report

by Jan Montano -

All good, I figured it out. I was making the query complicated earlier which is why it wasn't working.

Here's the query for anyone interested

SELECT

    quiza.userid,

    usr.username,

    usr.email,

    usr.firstname,

    usr.lastname,

    course.id AS 'Course ID',

    course.fullname AS 'Course Name',

    quiz.id AS 'Quiz ID',

    quiz.name AS 'Quiz Name',

    quiza.attempt,

    qa.slot as 'Question Number',

    q.id as 'Question ID',

    q.questiontext,

    qa.questionsummary,

    qa.rightanswer,

    qa.responsesummary,

    qas.state

FROM mdl_quiz_attempts quiza

JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid

JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id

JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id

JOIN mdl_question q ON qa.questionid = q.id

JOIN mdl_quiz quiz ON quiza.quiz = quiz.id

JOIN mdl_course course ON quiz.course = course.id

JOIN mdl_user usr on quiza.userid = usr.id

WHERE course.id = 2 and (qas.state = 'gradedwrong' or qas.state = 'gradedright')

ORDER BY quiza.id


Average of ratings: Useful (1)