cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Surveyor II

Reporting question

Is it possible to create a report for a particular Term with the following columns?

Student First Name

Student Last Name

Student Status

Course Name that the Student is enrolled in

Basically, I want something similar to the "Enrollment" report from within the "Provisioning" report section. The problem I am having is that the Enrollment report only shows IDs for the students and courses, not the names, and I would prefer to limit the amount of post-processing in Excel that would be required to match the IDs to the actual names of students and courses.

Thank you!

11 Replies
Highlighted
Community Coach
Community Coach

Greetings! Due to the technical nature of your question, I've shared it with the Canvas Developers‌ group to see if they can help or make any recommendations. 

Kona

Highlighted
Navigator

mas ,

It is possible -- sort of. You'll have to split the sortable_name on a comma to get their first name and last name.


If you have Canvas Data, you can write an SQL query to do this. This data is at least 18-36 hours old before you get it and there is an overhead with getting Canvas Data setup.

Let's say you don't have Canvas Data. That leaves you with the Canvas API. You would need to write a program that would get a list of all of the active courses and then fetch the enrollments for each of those courses. 

You can fit up to 100 enrollments per course in a single API request. However, Canvas is making changes to the way the enrollments API returns data so that you can't make guesses about what the next page is going to be, which means that you'll have to load additional pages of enrollments within a course sequentially. That only affects you if you have courses with more than 100 enrollments in them.

You can also write this using graphQL, but you will definitely need to use pagination and you're not able to filter as much in Canvas as the graphQL spec allows. This will allow you to get all of the enrollments for all of the courses, but you'll definitely have to use pagination and you'll need to filter out the courses and enrollments you don't want since you can't specify just students or just a single term.

In other words, it may be quicker to join them. Excel may not be the most efficient way to do this, though. I use Excel -- a lot -- but I say it's a general purpose spreadsheet designed to do everything and when you try to do everything, you do nothing well.That may be a bit extreme, but the other side of the coin is that there are programs designed to do one thing, but do it very well. Many of the Unix / Linux command line tools are designed this way. As for joining files together, join comes to mind. The fact that the fields are quoted when there's a comma inside of them can be problematic, though.

There are programs used to join CSV files together and it may be easier to get and run one of those. One of those is q - Run SQL directoy on CSV or TSV files. If you have a Debian/Ubuntu-based Linux system available, this is contained in the python-q-text-as-data package. It supplies a command q that allows you to write SQL but operate on CSV files. I had a Linux box available to me and installed q and wrote the script below in less than 5 minutes (it helped that I was already familiar with SQL)

Assuming I've downloaded and extracted a provisioning report, this statement gives the four items you want (note, my provisioning file is from 2015, but it should still work). This is a single line, I just broke it to make it more readable.

q -H -O -d, 
"SELECT u.first_name,u.last_name,e.status,c.long_name
FROM enrollments.csv e
JOIN courses.csv c USING (canvas_course_id)
JOIN users.csv u USING (canvas_user_id)"
‍‍‍‍‍

You could then redirect the output to a file and have just that file. In Linux, you could redirect the output to a custom.csv filename by adding this to the end of the statement: > custom.csv

You really probably want more than just those four fields. Perhaps SIS IDs or Canvas IDs. You can modify the statement as needed.

If you don't have a Linux system available, don't let that stop you. When you look at the installation section on the q website, there is support for OSX and Windows as well. The Windows installer even includes Python itself so it is isolated from the rest of your system.

Excel will do it, but using the right tool will save you a lot of time and frustration.

Highlighted

Hello James,

Maybe my question was too specific. Let me try a simpler question:

Where can I find the report that shows who is enrolled in each class in a term?

0 Kudos
Highlighted

mas 

While your original question made sense to me, I'm almost certain I don't understand the revised, simpler version.

You wrote something that made it sound like you already knew how to get the provisioning reports.

I want something similar to the "Enrollment" report from within the "Provisioning" report section

Maybe someone else had sent you that report and you're wanting to know how to get it yourself?  If so, this lesson from the Canvas Admin Guide explains how to generate a report: How do I view reports for an account? 

The three pieces you need for the original question are enrollments, users, and courses.

If you don't want to use the provisioning report and your information comes from a student information system (SIS), then you can use the SIS Export Report.

0 Kudos
Highlighted

Hi James,

Thank you and yes I know how to run the provisioning report. The issue with the provisioning report is that it shows IDs instead of real names of users and courses. I am gathering from your replies that Canvas does not actually have a basic enrollment report that shows who is enrolled in each class in a term, in human-readable format. Is that correct? If so, I am frankly shocked that this is not included. Every other LMS that I use has this most basic of enrollment reports. Thanks again.

0 Kudos
Highlighted

There is no such report that is as restrictive as what you're asking for. Each person has different values that they need and so Canvas supplies the normalized tables and it's up to you to put them together in the way that you want them. There would be too many reports if each person had their own.

I do believe that there were some feature ideas related to improving reporting, but I haven't seen any movement on them.

Highlighted

Hi James,

As mentioned below, every other LMS I work with includes reports in human readable format. When you say "There would be too many reports if each person had their own." I have to respectfully disagree. Most LMSs these days include a custom report feature. We specify columns and filters and get a report in human readable format. We can save our columns and filters as a new named report so that we can return to it at any time. LMSs have been doing this for years without issues. Canvas can do better in this area.

0 Kudos
Highlighted

That was what I was referring to when I said, "I do believe that there were some feature ideas related to improving reporting, but I haven't seen any movement on them."

The way that reports currently work inside Canvas, they are not customizable on the front end, you need to do processing after downloading them.

0 Kudos
Highlighted
Community Coach
Community Coach

The super-simple answer is that no, there is no direct way on the front-end of Canvas to get a report with the information you are wanting. 

The more complicated response is that yes, you can do this, it will just take some programming/coding - which is what James provided. Do you have anyone available at your school who could help with this? I'll admit I read what James wrote and he lost me about... well the first sentence... which is exactly why I have someone with this level of knowhow help me out when I need various reports/information. 

Kona

0 Kudos
Top Kudoed Authors
Labels