Quest & Email - Producing the CEM report

This report gets sent out once the Course Selection week has ended for students, it’s sent out the day after that closes.

PCS Populate Demand process: 

Waterloo Student Admin > CEM > Process > Populate Demand Tables 

Run these queries for these terms by going to: Reporting Tools → Query → Query Viewer 

Remove all transfer credit courses (1XX, ect., but not ENGL 108X) After running the queries to excel, you can sort by the catalog number and remove any with an X – Except that ONE ENGL 108X course... which is annoying. 

Change the UW_RS_SCH_HIST_CAP_SB term name to “Current Enrollment Cap” and delete the top row. 

Change the UW_RS_SCH_HIST_PCS_SB term name to “Current PCS enrollment” and delete the top row. 

Change the UW_RS_SCH_HIST_ENRL_SB term name to “Actual Enrollment last Spring” or whatever term you are working on and remove the “Cap Enrl” from it and “Location”, which is what the cap was set to – not the actual number of students enrolled and delete the top row.  

Filter out for only GRD courses in the “Current Enrollment Cap” and “Actual Enrollment last Spring” sheets and remove the GRD courses, as there will be no PCS data for them anyways. 

You can move all the three separate spreadsheets into the same workbook, by selecting and dragging the sheet title: 

And dragging it over to the other workbooks, you can also re-order the sheets in the workbook in the same way, by selecting them and dragging them to whatever order you want. 

Save on the Scheduling and Exams team channel under Data Analysis, then CEM Reports

Ensure that all three sheets have their columns in the same order – otherwise the data will all be wonky. The easiest way is to remove the “Career” column – this should align them. 

Take all data from the three different tabs and combine them into one sheet, then create a pivot table, by selecting all the data from the “All data Combined” sheet, click on “insert” then select “pivot Table”. 

Under the ROWS heading use Acad Group, Acad Org, Subject, Catalog, and Campus. Under columns, add Term, and Session. Under Sum of values, use Cap Total. 

Remove the auto Sums for the Acad Group, Acad Org, and Subject and catalog Number. The grand Total is removed for the columns, and the bottom row. Freeze the top rows: delete the 2 top blank rows. Select the first row under the blue area, select “View” tab, select “Freeze panes”, and then click “Freeze panes”.  

Right click on any cell inside the pivot table and select pivot table options, go to display, click Classic PivotTable layout. After doing this go back and unselect it (makes the view easier to read)  

Select whole pivot table again and click the line between the A&B column to resize   

Send it to the other Scheduling staff to check it for any glaring errors. 

Add it to the UW Scheduling Reps Team Channel: CEM Reports 

Then send out this email: