Course grades for students in a cohort

Jo James

• September 19, 2024 20:06

I need to generate a report for the high school students enrolled in courses in Moodle. We need to pull the current grade for all courses in which each high school student is enrolled.

I thought that I could use a Moodle Cohort to identify the high school students for each term. I built a cohort named 2024FA CCP Cohort and added students to the cohort. That part is working fine.

What I need to report is all of the courses in which the student is enrolled in and the course level grade (for all the courses in which the student is enrolled) at the time the report is executed.

Has anyone done this? If so, how did you accomplish this?

1

• 

  Jason Rodgers

  • September 20, 2024 14:31

  Would something like this help?

  SELECT MC.fullname, MU.firstname, MU.lastname, MGG.finalgrade/MGG.rawgrademax*100 FROM mdl_grade_grades  MGG
  LEFT JOIN mdl_grade_items MGI ON MGI.id=MGG.itemid
  LEFT JOIN mdl_course MC ON MC.id=MGI.courseid
  LEFT JOIN mdl_cohort_members MCM ON MCM.userid=MGG.userid
  LEFT JOIN mdl_cohort MCO ON MCO.id=MCM.cohortid
  LEFT JOIN mdl_user MU ON MU.id=MGG.userid
  WHERE MCO.name='2024FA CCP Cohort' and MGI.itemtype='course'
  ORDER BY MC.fullname, MU.lastname, MU.firstname

  Just replace the MCO.name whatever the Cohort's name is you are looking for.

  0
• 

  Jo James

  • September 20, 2024 14:35

  Thanks so much for responding.

  It's very close, but is it possible to change the code to show courses in a specific category (including any sub categories)?

  For example, the 2024 Fall category with subcategories for each academic division.

   

  0
• 

  Jason Rodgers

  • September 20, 2024 15:00

  I've added in the first Category name the course it in

  
  SELECT MC.fullname as COURSENAME, MCC.name as CategoryName, MU.firstname, MU.lastname, MGG.finalgrade/MGG.rawgrademax*100 FROM mdl_grade_grades  MGG
  LEFT JOIN mdl_grade_items MGI ON MGI.id=MGG.itemid
  LEFT JOIN mdl_course MC ON MC.id=MGI.courseid
  LEFt JOIN mdl_course_categories MCC ON MCC.id=MC.category
  LEFT JOIN mdl_cohort_members MCM ON MCM.userid=MGG.userid
  LEFT JOIN mdl_cohort MCO ON MCO.id=MCM.cohortid
  LEFT JOIN mdl_user MU ON MU.id=MGG.userid
  WHERE MCO.name='2024FA CCP Cohort' and MGI.itemtype='course'
  ORDER BY MC.fullname, MU.lastname, MU.firstname

  
  If your categories run deep then you could add a where condition with something like 

  MCC.path like '%/4/%' 
  
  In this case the '4' is the the categoryid of the top level.  Remove the / on the tail end if you have course directly in the subcategory.

  0

Please sign in to leave a comment.