Community

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
chofer
Community Coach
Community Coach

Report Listing Instructor Name and Role in a Term

Jump to solution

Is there a report I can run that would list all courses in a given term (in this case, our "Master Term") and also list the instructors who have access to those courses in that term...along with the role they are assigned?  I tried running the "Provisioning" report from Canvas, and the "enrollments.csv" gave me the roles (it even showed our custom "Viewer" role that I needed to see) and the Course IDs, but the User IDs are just each person's ID number.  Is there a way to match these IDs up to actual names?

1 Solution

Accepted Solutions
kblack
New Member

Hi  @chofer ‌ - I might be able to help you with this, based on a similar thing that I've tried on my own campus.  This assumes that you are somewhat comfortable using Excel and, specifically, the VLOOKUP function.  Please forgive me for spelling this out in the detail below, especially if you are comfortable with Excel, since I'll write this in a way that might help others who are not as comfortable with Excel in case they need to do this.

Bear in mind this is NOT going to win me any nominations in the "Programmer's Hall of Fame" or gain me a seat at the "API Wizard's Table," because it uses neither programming nor any API calls.  It is simply using what we admins can use in the CSV files that Canvas gives us.

First of all, instead of running just the "enrollments.csv" provisioning report, run BOTH "enrollments.csv" as well as "users.csv."  The result will be a zip file with both csv files that you will want to, obviously, extract.

Let's look at the enrollments.csv first.  What we will want to do is pare this down considerably by removing some extraneous columns.  What we ultimately want to leave intact, however, is the all-important canvas_user_id column, because that's what we will be using as the basis for our VLOOKUP to follow.  Excel users can just highlight the column headings they want to remove and then right-click and select Delete, as shown below:

Deleting extra columns on enrollments.csv

Basically, I'm paring this csv file down to three columns:  canvas_user_id, role, and section_id.  In our case, the section_ID is enough for me to identify the class in question.  Hopefully it is for you, too, or else you may have to also have a peek at the courses.csv later or run that one, as well.

Save this as an Excel file, just so you don't overwrite the original .csv in case something bad happens later.

Next, let's look at the "users.csv."  This will become our "lookup" table for VLOOKUP to work properly.  We again want to pare this down considerably.  Specifically, I recommend leaving ONLY the all-important canvas_user_id column and the equally important (for you!) sortable_name.  However, there is one final, critical step to take with this specific file.  You must SORT the canvas_user_id column in ascending order.  For non-Excel users, that just means clicking in cell A1 to make it active and clicking the Sort button under the Data tab on a Windows-based PC.  The rest should be self-explanatory through drop-down menus, but the end result will look something like this:

Final users file

Save this file in the Excel format, too but KEEP IT OPEN for the next part.

Now, back to the original enrollments Excel file that we saved earlier.  (Again, the users Excel file should be open, too.)  Put your cursor in row 2 in the first empty cell to the right of the data, which should be cell D2.  Click the function button on the formula bar and do search for the function called VLOOKUP.  (Just type that name in the "Search for function" window and click Go to find it, then click on it.)  

Now for the "fun" part:  doing a VLOOKUP function.  It can be a tad intimidating, but we are just going to fill out the first three windows.  The first, Lookup_value should be populated with cell A2, which is the first canvas_user_id value.  The next one is critical to get just right.  For Table_array, put your cursor in that window and then head to the still-open users file.  The dialog box, still open, should be there with you.  Now, simply highlight the entire columns of A and B in that sheet.  You will see the file name and the column references created automatically for you in that Table_array window.  (Screen capture below.)

Now, head back to the enrollments spreadsheet, and to complete the VLOOKUP formula, simply type in the number 2 for Col_index_num.  That means you want the second column from that table array you specified in the previous window populate the formula result.  That second column is the sortable_name.  If you did this all correctly, the VLOOKUP function window should look something like the one shown below--and where the black arrow is pointing you will see your first name, in quotation marks.  (I blurred mine out, since it's a real name):

VLOOKUP formula

Click OK, and copy that formula a-l-l the way down the column.  (Excel trick:  simply double-click the fill handle at the bottom right of a completed formula, and it should copy the original formula all the way down the column without you having to click-and-drag.)  

Presto!  You now have names showing up in column D, all based on that users file.

So, what the VLOOKUP function is doing is looking up value in A2, taking the table array you specified in the users file, and returning the contents of the second column.  VLOOKUP requires that the table array be in ascending order, so that's why there was the necessary earlier step of putting column A in ascending order in the users file.

To make your life easier, since you want to concentrate just on teachers and not ALL users, FILTER that enrollments Excel file to just show the role of Teacher.  On a Windows-based PC, head to the DATA tab and click Filter.  That should add drop-down arrows for each column heading in row A.  Click the drop-down menu for role and uncheck all of them except for the teacher role, as shown below:

269308_filter command.jpg

That will then remove from view the rows that had students in them.

That should do it...I hope.  I hope this helps a bit, Chris.  Feel free to let me know if something does not make sense or does not work.

View solution in original post

9 Replies
kblack
New Member

Hi  @chofer ‌ - I might be able to help you with this, based on a similar thing that I've tried on my own campus.  This assumes that you are somewhat comfortable using Excel and, specifically, the VLOOKUP function.  Please forgive me for spelling this out in the detail below, especially if you are comfortable with Excel, since I'll write this in a way that might help others who are not as comfortable with Excel in case they need to do this.

Bear in mind this is NOT going to win me any nominations in the "Programmer's Hall of Fame" or gain me a seat at the "API Wizard's Table," because it uses neither programming nor any API calls.  It is simply using what we admins can use in the CSV files that Canvas gives us.

First of all, instead of running just the "enrollments.csv" provisioning report, run BOTH "enrollments.csv" as well as "users.csv."  The result will be a zip file with both csv files that you will want to, obviously, extract.

Let's look at the enrollments.csv first.  What we will want to do is pare this down considerably by removing some extraneous columns.  What we ultimately want to leave intact, however, is the all-important canvas_user_id column, because that's what we will be using as the basis for our VLOOKUP to follow.  Excel users can just highlight the column headings they want to remove and then right-click and select Delete, as shown below:

Deleting extra columns on enrollments.csv

Basically, I'm paring this csv file down to three columns:  canvas_user_id, role, and section_id.  In our case, the section_ID is enough for me to identify the class in question.  Hopefully it is for you, too, or else you may have to also have a peek at the courses.csv later or run that one, as well.

Save this as an Excel file, just so you don't overwrite the original .csv in case something bad happens later.

Next, let's look at the "users.csv."  This will become our "lookup" table for VLOOKUP to work properly.  We again want to pare this down considerably.  Specifically, I recommend leaving ONLY the all-important canvas_user_id column and the equally important (for you!) sortable_name.  However, there is one final, critical step to take with this specific file.  You must SORT the canvas_user_id column in ascending order.  For non-Excel users, that just means clicking in cell A1 to make it active and clicking the Sort button under the Data tab on a Windows-based PC.  The rest should be self-explanatory through drop-down menus, but the end result will look something like this:

Final users file

Save this file in the Excel format, too but KEEP IT OPEN for the next part.

Now, back to the original enrollments Excel file that we saved earlier.  (Again, the users Excel file should be open, too.)  Put your cursor in row 2 in the first empty cell to the right of the data, which should be cell D2.  Click the function button on the formula bar and do search for the function called VLOOKUP.  (Just type that name in the "Search for function" window and click Go to find it, then click on it.)  

Now for the "fun" part:  doing a VLOOKUP function.  It can be a tad intimidating, but we are just going to fill out the first three windows.  The first, Lookup_value should be populated with cell A2, which is the first canvas_user_id value.  The next one is critical to get just right.  For Table_array, put your cursor in that window and then head to the still-open users file.  The dialog box, still open, should be there with you.  Now, simply highlight the entire columns of A and B in that sheet.  You will see the file name and the column references created automatically for you in that Table_array window.  (Screen capture below.)

Now, head back to the enrollments spreadsheet, and to complete the VLOOKUP formula, simply type in the number 2 for Col_index_num.  That means you want the second column from that table array you specified in the previous window populate the formula result.  That second column is the sortable_name.  If you did this all correctly, the VLOOKUP function window should look something like the one shown below--and where the black arrow is pointing you will see your first name, in quotation marks.  (I blurred mine out, since it's a real name):

VLOOKUP formula

Click OK, and copy that formula a-l-l the way down the column.  (Excel trick:  simply double-click the fill handle at the bottom right of a completed formula, and it should copy the original formula all the way down the column without you having to click-and-drag.)  

Presto!  You now have names showing up in column D, all based on that users file.

So, what the VLOOKUP function is doing is looking up value in A2, taking the table array you specified in the users file, and returning the contents of the second column.  VLOOKUP requires that the table array be in ascending order, so that's why there was the necessary earlier step of putting column A in ascending order in the users file.

To make your life easier, since you want to concentrate just on teachers and not ALL users, FILTER that enrollments Excel file to just show the role of Teacher.  On a Windows-based PC, head to the DATA tab and click Filter.  That should add drop-down arrows for each column heading in row A.  Click the drop-down menu for role and uncheck all of them except for the teacher role, as shown below:

269308_filter command.jpg

That will then remove from view the rows that had students in them.

That should do it...I hope.  I hope this helps a bit, Chris.  Feel free to let me know if something does not make sense or does not work.

chofer
Community Coach
Community Coach

Hi  @kblack ...

Thank you for these directions.  I have started to make my way through them, but I've already hit a snag...which you've hinted at in the paragraph below your first image.  I should have maybe given a bit more detail.  I'm running this report in our "Master Term" which only has "master" courses (we do not enroll students in this term).  Our instructors have one of two roles in these "master" courses...either a "Teacher" (so they can edit content) or a custom "Viewer" role that we created (which has less permissions but based on the "Teacher" role type...basically, no editing rights but still able to import content).  When I ran the reports you indicated, the "section_id" column is blank.  (I can still see the course number in the "course_id" field, so that's good.  Maybe I can just copy the values from "course_id" into "section_id" for now so that I can continue on with your directions?)  There are a few reasons for this:

  • We manually create "master" course shells by hand when building a new course for our instructors.  We then manually add instructors using the two roles described above.
  • Because we manually create "master" course shells, these shells are not coming over from our Banner SIS.  So, there is no "section_id" number in our "Master Term" courses like there would be for our Spring/Summer/Fall courses.
  • And, I'm not sure if this matters or not, but we do not use "Sections" in the way that Canvas defines Sections.  Each course number (no matter if it is 103-159-12345 or 103-159-12346) gets its own course shell in Canvas.  We do not have one course shell of 103-159 with two Sections (12345 and 12346) in it, for example.

I'll keep plugging my way through things.  I don't think I've used VLOOKUP before, but I'll give it a shot.  I'm not experienced with API stuff, so I'm thankful that you detailed this in Excel.  I'll keep you posted.  Thanks.

chofer
Community Coach
Community Coach

Hi  @kblack ...

This is super cool!  Thanks again for providing these directions!  The only other thing that would be nice at this point is if it also listed the actual course name (and not just the course number), too.  Would that involve using the "courses.csv" file?  If so, would I do something similar that I did with the "users.csv" file and then add that data in the "enrollments" Excel file in a new column...such as E2?  Which columns would I need from "courses.csv" if that's the case?

Hi Chris -

Dare I suggest that you can actually try running a separate VLOOKUP against yet another provisioning report:  courses.csv?  That csv file has a canvas_course_id column which, in turn, also appears on the enrollments.csv.  You can then return the course_id or any other column from the courses.csv file to the enrollments.csv file that more properly identifies the course name for you.  Again, however, you have to put that course.csv file in ascending order by the canvas_course-id column for the VLOOKUP to work.

After doing so you will then have to move some columns around to, ultimately, get that canvas_user_id field in the enrollments.csv file "ready" for the next VLOOKUP function so that you have a column to put the formula in. 

I might add that the Col_index_num does not have to be "2."  You can return another column number--whatever column you want--to populate with the course name or whatever identifying information you require for the courses from the courses.csv file.  I realize this gets a bit messier the more VLOOKUPs you throw into it. but what you want should be do-able.

We just posted at the same time!  I hate when that happens.  See if my reply above makes sense, Chris.

chofer
Community Coach
Community Coach

That does sound a bit more tricky.  Thanks for all your help with this.

chofer
Community Coach
Community Coach

 @kblack ...

In the words of young Anakin Skywalker...  It's working! - YouTube 

Awesome!  Thanks for letting me know,  @chofer  !  I was just now starting to play around with it further, so I'm glad you got it.  SORTing is important with any lookup file you use.  It doesn't hurt to eyeball the results a bit to ensure everything looks okay.  You likely have a good idea of which courses should go with which instructors.  

This can also be taxing on a PC with having multiple, likely BIG Excel files open.  This may not work well at large institutions.  But I'm happy to help someone like you who is so good with answering so many of our questions in the Community.

chofer
Community Coach
Community Coach

Yes...I've spot-checked a few courses, and things look good for the most part.  There are a few courses where there are instructors listed in the report that no longer have access to the "Master Term" course, so I am not sure why those are showing up on the report.  And, it's not looking at any "pending" invites, either.  That's okay, though.  Maybe as we clean up the enrollments in these "Master Term" courses, we'll take care of the "pending" ones, too, that haven't been accepted in a while.

Thanks again!