Specific Query Question

[kevinw]

I'm not sure if this is the right way to report this but please redirect me if it is. My institution just got access to Canvas data and I'm trying to merge the reports in a way so that it give me the short name, the long name of the course, and the total enrollments. 

I'm working with the Canvas data cli and I'm trying to accomplish this via the unpacked files in Excel 2016. 

[jperkins]

Kevin, you may want to check out the https://community.canvaslms.com/community/answers/data space. I know they have some prebuilt example reports (I've seen them but couldn't quickly find a link) as well as detailed instructions on using the CLI. 

[kevinw]

Thanks Jeremy.

I was actually able to figure this out on my own after playing around with it a little more. 

I needed three unpacked files:

1. Enrollment_fact
2. Course_dim
3. User_dim

Then I merged enrollment fact and user dimension based on the user ID. I included the "name". Then I merged this new query with course dimensions based on the course ID, and included the course code. I filtered the rows for the proper enrollment term, removed duplicate users. 

That gave me the basic kind of report I needed. The last step was to group the columns so I could get the count. To accomplish this group I grouped:

1. Course ID
2. Enrollment Term ID
3. Course Account ID
4. Course Code

The final product looks like this