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.  Also remove the “OFF” “EXCHANGE” courses from the results of UW_RS_SCH_HIST_ENRL_SB.

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: 

Open

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 name it “All Data Combined”, by copying and pasting all the information from each tab, then create a pivot table.

How to create a pivot Table

• Select only the data from the All Data Combined worksheet, then go the “Insert” tab along the top of Excel, select pivotTable, ensure “yes to new worksheet” is selected, then just click ok

• In the Pivot Table Fields, as seen below click and drag the options to the boxes except with the caveat noted below

• Drag Enrollment Total (or which ever cap heading was used) to “Values”, it will appear as Sum of Enrollment total

Under the ROWS heading use Acad Group, Acad Org, Subject, Catalog, and Campus. Under columns, add Term, and Session. Under ∑ Values, use Enrollment Total. 

Open

After Pivot Table is created:

• Once the Pivot table is set up you need to stop subtotaling rows – check it off for Career, Acad Group, Acad Org, Subject and Catalog - by right clicking on the cell and unselect subtotal, right click Total Sum of Enrollment Total and Total Sum of Cap Enrl and select remove grand total. Also remove Grand total of last row of the data (bottom of spreadsheet)

• Delete the top two blank rows.

• Freeze the top 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 within 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 worksheet (the small triangle at the top corner of the Excel sheet 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: