reporting users who have "not attempted" a course

reporting users who have "not attempted" a course

by Stephen Wilson -
Number of replies: 4

Hi everyone, 

I'm very much a noob at SQL and am trying to implement an SQL report though the configurable reports plug in I have located the script below and modified it slightly to give me a little more user information. I now have the report showing those who have passed/ failed or completed all courses but cannot get it to display those who have not attempted a course I have tried various variants of "OR st.value = 'not attempted'" but to no avail when I include the statement it seems to report those users as 'completed'.


SELECT DISTINCT u.firstname FIRST,u.lastname LAST,c.fullname, u.IDNumber,u.department, u.institution AS 'jobtitle', u.city, st.attempt Attempt,st.VALUE STATUS,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") DATE 

FROM prefix_scorm_scoes_track AS st 

JOIN prefix_user AS u ON st.userid=u.id

JOIN prefix_scorm AS sc ON sc.id=st.scormid

JOIN prefix_course AS c ON c.id=sc.course

WHERE 

st.VALUE='completed'

OR st.VALUE='passed'

OR st.VALUE='incomplete' 

OR st.VALUE='failed' 


ORDER BY c.fullname, u.lastname,u.firstname, st.attempt


I am using Moodle v2.9

Any help would be appreciated
Thanks! 

Average of ratings: -
In reply to Stephen Wilson

Re: reporting users who have "not attempted" a course

by Elizabeth Dalton -

Hi Stephen,

The problem with "not attempted" queries is that a regular join will, by default, only include records that are in both sets. Here, you're starting from SCORM data and joining to user data that matches the SCORM data, so you will only see records of users who appear in the SCORM activity set. What you are going to need to do is start from a set of all the users you are interested in, e.g. all those enrolled in a specific Moodle course, and then do an OUTER JOIN to pick up their SCORM status without dropping the users who never attempted the SCORM module.

I hope this helps-- feel free to post again if you have more questions.

Average of ratings: -
In reply to Elizabeth Dalton

Re: reporting users who have "not attempted" a course

by Stephen Wilson -

Thanks for that Elizabeth, basically what I am trying to do is produce a single report collated from the basic reports on each course rather than having to manually run around each of the thirty scorm courses we have pulling off the data individually, is there a way of doing that through "bulk user actions" or some other method in Moodle?

Thanks

Stephen. 


Average of ratings: -
In reply to Stephen Wilson

Re: reporting users who have "not attempted" a course

by Elizabeth Dalton -

I think you will need to use custom SQL, as you suspected. But it should be possible to do. See this example:

https://docs.moodle.org/32/en/ad-hoc_contributed_reports#Enrolled_users.2Cwhich_did_not_login_into_the_Course.2C_even_once_.28Moodle_2.29

This shows how to get students who are enrolled in a course but did not log in (so they have no log record in the course). This is the general idea of what you want to do with the SCORM activities, I think.

Elizabeth

Average of ratings: Useful (1)
In reply to Elizabeth Dalton

Re: reporting users who have "not attempted" a course

by Stephen Wilson -
Thanks for your help Elizabeth, that's a good pointer I'll have a look at that and try and build a query based upon it.


Stephen

Average of ratings: -