cancel
Showing results for 
Search instead for 
Did you mean: 
liuz21
Community Participant

Published or Unpublished Course Reports

Jump to solution

How can I get the course list for published courses or unpublished courses separately for a institution?

I am the account admin. I can generate the course or section report. But I cannot tell which courses are published or unpublished from the report.

Any idea? Thanks!

Message was edited by: Chris Hofer

31 Replies
liuz21
Community Participant

Thanks Jordan!!!

I have a new question: how to get a list, which includes all the published courses and the instructors, for a institution?

I am sure that we can get the list via API, though I don't know how to do it in detail.

Is there an easy way to get that list, such as configuring a report? If not, do you mind to tell me how to get the list with API?

brendaa
Community Contributor

This is actually my question also.  I see how to use the Provisioning reports to get the published and unpublished courses, but unfortunately, this does not include faculty information.  We are tracking adoption rate by faculty, not class so the Provisioning reports are nice, but not useful.  It seems like it would be easy enough to include the field for "Teacher" in this report.

jordan
Community Champion

 @liuz21 ​, and  @brendaa 

It sure would be nice to have something on the front end that revealed (very simply) which teachers have published their courses and which have not. By way of explanation... what happens on the back end is not a simple as what we see on the front... Teachers are not inherently connected to a course. Courses exist independently. Users exist independently. Then there's this bridge between the Users and Courses called Enrollments. A user would be enrolled in a course and identified as the teacher for that course.

I'm not going to propose and api solution b/c I'm not savvy enough to back it up Smiley Sad

The following is a valid option but will still require some finagling. Are you ready?!

  1. Run your Provisioning report
    1. Select Users
    2. Select Courses
    3. Select Enrollments
    4. Download report
  2. Unzip and find three .csv files, and open all in Excel
  3. Insert a few columns next to the Course_ID on the Courses.csv (like this)
  4. Then use a VLOOKUP function in one of the cells to grab the Canvas_User_ID, columns from your Enrollments.csv
  5. Then copy this formula to all empty cells in your Courses.csv
  6. Then use a VLOOKUP function to grab the First/Last name of your users, from your Users.csv and link that to the Canvas_user_id in your Courses.csv

This would allow you to see exactly what you want! A list of users are enrolled as teachers for each course. You could then sort the Courses.csv by active/unpublished to see a convenient list of all the teachers who have not published their courses! Smiley Wink

I can't offer additional help on the VLOOKUP (right now) b/c like the API, it's another area where I'm not very savvy. I wonder if  @kona ​, or stefaniesanders knows someone who could offer additional direction here on leveraging a VLOOKUP funtion in excel to apply the process above?

brendaa
Community Contributor

This does sound like a good solution.  I've never used VLOOKUP and I suspect it only works on the Windows version of Excel, but I will investigate!

James
Community Champion

 @brendaa ​,

=VLOOKUP() has been a standard in spreadsheets forever. That "forever" is a link to a 1991 version of a Lotus 1-2-3 manual that had VLOOKUP listed. The first link was to the Excel page on VLOOKUP and at the bottom, it specifically lists Excel for Mac 2011 and 2016. Google Sheets has it. Trust me, it's been around a long time.

I use =VLOOKUP() ... a lot ... for some pretty powerful lookups but have hacked my way around its limitations until I recently (last couple of years) discovered the more powerful MATCH() and INDEX() pairing.

If you do use VLOOKUP(), the value you're keying off of (the one you're trying to match) needs to be in the first column of the range you select. And if you leave off the optional fourth parameter, your keys in that first column need to be sorted ascending AND you better be sure a value is there because it does approximate matching. That also means that if you have users 10, 11, and 13 and you search for user 12, it will give you user 13. NOT what you want. Make sure you set the fourth option to FALSE for an exact match.

VLOOKUP() returns the VALUE that it finds and is a special combination of INDEX() and MATCH() where the key is in the first column.

If, however, you want to be more productive, or the information that you need is in column 1 but the value you want to match off of is in column 3, then VLOOKUP() is not the route you want to go.

Instead, you would do a =MATCH() on column 3, where you specify just column 3 in the data range. It returns the row that matches. Then you can use that with the INDEX() function which pulls the information from a specific row and column. If you need just one piece of information, you could merge them into a single nested function =INDEX(fullrange,MATCH(keyvalue,keycolumn,0),columnofdata). The 0 at the end of MATCH() is to force it into exact mode and make sure you get the one you want to get and not just an approximate answer.

Do an internet search for Excel match index. If the page says "Microsoft" on it, it's probably not the one you want. There are a lot of tutorials out there about using the match/index pair, but Microsoft's explanations (at least the ones I've seen) focus on each one separately, not on the powerful combination of the two. This is Microsoft's contribution to all three.

Deactivated user​,  @kona ​ knows someone who knows a lot about Excel. Smiley Happy

jordan
Community Champion

I should have known! Haha. This is perfect! Thank you  @James ​!

James what do you think about this?

I was thinking it would be really valuable resource if you created a "Document" in the Canvas Admins group and shared your comments from above. Maybe call it something like "View a list of (un)published courses by teacher name" or "View a list of teachers with (un)published courses"

I imagine it would make it even easier for others to replicate if they could watch a quick walkthrough/screencast video to see the process in action.  Here's a provisioning report (including users.csv - courses.csv - enrollments.csv) with safe-to-share data, from my personal Canvas instance. Do you think you could include a short video walking others through the most efficient way to get a list of teachers with (un)published courses?

kona
Community Coach
Community Coach

I would find something like this hugely useful. ​Yes, James could run it for me, but it's nice to know how to do it myself and if he's busy or not around it would be nice to know how to find this information with out tracking him down or bugging him.

James
Community Champion

Deactivated user​,

Here you go (video and all): How to list teachers who have unpublished courses

It turns out there is a better (???) way than VLOOKUP or MATCH/INDEX. Better as in it doesn't require any programming or formulas. Not better as in it is interactive and not meant to be printed. It also uses a new feature of Excel 2013, so it won't work in previous versions.

I personally wouldn't do this in Excel because it's what relational databases are all about. But I took your "most efficient" request to really mean "the easiest way for most people."

liuz21
Community Participant

Thanks James! Sounds great! I will go ahead to try your method.

kona
Community Coach
Community Coach

Let us know how it works!