Canvas Data: NULL grades that don't count towards final grade

[hbarkes]

We are attempting to count assignments that have null grades, but we do not want to count assignments that are not supposed to count towards the final grade (We want to ignore those assignments for our custom report/query). Can someone help us determine if there is a flag for “do not count towards final grade”. If so, where in the data that might reside?

We truly appreciate any and all suggestions. 

[robotcars]

Hi  @hbarkes ,

What tables are you using (or not using) in the report?

Submission_dim is populated with rows when the student gets visibility to the assignment. Meaning that, it's possible to have rows where the student never took action. Here's a chart I made recently to identify removable rows (on import). We have 10M rows the student never saw and never touched. 10M rows is 1/3 of our submission_dim.

Most schools would never see this to this degree... we offer district developed content for full courses... but blended teachers pick and choose what modules/assignments to use... after the student already got visibility.

assignment_dim has the following:

workflow_state Current workflow state of the assignment. Possible values are unpublished, published and deleted

grading_type Describes how the assignment will be graded (gpa_scale, pass_fail, percent, points, not_graded, letter_grade)

submission_dim has the following:

workflow_state Workflow state for submission lifetime values. Possible values are 'graded', 'pending_review', 'submitted' and 'unsubmitted'.

grade_state Denotes the current state of the grade. Possible values are 'auto_graded', 'human_graded' and 'not_graded'.

published_grade Valid only for a graded submission. The values are strings that reflect the grading type used. For example, a scoring method of 'points' will show '4' if given a '4' out of '5', and a scoring method of 'letter grade' will show 'B' for the same score (assuming a grading scale where 80-90% is a 'B'). Defaults to 'NULL'.

try the following:

assignment_dim.workflow_state = published

assignment_dim.grading_type != not_graded

submission_dim.workflow_state IN (graded, submitted)

If you filter these out and still have NULL instead of 0 and doing math, you might have to setup a null replacement depending on the sql/tools/reporting you're using.

[James]

carroll-ccsd,

Excellent and insightful response, as normal. I'm not sure it answered the question, though. Maybe I'm reading the question wrong, but I read this as asking about the assignments that have been marked as "do not count this assignment towards the final grade" and I don't see how it addresses that.

That's a separate field available in the API that's not available in Canvas Data. It is available in the API as omit_from_final_grade when you fetch an assignment (or list of assignments).

I ran a query against SQL data to list the fields you mentioned (the 20738248) is the assignment with that box checked.

SELECT ad.workflow_state AS ad_workflow_state, 
  ad.grading_type, 
  sd.workflow_state AS sd_workflow_state, 
  sd.grade_state, 
  sd.published_grade
FROM assignment_dim ad
JOIN submission_dim sd ON (sd.assignment_id = ad.id)
WHERE ad.canvas_id = 20738248;
‍‍‍‍‍‍‍‍

Here's what I get for that query.

None of those should be counted, though, because the assignment is marked as not counting towards the final.

Here are a couple of previous questions about the same issue.

• Assignment Counts Towards Final Grade 
• Where is the flag that marks an assignment as excluded from the final grade? 

[robotcars]

 @James ,

I'm not positive I understood the question either. TBH, since I don't teach and I rarely get to use Canvas like most, or try to compare the API to CD, I didn't even see flag for “do not count towards final grade” in the OP as something from the UI/API, but as a field/value  @hbarkes  wanted to hunt down. I read the question as trying to remove rows that will not show up in the gradebook or to include only the assignments that are being counted toward the students grade. Which was the goal of my first reply.

In addition to omit_from_final_grade, in his thread  @palvara2  mentions other scenarios where the assignment might not be counted, or need to be counted right now.

This query, returns assignment submissions with the conditions in my previous post.

SELECT
	ad.workflow_state AS ad_workflow_state, 
	ad.grading_type, 
	sd.workflow_state AS sd_workflow_state, 
	sd.grade_state, 
	sd.published_grade
FROM submission_dim sd
	JOIN assignment_dim ad ON ad.id = sd.assignment_id
	JOIN course_dim cd ON cd.id = ad.course_id
WHERE cd.canvas_id = 1
	AND ad.workflow_state = 'published'
	AND ad.grading_type != 'not_graded'
	AND sd.workflow_state IN ('graded', 'submitted')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This query, returns assignments that are not published and are not graded.

SELECT
	ad.workflow_state AS ad_workflow_state, 
	ad.grading_type, 
	sd.workflow_state AS sd_workflow_state, 
	sd.grade_state, 
	sd.published_grade
FROM submission_dim sd
	JOIN assignment_dim ad ON ad.id = sd.assignment_id
	JOIN course_dim cd ON cd.id = ad.course_id
WHERE cd.canvas_id = 1
	AND ad.workflow_state != 'published'
	AND ad.grading_type = 'not_graded'
	AND sd.workflow_state NOT IN ('graded', 'submitted')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The assignment is not available.

It's not in the gradebook.

This query, returns assignments that are not published anymore, may not be graded, but have been submitted.

SELECT DISTINCT
	ad.workflow_state AS ad_workflow_state, 
	ad.grading_type, 
	sd.workflow_state AS sd_workflow_state, 
	sd.grade_state, 
	sd.published_grade
FROM submission_dim sd
	JOIN assignment_dim ad ON ad.id = sd.assignment_id
	JOIN course_dim cd ON cd.id = ad.course_id
WHERE 1=1
	AND ad.workflow_state != 'published'
	AND ad.grading_type != 'not_graded'
	AND sd.workflow_state = 'submitted'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Finally, if we care about NULLs. (might require case on grading_type)

,ISNULL(sd.published_grade, 0) null_to_zero‍‍‍‍‍‍‍‍‍‍

Still not sure whether it satisfies the question.

However, now my question would be, is it possible for assignments marked with omit_from_final_grade to be missed with these conditions, and are there any other conditions we need to look for? That I can't do at the moment, because I'd have to find or make a test case, and I think it's bed time.

Personal victory here, our CD import starts at 8pm. I started writing this around 8:20 or so. At one point I wrote a bad query, but thought my table had been truncated, but that's not my workflow anymore. They are imported, and then the existing table is replaced with the new dump. So I was able to do this during import!!!

[James]

carroll-ccsd,

We'll take personal victories when they present themselves. My current staging approach gives me about 2 seconds with every update when the system is unavailable, but during those two seconds there's a possible race condition where one table might reflect the new data and another one the old data. I can live with that since I'm the only person using it

The original post now reads (emphasis mine).

We are attempting to count assignments that have null grades, but we do not want to count assignments that are not supposed to count towards the final grade (We want to ignore those assignments for our custom report/query). Can someone help us determine if there is a flag for “do not count towards final grade”. If so, where in the data that might reside?

I think the part I bolded was added after I wrote my response yesterday. I don't remember it being there when I read the original question, I was going off the part in italics. If it had been there, I wouldn't have been insecure about my reading the question correctly. There was a "mention deleted" notification last for a while where someone had thrown my name out in a post and then deleted it. That sometimes happens if someone edits the post before I get to it as well. It's more likely in this case that  @hbarkes  wrote a comment and then decided to go back and clarify the original question so people would see it there rather than having to read through the comment.

Omit_from_final_grade isn't in Canvas Data. It would be in assignment_(dim|fact) if it were since it applies to the entire assignment and not individual submissions. There's nothing different otherwise about the assignment that would allow you to ascertain from CD that it was not supposed to be counted towards the final. It's a regular submission, there's a score for it, etc.

This seems to be another one of those instances that you and I have discussed before where people want everything in Canvas Data and that just isn't the case. You have the REST API, Canvas Data, Canvas Live Events, and now GraphQL (although it can be accessed through the REST API). For the adventurous, I would even add undocumented internal calls to that list and I'm probably still leaving something out -- not enough sleep last night. The particular piece of information that you want may be available all, several, one, or none of those.

[hbarkes]

Thank you both for all the details you have provided. I'm actually the LMS Admin and have forwarded this along to our DBA. The original post was not edited, we were specifically looking for the “do not count towards final grade” option. You both provided some great insight to the data that I hope we can leverage. Thank you so much for your quick replies. I might respond back if our DBA has any additional questions.
Thank you!

[robotcars]

 @James  assessment is correct.

Omit_from_final_grade isn't in Canvas Data. It would be in assignment_(dim|fact) if it were since it applies to the entire assignment and not individual submissions. There's nothing different otherwise about the assignment that would allow you to ascertain from CD that it was not supposed to be counted towards the final. It's a regular submission, there's a score for it, etc.

After some additional testing and asking the engineers, we cannot account for this in CD.