Quest & Email - Producing historical Data Reports

Producing historical data reports

Go to: Navigation bar – Reporting Tools – Query – Query Viewer (if in scheduling)

• Run the following Query to Excel for the past three like terms: UW_RS_SCH_HIST_ENRL_SB – All three terms in one query (doesn’t matter the order)

Once in Excel, spreadsheet cleanup:

• Delete the top row

• Highlight the new top row and go to data tab to turn on filters

• Delete blank row if there is one (sometimes there is and sometimes there isn’t)

• Select everything by selecting top left corner triangle, double click in between the 2 rows to resize the rows (then it’s all on one page)

• Go to the Location column filter: select the exchange courses only and then delete them (If asked if other Off courses should be removed – no as they are still courses)

• Column and Row order should already come in sorted correctly – doesn’t affect results even if it isn’t

Set up the Pivot Table:

• Create 2 new columns after Catalogue Column, in the top cell of the first column (H1) enter: =CONCATENATE(F1,G1)

• Concatenate the subject and catalog number into one cell and name it “Subject and Catalog Number”

• Then double click the bottom right corner of the top cell in the column and it should fill the column for you, select fill without formatting in the formatting options (icon appears in the bottom right)

• Copy that column and paste (Value only) into other blank column as blank values (123 icon)

• Delete the concatenated column (don’t need the equation column, only the final values)

Create a pivot table

• Select only the data, then go the Insert tab, select pivot table, ensure “yes to new worksheet” is selected, then just click ok

• In the Pivot Tale Fields, as seen below click and drag the options to the boxes except with the exception noted in step C:

• Note that you do not drag over “Values”, it will appear once the Sum of Enrollment and Sum of Cap Total are dragged down

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 - right click 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)

• 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 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

Save to the Teams channel “Scheduling and Exams” under Data Analysis. Send to scheduling staff to check for mistakes.

Once it’s checked send to UTC Staff (University Timetabling Committee) – you can find a list of UTC members on the UTC Teams page, go to manage team and a list should come up.