[ARCHIVED] Assignment report - API or Canvas Data portal?

[j_avneri]

Hi there, 

I'm a complete beginner with Canvas API and Canvas Data Portal - I was wondering if anyone could help me in how I would use either to run a report that would show me the following information for assignments from all courses, if possible with me being able to select the time period to run the report from e.g. from 30 days ago to now or a time range etc. (selecting the time period would help alleviate too much data being returned and crashing the report.) 

• Course title 
• Course name 
• Sub-account name 
• Assignment title 
• Assignment publish status 
• Assignment from date 
• Assignment due date 
• Assignment until date 
• Assignment type 
• Date assignment added to course 
• Assignment group title (key information)

The ‘Assignment group title’ is a key piece of information that would have priority in being returned in this report. 

Many thanks! 

Joe  

[robotcars]

Not every assignment will have an assignment_group, at least not logically in CD. So that's a LEFT JOIN. I mention this because, for any assignment without an assignment group, you won't see the title. The rest depends on your use of Canvas, but I think we can get you what you're looking for. 

Depending on your sub account structure, you may have to look up multiple sub account names to get the right one. For instance, account name of most of our courses is just the department name (math, science, social studies), above that is Live SIS Courses, then School Name.

Time range and date conditions are mostly DB specific, but I've provided some examples. My favorite way to limit data for right now reporting, is to start by attaching enrollment_term_dim, and immediately selecting the term, before any conditions. This keeps my queries from scanning 6 years worth of Canvas. Using the Terms page of Canvas, you can use SIS_ID without tracking down the other ID's. https://x.instructure.com/accounts/1/terms 

Let me know if you have any questions.

SELECT 
  course_dim.name AS course_name
  , course_dim.code As short_name
  , account_dim.name
  , account_dim.parent_account
  , account_dim.grandparent_account
  , assignment_group_dim.name AS assignment_group
  , assignment_dim.title
  , assignment_dim.submission_types
  , assignment_dim.grading_type
  , assignment_dim.workflow_state
  , assignment_dim.created_at
  , assignment_dim.updated_at
  , assignment_dim.due_at
  , assignment_dim.unlock_at
  , assignment_dim.lock_at
FROM assignment_dim
  LEFT JOIN assignment_group_dim ON assignment_group_dim.id = assignment_dim.assignment_group_id
  LEFT JOIN assignment_group_fact ON assignment_group_fact.assignment_group_id = assignment_dim.assignment_group_id
  JOIN course_dim ON course_dim.id = assignment_dim.course_id
  JOIN account_dim ON account_dim.id = course_dim.account_id
  JOIN enrollment_term_dim ON enrollment_term_dim.id = course_dim.enrollment_term_id
WHERE enrollment_term_dim.sis_source_id = 'T1920SPR' -- example id
  -- AND assignment_dim.created_at >= GETDATE()-30
  -- AND assignment_dim.created_at >= '2020-03-15'
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍