Help with adding a cohort filter

Re: Help with adding a cohort filter

by Gregor McNish -
Number of replies: 0
Picture of Particularly helpful Moodlers
I'm not sure about the configurable reports part of it -- cohorts isn't one of the filters specfied in the documentation, but if you're able to enter the cohort name or id, the sql to select the above, but filtered on cohorts would be
SELECT DISTINCT u.lastname as "Last Name", u.firstname as "First Name", concat('',c.fullname,'') AS Course,
case
when ccom.timecompleted IS NULL then concat('', 'Not Complete','')
when ccom.timecompleted IS NOT NULL then 'Completed'
end as "Completion Status"

FROM {user} AS u
JOIN {course_completions} AS ccom ON u.id = ccom.userid
JOIN {course} AS c ON c.id = ccom.course
join {cohort_members} cm on cm.userid=u.id
join {cohort} co on co.id=cm.cohortid
where co.idnumber= (here is where you put the input variable)
 ORDER BY u.lastname, u.firstname,c.fullname

if you want to filter on cohort name then it would be where co.name= ...
Note that in this case cohort membership is treated independently of the course; if you're using cohort enrolments, then you may want to factor that in, so for a cohort you only see the list of people and courses relevant to that cohort., rather than all courses for people in a particular cohort.
Average of ratings: Useful (1)