Activity completion including COHORT

Activity completion including COHORT

by Jason Palmer -
Number of replies: 4

Hi all,
I work for a small private hospital that has approx. 750 staff.
I've inherited a set up which seems to have been delivered based on splitting all the nursing staff into various cohorts. These cohorts don't tend to change each year etc. - they're a proxy for department names

I need to be able to deliver reporting that shows activity completion for our ORG WIDE compliance learnings.

The department field has not been used consistently so I can't filter by this.

What would be the SQL code I could dump into our system that would give me the same data as a standard activity report but INCLUDING the cohort field. ideally, the report would separate each cohort into a different tab on a workbook, but failing that, I can run some vba across the report.




Average of ratings: -
In reply to Jason Palmer

Re: Activity completion including COHORT

by Randy Thornton -
Picture of Documentation writers

You can take a look at the Cohorts queries on the Ad-hoc contributed reports page. All the logic you need to do that should be there.
Average of ratings: -
In reply to Randy Thornton

Re: Activity completion including COHORT

by Jason Palmer -
Hey Randy,
Thanks for your reply. I'm new to all this, however, I have viewed these pages previously - the challenge I have is that I don't know how to successfully "chain" pieces of SQL code together.
in my really junior kind of way, I've picked two reports and tried to combine them - and this is not working.

If I was to find an Activity Completion report, and add this piece of SQL to it, should it work?

ORDER BY u.username
SELECT u.firstname, u.lastname, h.idnumber, h.name
FROM prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_user AS u ON hm.userid = u.id
ORDER BY u.firstname

here's a report I found on Activity completion - it seems like it has way more info than i need, however,

SELECT
u.username As 'User',
c.shortname AS 'Course',
m.name AS Activitytype,
CASE
WHEN m.name = 'assign' THEN (SELECT name FROM prefix_assign WHERE id = cm.instance)
WHEN m.name = 'assignment' THEN (SELECT name FROM prefix_assignment WHERE id = cm.instance)
WHEN m.name = 'book' THEN (SELECT name FROM prefix_book WHERE id = cm.instance)
WHEN m.name = 'chat' THEN (SELECT name FROM prefix_chat WHERE id = cm.instance)
WHEN m.name = 'choice' THEN (SELECT name FROM prefix_choice WHERE id = cm.instance)
WHEN m.name = 'data' THEN (SELECT name FROM prefix_data WHERE id = cm.instance)
WHEN m.name = 'feedback' THEN (SELECT name FROM prefix_feedback WHERE id = cm.instance)
WHEN m.name = 'folder' THEN (SELECT name FROM prefix_folder WHERE id = cm.instance)
WHEN m.name = 'forum' THEN (SELECT name FROM prefix_forum WHERE id = cm.instance)
WHEN m.name = 'glossary' THEN (SELECT name FROM prefix_glossary WHERE id = cm.instance)
WHEN m.name = 'h5pactivity' THEN (SELECT name FROM prefix_h5pactivity WHERE id = cm.instance)
WHEN m.name = 'imscp' THEN (SELECT name FROM prefix_imscp WHERE id = cm.instance)
WHEN m.name = 'label' THEN (SELECT name FROM prefix_label WHERE id = cm.instance)
WHEN m.name = 'lesson' THEN (SELECT name FROM prefix_lesson WHERE id = cm.instance)
WHEN m.name = 'lti' THEN (SELECT name FROM prefix_lti WHERE id = cm.instance)
WHEN m.name = 'page' THEN (SELECT name FROM prefix_page WHERE id = cm.instance)
WHEN m.name = 'quiz' THEN (SELECT name FROM prefix_quiz WHERE id = cm.instance)
WHEN m.name = 'resource' THEN (SELECT name FROM prefix_resource WHERE id = cm.instance)
WHEN m.name = 'scorm' THEN (SELECT name FROM prefix_scorm WHERE id = cm.instance)
WHEN m.name = 'survey' THEN (SELECT name FROM prefix_survey WHERE id = cm.instance)
WHEN m.name = 'url' THEN (SELECT name FROM prefix_url WHERE id = cm.instance)
WHEN m.name = 'wiki' THEN (SELECT name FROM prefix_wiki WHERE id = cm.instance)
WHEN m.name = 'workshop' THEN (SELECT name FROM prefix_workshop WHERE id = cm.instance)
ELSE "Other activity"
END AS Activityname,
# cm.section AS Coursesection,
CASE
WHEN cm.completion = 0 THEN '0 None'
WHEN cm.completion = 1 THEN '1 Self'
WHEN cm.completion = 2 THEN '2 Auto'
END AS Activtycompletiontype,
CASE
WHEN cmc.completionstate = 0 THEN 'In Progress'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
ELSE 'Unknown'
END AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When'
FROM prefix_course_modules_completion cmc
JOIN prefix_user u ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_course c ON cm.course = c.id
# skip the predefined admin and guest user
WHERE u.id > 2
# config reports filters
%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:m.name:~%%
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%


I tried combining this, but got errors.....


As you can tell, this is my first foray into SQL - I suspect it's going to be a bumpy ride smile
Many thanks,
Jason
Average of ratings: -
In reply to Jason Palmer

Re: Activity completion including COHORT

by Randy Thornton -
Picture of Documentation writers
I would suggest that you step back and first do something basic then.

- Get a handle on the basics of query syntax from some of the learning sites, like https://www.w3schools.com/mysql/ and https://www.mysqltutorial.org/. You only need to be sure of the basics first (like how SELECT, JOIN, and WHERE work) to do about many if not most of the queries on that page.

- Start with a basic query and add to it: even experienced users do it this way rather than try to cram two complex queries together. As John Gall says, "All complex systems that work can be shown to come from simpler systems that work."

- Study some queries that are similar to what you need if not exact and get those running first: do those above queries on completion and the ones on groups run and work on your site? If not, then combining won't either.
Average of ratings: -
In reply to Jason Palmer

Re: Activity completion including COHORT

by Thomas Stanley-Jones -
Have you thought about using the Cohort info to UPDATE the Department field? This might simplify reporting on the Department field.

I am not a master at SQL, but I have some experience.  Randy's method of digging in is a good one.  I've found most of Moodle's standard reports are highly complex and like you say, include more than necessary.  Do you have access to the database?  I don't, so I use the plugin Adminer to view what all the tables look like and what data to expect.  Helps me understand what initially looks like a big mess, lol.  

Understanding all these Adhoq queries and why they are built the way they are is key as well.  Otherwise you'll never know what can be cut out.  Each site and need is often quite different.
Average of ratings: -