Where to get students calculated grades?

Question

Hello,

We are trying to get student's calculated grades from Canvas course sections. It looks like the Canvas Data 2 datasets do not provide this calculated data, which makes us have to manually calculate the grades and we're concerned our calculations won't always match what faculty and students see in Canvas.

Is there a provided data set that contains calculated grades for students?

If this isn't available in a dataset, are there any other methods we can use to retrieve this data?

Thanks,

Bobby

stimme · Answer

Can you say a bit more about the purpose of the report?

Pete5484 · Answer

It does seem rather difficult to link grades to scores, unless I'm missing somthing! My partial solution is below (but it has limitations)! @Gergely Schmidt is there a better way of doing this (other than putting them in the schema, because that would be appreciated)!? My approach is to start at scores, then enrolments, users, grading_standards, assignment_groups, assignments, then submissions. Also I havent been able to locate any ability to output grades in live events, other than overrides. SELECT c.course_code, c.name AS course_name, c.id as course_id, e.course_section_id, cs.name AS course_section_name, t.name AS term_name, u.id AS user_id, u.sortable_name, u.short_name, cc.path as email, — its more complex than this sc.current_score, sc.current_points, a.points_possible, sc.enrollment_id AS sc_e_id, sc.course_score, c.group_weighting_scheme, sc.override_score, CASE WHEN c.course_code ~* 'TTI P|K P' THEN 'Professional' WHEN c.course_code ~* 'TTI B|K B' THEN 'Bridging' WHEN c.course_code ~* 'TTI A|K A' THEN 'Advanced' ELSE NULL END AS microcredential_level, ( SELECT gvalue->>0 FROM json_array_elements(CAST(cgs.data AS json)) AS gvalue WHERE CASE WHEN sc.override_score IS NOT NULL THEN sc.override_score / 100 WHEN c.group_weighting_scheme IN ('percent') THEN sc.current_score / 100 WHEN c.group_weighting_scheme IS NULL THEN sc.current_score / 100 ELSE 0 END >= (gvalue->>1)::numeric ORDER BY (gvalue->>1)::numeric DESC LIMIT 1 ) AS final_grade, ag.id AS ag_id, ag.name AS ag_name, ag.group_weight AS ag_weight, ag.position, c.group_weighting_scheme, a.id AS assignment_ids, a.title, a.position, a.omit_from_final_grade, a.points_possible, a.grading_standard_id, a.due_at, a.due_at, s.score, s.published_score, s.published_grade, s.attempt, CASE WHEN a.omit_from_final_grade = TRUE THEN 0::INTEGER WHEN sc.course_score = FALSE THEN CASE WHEN c.group_weighting_scheme = 'percent' AND a.points_possible != 0 THEN ROUND((sc.current_score * ag.group_weight / a.points_possible)::numeric, 2) WHEN a.points_possible != 0 THEN ROUND((sc.current_points * ag.group_weight / a.points_possible)::numeric, 2) ELSE 0 END END AS contribution, ( SELECT svalue->>0 FROM jsonb_array_elements(CAST(gs.data AS jsonb)) AS svalue WHERE (s.score/a.points_possible) >= (svalue->>1)::numeric ORDER BY (svalue->>1)::numeric DESC LIMIT 1 ) AS submission_grade, s.graded_at, ((e.last_activity_at AT TIME ZONE 'UTC') AT TIME ZONE 'Australia/Sydney') AS last_activity_Sydney, TO_CHAR(e.total_activity_time * INTERVAL '1 second', 'HH24:MI.SS') AS total_activity_time_hhmmss, … AS module_progress, … AS module_progress_percent, ((p.current_login_at AT TIME ZONE 'UTC') AT TIME ZONE 'Australia/Sydney') AS current_login_Sydney FROM canvas.scores sc JOIN canvas.enrollments e ON e.id = sc.enrollment_id JOIN canvas.course_sections cs ON cs.id = e.course_section_id JOIN canvas.courses c ON e.course_id = c.id JOIN canvas.users u ON u.id = e.user_id JOIN canvas.pseudonyms p ON p.user_id = u.id LEFT JOIN canvas.grading_standards cgs ON cgs.id = c.grading_standard_id LEFT JOIN canvas.assignment_groups ag ON ag.id = sc.assignment_group_id LEFT JOIN canvas.assignments a ON a.assignment_group_id = ag.id LEFT JOIN canvas.grading_standards gs ON gs.id = a.grading_standard_id LEFT JOIN canvas.submissions s ON s.user_id = e.user_id AND s.course_id = e.course_id AND s.assignment_id = a.id JOIN canvas.enrollment_terms t ON t.id = c.enrollment_term_id LEFT JOIN canvas.v_module_progress mc ON mc.e_id = e.id -- LEFT JOIN canvas.communication_channels cc ON u.id = cc.user_id LEFT JOIN canvas.communication_channels cc ON cc.user_id = u.id AND cc.path_type = 'email' -- Include communication_channels table WHERE 1=1 AND c.workflow_state <> 'deleted' AND e.workflow_state <> 'deleted' AND e.type = 'StudentEnrollment' -- Exclude teachers and other non-student enrollments AND c.workflow_state = 'available'

ZubirHusein72981998 · Answer

the score columns are already the percentage score, you can get both the percentage and the points

I don't believe they would be weighted in the scores table, you would need to access that by its assignment_group canvas.assignment_groups via GROUP_WEIGHT column