Hi,
A little new to configurable reports, and Im no sql expert. Thats only two strikes.
Ive been creating many reports since I recently installed the plugin and using some of the many sql examples from moodle docs page. Now I want to add a cohort filter to many of my reports. I found documentation on how to do that, although Im still working on that...
Anyways I also found this previous post here that appears to have the answer, but not the actual sql that solved the problem, and I cant figure out how to incorporate the logic and where clause with this sql. In other words, the sql in that post worked for me (see below), but when talking about working in the logic for cohorts and adding a where clause is not working for me. Can anyone suggest the edit (and/or any additional steps needed) for this sql to add a cohort filter?
SELECT DISTINCT u.lastname as "Last Name", u.firstname as "First Name", concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course,
case
when ccom.timecompleted IS NULL then concat('<a target="_new" href="%%WWWROOT%%/report/completion/index.php?course=',c.id,'">', 'Not Complete','</a>')
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
ORDER BY u.lastname, u.firstname,c.fullname
