cancel
Showing results for 
Show  only  | 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

Unfortunately, the Excel in my pc is 2011 and I got a stuck when converting the cells to tables. I also didn't find "relationship" under "Data".

I will try VLOOKUP. Smiley Happy

James
Community Champion

 @liuz21 ​​, this definitely will not work with older versions of Excel, the Data Model that it uses was added in that version. I asked  @kona ​ about it earlier this morning and she said "I think most people have Excel 2013"

Realize that =VLOOKUP will only give you one instructor per course. That may or may not be an issue.

liuz21
Community Participant

Thanks for your reply James.

I tried VLOOKUP and got some data. But the result looks not good. So, I am going to upgrade the Microsoft Office in my PC, and will try your method again.

liuz21
Community Participant

Hi James,

I have updated the Microsoft Office in my PC.

When I was trying to create the relationships between enrollment and users with "canvas_user_id", it said "Both selected columns contain duplicate values. At least one of the columns selected must contain only unique values to create a relationship between the tables."

So I cannot go to the next step.

Any idea? Thanks.

James
Community Champion

Hmmm ... a problem that Jordan's test file didn't reflect real-world issues?

The canvas_user_id is supposed to be unique for each user. It has multiple values within the enrollments table. You might try looking through the users table and see if there is a value repeated. You might also double check that every user has a canvas_user_id and that you didn't accidentally click "user_id" instead of "canvas_user_id". The clicking in the wrong column makes more sense. If there really is a duplicate canvas_user_id, we may have to strip those out first and then report it as a bug.

I just finished a video that should work with previous versions of Excel and only uses the lookup functions, not the Data Model. I was going to have you try it, but you're done with the upgrade. Now that I'm done with it, I kind of like it better than the Power View thing.

It's created, but I still have to upload it to YouTube and wait for processing. I'll share the link after it's done and you might want to give it a try. It was actually less involved and generated a pretty output. I won't have time to get it written up today, but you should be able to follow the video.

James
Community Champion

 @liuz21 ​,

Here's the video I made showing it without using the Data Model in Excel 2013. It has numerous benefits over the first method. It's downside is that it will only return one instructor per course. But it does generate a list that you can then use elsewhere besides your computer screen.

Canvas List of Unpublished Courses & Teachers (alternative)

The earliest it might get written up is tomorrow, but you can probably follow it from the video.

Here are the commands I typed, just in case they're hard to read on the video. You can also copy/paste from here to avoid typing or clicking mistakes. The order you add them in is important (the enrollments table user_row must exist in order to reference it in the courses table)

Enrollments Table

add a user_row column at the end

=MATCH([@[canvas_user_id]],users[canvas_user_id],0)

Courses Table

add a full_name column at the end

=INDEX(users,enrollments[user_row],6)

The 6 is for the full_name. You could use 7 for the login_id/email/netID. You could add two columns and have both.

liuz21
Community Participant

Hi James,

Thanks for your help! I have found "canvas_user_id" is not unique in users, which I thought should be unique. The duplicated users actually refers to the same person, but with different login_id.

I would love to try your another tutorial. Smiley Happy

James
Community Champion

Hmmm ... do you know if this was the result of a merge? Normally it should be unique.

That little glitch may throw out the whole Power View report. Theoretically, you can go into the Power Pivot option and specify the primary keys and use more than one column. In that case, you could make it a combination of canvas_user_id followed by their login_id. I'm not sure if that translates over to Power Views or not. I only discovered Power Views yesterday, I had always use the lookup routines before then.

The lookup routines in the second video don't enforce unique keys, so it should work with the duplicate, but you won't know for sure which one you're going to get -- probably depends on the order the user table is sorted in.

liuz21
Community Participant

I have tried this!

It is fabulous! It is easy and the final report looks beautiful! I haven't checked whether all the data is correct or not, but it looks great!

Thanks James!

James
Community Champion

Amy, I'm glad it worked for you.

I've updated the documentation to reflect the new way. I've also marked it as the recommended approach.

I left the first way in there because it has potential - but also an awful lot of issues.

The updated documentation is still at How to list teachers who have unpublished courses