Custom report to show trainers what to mark

[JeremyCallan]

Hey Canvas Community 

I am wanting to create a custom report using Canvas Data that gives me the following attributes:

• Course
• Section
• Student 
• Assignment 
• Needs marking
• Trainer that is require to mark

I am wondering if anyone had examples of the API's to use and code to string them together?

Attached is an example of a report id like to pull from canvas.

Thanks

Jeremy

[jerry_nguyen · Accepted solution]

@JeremyCallan 

There are 2 ways you can get this data

1. Through Canvas Data (up to 48 hours behind)

Canvas Data will allow you to get the data school-wide. The following SQL (Postgres) will list all submissions and trainer that is assigned to the course

SELECT 
   course_dim.name, -- Course Name
   account_dim.name AS School, -- Account Name that the course belong to
   assignment_dim.title AS "Assessment Name", -- Name of the assessment
   user_dim.NAME AS "Student Name", -- Student Name
   ES.workflow_state AS "Course Enrollment Status", -- Student's enrolment status -E.g. active / completed
   pseudonym_dim.sis_user_id AS "Student ID", -- Student SIS ID
   pseudonym_dim.unique_name AS "Email", -- Student Email
   pseudonym_dim.last_request_at AS "Last Login", -- Last time they login
   CASE WHEN submission_dim.grade is NULL THEN 'First Submission' WHEN submission_dim.grade = 'Not Yet Satisfactory' THEN 'ReSubmission' END, -- If no grade -> New submission, if grade exist -> resubmission
   submission_dim.submitted_at AS "Submitted At", -- submission date
   DATE_PART('day', (Now() - submission_dim.submitted_at)) AS "Days since Submitted", -- script to calculate date different
   assignment_dim.due_at AS "Assessment Due Date", -- assessment due date
   DATE_PART('day',(assignment_dim.due_at - submission_dim.submitted_at)) AS "Days since/to Due Date", -- script to calculate date different
   CONCAT('https://youracademy.instructure.com/courses/', course_dim.canvas_id, '/gradebook/speed_grader?assignment_id=', assignment_dim.canvas_id, '&student_id=', user_dim.canvas_id) AS "SpeedGrader Link", -- Create SpeedGrader link
   (SELECT string_agg(DISTINCT User_dim.name, ', ')
FROM (Enrollment_dim INNER JOIN Course_dim as cd ON Enrollment_dim.course_id = cd.id) INNER JOIN User_dim ON Enrollment_dim.user_id = User_dim.id
WHERE Enrollment_dim.type = 'TeacherEnrollment' and cd.name = course_dim.name
GROUP BY cd.name) -- Get the trainer that is assigned to the course
FROM
   enrollment_dim as ES
   INNER JOIN
      (
         pseudonym_dim 
         INNER JOIN
            (
(((course_dim 
               INNER JOIN
                  submission_fact 
                  ON course_dim.id = submission_fact.course_id) 
               INNER JOIN
                  assignment_dim 
                  ON submission_fact.assignment_id = assignment_dim.id) 
               INNER JOIN
                  submission_dim 
                  ON submission_fact.submission_id = submission_dim.id) 
               INNER JOIN
                  user_dim 
                  ON submission_fact.user_id = user_dim.id
            )
            ON pseudonym_dim.user_id = user_dim.id
      )
      ON ( ES.course_id = course_dim.id ) 
      AND 
      (
         ES.user_id = user_dim.id 
      )
	  INNER JOIN account_dim ON course_dim.account_id = account_dim.id 
WHERE
   ES.type = 'StudentEnrollment' 
   AND
 	ES.workflow_state != 'completed' -- only return submission list from active student
    AND
   pseudonym_dim.sis_user_id IS NOT NULL
   AND 
   (
      submission_dim.workflow_state = 'submitted' 
      AND NOT submission_dim.workflow_state = 'deleted'
	  AND NOT submission_dim.workflow_state = 'graded'
   )

2. Using API/GraphQL - real-time data to get "To-Do" list

You can create a portal or an LTI tool where a trainer can request this list. You will need to know some basic programming to process the data returned (E.g. process and filter JSON)

• API - Through To-do endpoint (https://canvas.instructure.com/doc/api/users.html#method.users.todo_items), however, the list is limited by pagination
• GraphQL - I'll post the query here later

[jerry_nguyen · Accepted solution]

@JeremyCallan 

There are 2 ways you can get this data

1. Through Canvas Data (up to 48 hours behind)

Canvas Data will allow you to get the data school-wide. The following SQL (Postgres) will list all submissions and trainer that is assigned to the course

SELECT 
   course_dim.name, -- Course Name
   account_dim.name AS School, -- Account Name that the course belong to
   assignment_dim.title AS "Assessment Name", -- Name of the assessment
   user_dim.NAME AS "Student Name", -- Student Name
   ES.workflow_state AS "Course Enrollment Status", -- Student's enrolment status -E.g. active / completed
   pseudonym_dim.sis_user_id AS "Student ID", -- Student SIS ID
   pseudonym_dim.unique_name AS "Email", -- Student Email
   pseudonym_dim.last_request_at AS "Last Login", -- Last time they login
   CASE WHEN submission_dim.grade is NULL THEN 'First Submission' WHEN submission_dim.grade = 'Not Yet Satisfactory' THEN 'ReSubmission' END, -- If no grade -> New submission, if grade exist -> resubmission
   submission_dim.submitted_at AS "Submitted At", -- submission date
   DATE_PART('day', (Now() - submission_dim.submitted_at)) AS "Days since Submitted", -- script to calculate date different
   assignment_dim.due_at AS "Assessment Due Date", -- assessment due date
   DATE_PART('day',(assignment_dim.due_at - submission_dim.submitted_at)) AS "Days since/to Due Date", -- script to calculate date different
   CONCAT('https://youracademy.instructure.com/courses/', course_dim.canvas_id, '/gradebook/speed_grader?assignment_id=', assignment_dim.canvas_id, '&student_id=', user_dim.canvas_id) AS "SpeedGrader Link", -- Create SpeedGrader link
   (SELECT string_agg(DISTINCT User_dim.name, ', ')
FROM (Enrollment_dim INNER JOIN Course_dim as cd ON Enrollment_dim.course_id = cd.id) INNER JOIN User_dim ON Enrollment_dim.user_id = User_dim.id
WHERE Enrollment_dim.type = 'TeacherEnrollment' and cd.name = course_dim.name
GROUP BY cd.name) -- Get the trainer that is assigned to the course
FROM
   enrollment_dim as ES
   INNER JOIN
      (
         pseudonym_dim 
         INNER JOIN
            (
(((course_dim 
               INNER JOIN
                  submission_fact 
                  ON course_dim.id = submission_fact.course_id) 
               INNER JOIN
                  assignment_dim 
                  ON submission_fact.assignment_id = assignment_dim.id) 
               INNER JOIN
                  submission_dim 
                  ON submission_fact.submission_id = submission_dim.id) 
               INNER JOIN
                  user_dim 
                  ON submission_fact.user_id = user_dim.id
            )
            ON pseudonym_dim.user_id = user_dim.id
      )
      ON ( ES.course_id = course_dim.id ) 
      AND 
      (
         ES.user_id = user_dim.id 
      )
	  INNER JOIN account_dim ON course_dim.account_id = account_dim.id 
WHERE
   ES.type = 'StudentEnrollment' 
   AND
 	ES.workflow_state != 'completed' -- only return submission list from active student
    AND
   pseudonym_dim.sis_user_id IS NOT NULL
   AND 
   (
      submission_dim.workflow_state = 'submitted' 
      AND NOT submission_dim.workflow_state = 'deleted'
	  AND NOT submission_dim.workflow_state = 'graded'
   )

2. Using API/GraphQL - real-time data to get "To-Do" list

You can create a portal or an LTI tool where a trainer can request this list. You will need to know some basic programming to process the data returned (E.g. process and filter JSON)

• API - Through To-do endpoint (https://canvas.instructure.com/doc/api/users.html#method.users.todo_items), however, the list is limited by pagination
• GraphQL - I'll post the query here later

[jeremycallan88]

Hey Jerry

Thanks for your reply, really appreciate you sending the above code through.

I am new to SQL and Canvas date, what is the best program to use to run the script and generate a viewable report?

Cheers

Jeremy

[jerry_nguyen · Accepted solution]

@jeremycallan88 

You can use any SQL client to run the query against your data, personally, I use this one https://navicat.com/en/products. Please note, the SQL above is for PostgreSQL

You will need to have access to Canvas Data (https://community.canvaslms.com/t5/Admin-Guide/What-is-Canvas-Data-Services/ta-p/142), which you can ask your CSM to enable for you. 

If you have (paid) Amazon Redshift then you can access the data directly.

Otherwise, you can download the data either manually or using a program (https://community.canvaslms.com/t5/Canvas-Data-Users/How-to-Use-the-CLI-Data-Tool/ta-p/421486). Then you will need to create a database (E.g. Postgres) with tables correlated to Canvas Data structure (You might try these scripts: https://github.com/neurotech/canvas-data-warehouse or https://github.com/xcesaralejandro/Canvas_data_portal_to_postgres)

Once the database is set up, you can then extract the data and import it into the database (https://community.canvaslms.com/t5/Canvas-Developers-Group/Managing-Canvas-Data-with-Embulk/ba-p/272...)

I set up a small Linux server to run those scripts above, however they should work on any platforms.

You can take a look at some report I built from the data here: https://community.canvaslms.com/t5/Vocational-Ed-Training/Assessment-reporting-amp-API-Canvas/m-p/49...