Assessment reporting & API Canvas

[ChadWhite]

Hello all, We are new to Canvas and are hoping the community can assist with enhancing the Assessment reporting from within Canvas. We would love to hear how others have developed Assessment reporting dashboards by trainer & Course. Any  suggestion - Admin 

[jerry_nguyen · Accepted solution]

@ChadWhite 

You can run SQL queries on Canvas Data (https://community.canvaslms.com/t5/Admin-Guide/How-do-I-use-the-Canvas-Data-Portal-for-an-account/ta...)

I have designed reports for things like pending grading, overdue assessments, identify students at risk by the number of pending submission and the last submission date, etc.

If you're interested and have access to Canvas Data, I can share my SQL queries for you to get started. 

[jerry_nguyen · Accepted solution]

@ChadWhite 

You can run SQL queries on Canvas Data (https://community.canvaslms.com/t5/Admin-Guide/How-do-I-use-the-Canvas-Data-Portal-for-an-account/ta...)

I have designed reports for things like pending grading, overdue assessments, identify students at risk by the number of pending submission and the last submission date, etc.

If you're interested and have access to Canvas Data, I can share my SQL queries for you to get started. 

[ChadWhite]

Hi Jerry, 

That would be absolutely awesome, to get an insight into the SQL reporting you have already completed would be great.

Many thanks for sharing.

[jerry_nguyen · Accepted solution]

Here are samples of some of the reports I have developed

- Module progression report. This report shows the status of student's module (Whether they've completed all the requirements)

- Student's overall progression. This report shows % of submitted assessments and last time they access they system

- At-risk students report based on the number of overdue assessments vs class end date

- Ungraded assessment list.

[YvetteBender]

Hi

I particularly interested in creating weekly UNGRADED ASSESSMENT reports to manage the trainer assessor workload can you advise how you gained this data 

Look forward to hearing from you 

[jerry_nguyen]

@YvetteBender 

You can discuss with your CSM to gain access to Canvas Data. Basically, there are ways

• Paying for hosted service

If you're not tech-savvy, this will be the easiest way to get the report. Your data will be hosted externally (E.g. on Amazon Redshift data warehouse). From my understanding, Canvas will handle the whole ETL process (Extract, transform, load) data from Canvas to the hosted database. You can then use any open database connectivity (ODBC) analytics tool to generate custom data visualization and reports. You can read more on Pros & Cons of using this service here: https://community.canvaslms.com/t5/Canvas-Data-Users/To-Redshift-or-Not-to-Redshift-that-is-my-quest...

• Self-host (No extra cost from Canvas) 

You can set up a local server to download raw data from Canvas, import it to a database (E.g. Postgres, MySQL) then you can run any SQL queries to generate your custom reports. Send me a PM if you want to go down this path, I can assist you with it.  

[kc800]

Hi @jerry_nguyen 

This is amazing!! Yes, would you please share your SQL queries and a brief walkthrough on how to host locally to import to Postgres?

[jerry_nguyen]

@kc800 

1. Firstly, you will need to set up a Linux machine (E.g. through Virtualbox)
2. Setup local database and create schema https://community.canvaslms.com/t5/Instructor-Discussion/Our-Canvas-data-journey/td-p/87378
3. Use canvasDataCli to download and unpack data (Providing that you already had access to Canvas Data): https://community.canvaslms.com/t5/Canvas-Data-Users/How-to-Use-the-CLI-Data-Tool/ta-p/421486
4. Import data to the database: https://community.canvaslms.com/t5/Canvas-Developers-Group/Managing-Canvas-Data-with-Embulk/ba-p/272...

You can find the query for ungraded list attached below

[YvetteBender]

Thanks Jerry 

Can we please contact you direct? 

Yvette Bender and Chad White 

Forsythes Training 

[jerry_nguyen]

@YvetteBender 

Yes, you can send me a private message with your details.

[DIRWIN45]

Hello, Jerry!

Is there any way you can share the SQL's with me?  I am the Online Academic Counselor at Franciscan University and we just switched to Canvas. Having a report system like this would be amazing. Thank you so much!

[jerry_nguyen]

@DIRWIN45 

You can find the ungraded submissions SQL that I shared on the previous page

[LeisaRobson]

@jerry_nguyen Hi there!  Were you able to customise reporting? I have information that led me to believe customised reporting wasn't an option

[jerry_nguyen]

@LeisaRobson you can run your own report with Canvas Data (which you have to set up outside of Canvas). You can contact your CSM to get access to Canvas Data

[adamwarecx]

@jerry_nguyen - Awesome work legend!

[Amanda_Spofford]

@jerry_nguyen  Could you possibly provide your queries for the overall student module progression and the module progress report? We will be using Canvas in the very near future and a report like this is precisely what I was seeking. We do have Canvas Data, but I'm not very experienced with writing SQL queries. Any help or guidance you can provide would be fantastic. Sorry for adding to an older post, but this is just what I needed! Thanks for your time!!

[jerry_nguyen]

@Amanda_Spofford I have attached the SQL for Module Progression. As for the "student module progression" , I can't share it here as it's quite a complicated SQL combined with a custom Javascript table to display the data, thus you will need a web server to set it up.

[Amanda_Spofford]

Thank you very much!