Technical Details

Current Release Version: 2025.1.0

Release Date: 10 Mar 2025

Report Location: PowerBI / Apps / SPS Enrollment

Security Roles: Enrollment

Requested Features

Current Cycle

Release 2025.1.1 [April 2025]

Features to Complete

None

Features to Consider

None

Features Ready for Release

SQL Only: exclude the 'REGIK0111','REGIK0202','REGIK0100','REGIK0200' from the TOOK , TRCL and TRCL2 stored procedures
SQL Only: added TRY_CAST to CTD_Offset, CTD

Previous Cycles

Release 2025.1.0 [March 2025]

Added a 'Summer YOY Registration' beta page to the report bookmarks section.

Release 2024.1.1 [September 2024]

Added Core_Req_in_Program, Elective_in_Program , and Selective_in_Program fields to the 'all output' sections

Release 2024.1.0 [May 2023]

Added the required_for_majors filter to every page

Release 2023.1.3 [May 2023]

PROC: a2_em_pbi
Remove BETA graphic from the TRCL Trends (Full) pageQL
SQL: Added the Current_SPS_Enrollment and Current_Non_SPS_Enrollment fields in the a2_em_pbi report
Added the SPS Enrollment, NonSPS Enrollment, and Total Enrollment columns in the Enrollment by Course page in the EM Power Bi report.

Release 2023.1.2 [May 2023]

SQL-ONLY: Add three terms to the window of terms displayed in the report
Remove trend lines from charts (extraneous)
TRCL Trends (Attended)
- Rename the original TRCL Trends page to highlight that the counts are for students that attended courses, or will attend as of the current date (using TOOK)
- All credits are Academic Credits from TOOK
- Any students that net out to zero credits will drop from TOOK, and thus from the counts in this view
TRCL Trends (Full)
- New page to show all TRCL records regardless of attendance
- This page will allow us to track how registrations are tracking YoY, including the churn that comes from student registering and then dropping out for the term before attending
- These counts should not be used to report out expected credits or headcount, and are purely intended to show if students are behaving similarly on any given day or period of time across programs
- New SQL view to isolate the TRCL records (a1_sps_enrollment_trcl)

Release 2023.1.1 [March 2023]

Fix the notation on the TRCL Trends page to correct it to "Add" 3 months to get the proper date
SQL: Updated the procedure to correct duplicate rows in credit trends caused by including Academic Department
SQL: Fixed Online course naming to use new function instead of directly pulling from PGRM table
Removed Academic Department from visuals that referenced the field, and any filters on that field
Minor cosmetic changes to layout on some pages

Release 2023.1.0 [January 2023]

TRCL Trends
- Replace the general date slicer in the banner
- Add comma formatting to Net_Credits field for visuals
- Align the visuals and size them to match in the columns
- Create a CTD_Offset field by subtracting three months from any CTD value so it can appear within the same calendar year in visuals
- Replace the notation at the bottom with an explanation for the Spring offset dates

Release 2022.1.7 [December 2022]

Report Development page line broken on the Production report. - updated to the new page link
Consolidated both a2_em_took_pbi and a2_em_trcl_pbi SQL queries into one a2_em_pbi and updated it to the Power Bi report.
Archived both a2_em_took_pbi and a2_em_trcl_pbi in W:\System Folders\Stored Procedure\mji3\pbi\ and deleted both SPs from the PROD.

Release 2022.1.6 [November 2022]

Changes pending from lost page of documentation

Release 2022.1.5 [August 2022]

Add the Method of Instruction to the underlying procedure (a2_em_took_pbi) and include as a filter on all of the pages that use this dataset.
Sort the 'Student Headcount, Enrollment and Credits' visual by term.

Release 2022.1.4 [August 2022]

In the TRCL dashboard, data is showing under the blank term id
The total terms now show 12 terms
Added billing credits to the reports (split into two columns 1. Billing Credits, 2. Academic Credits)

Release 2022.1.3 [May 2022]

Add an Enrollment column to the By Course page, similar to the column on the By Program page

Should reflect the total count of enrollments, not a distinct headcount
Note: Student Headcount and Enrollment numbers are the same in each row only difference is the total

Release 2022.1.2 [April 2022]

Added Running Total in Student Headcount view and Trendline on the TRCL page
Added Running Total in Credits view and Trendline on the TRCL page
Added Total Credits Drop - Running Total by Term and Trendline
Added Total Credits Add - Running Total by Term and Trendline
Replace the hard-coded CTD field in a2_em_trcl_pbi stored procedure
Recalculate the Headcount running total and Credits Running total
Removed the hardcoded code for CTD in a2_em_trcl_pbi
Created a backup copy of a2_em_trcl_pbi and saved as a5_em_trcl_pbi_bak
Deleted a2_em_trcl_pbi
Saved a new copy a2_em_trcl_pbi from the a9_em_trcl_pbi_tmp_4_8_2022
Delete a9_em_trcl_pbi_tmp_4_8_2022
TRCL Credits Trends page point to execute cereports.dbo.a2_em_took_pbi
Report version saved as v2022.1.2
The report tested in DEV and no issues were found on DEV
Prod report saved as Enrollment Management v2022.1.3 under version folder

Release 2022.1.1 [March 2022]

Reverted to default colors in the filter panes and cards
Use the a2_em_took_pbi table on (1) Program Description and (2) Degree Code Slicers
Added the code to count Student headcount when the Term_Billing_Credits_Count = 0 (SPS sponsored program) in a2_em_trcl_pbi and a2_em_took_pbi
tsta.Billing_Program_Code IN ('AUAAF','AUCSP','AUAUM','AUOS','AUEPIC','SPCSEC','SPJIEF','SPJIEN','SPNYUX','SPJUIG','SPUTS',
'SPSEAF','SPSEJO','SPJUIU','SPYCVX','SSAMGN') -- SPS sponsored programs Term_Billing_Credits_Count=0
Remove the Enrollment column from the By Course tab
Added the Dates table by using the calendarauto DAX, and added Month, Year, Day, and MonthNum columns and Joined the Dates table with the a2_em_trcl_pbi table.

Release 2022.1.0 [December 2021]

Cosmetic updates to bring the report in line with the latest SPS design standard

Set the Banner size to W=1280 H=85 and color = #162A53 on all pages
Set the Columbia SPS Logo to Width = 300 and Height = 85 on all pages

Make a copy of a2_enrollment_management_pbi SP and named it a2_em_took_pbi and changed the source in PBI (execute cereports.dbo.a2_em_took_pbi)
Make a copy of a2_enrollment_management_trcl_pbi SP and named it a2_em_trcl_pbi and changed the source in PBI (execute cereports.dbo.a2_em_trcl_pbi)
Add Term, Program, and the Degree code filters on the banner on all pages
Remove the Slicers from the actual pages and add them to the Filters pane
Create a term_lookup, program_code_lookup, and degree_code_lookup tables in PowerBi to use it in the Banner filter so that it syncs with the other pages
Add the Subterm filter to the ‘TRCL Credits Trends’ page
Add the Academic Department to the TRCL Trends page
Add the School Type (SPS and NonSPS) in a2_em_took_pbi SP
Add the Academic_department_Code in a2_em_took_pbi SP
Add the School Type (SPS and NonSPS) in a2_em_trcl_pbi SP
Add the Degree Code in Enrollment by Program page
Hide all visual filters from all pages
Enable the Visual Headers on all visuals on the 'TRCL Credits Trends' page

Release 2021.1.1 [September 2021]

Add the Academic Department code field in the Enrollment by Subjects/Course dashboard
Add the Academic Department code slicers in all dashboards except the TRCL view
Release 2021.1.0 [July 2021]
Remove unnecessary filters (Class_Offereing_School, Subject_Area_Code, School_Bulletin_Prefix)
Use the Faculty_Code=’SPEC’ to pull the data on both stored procedures
Add the Term field in the SP and in the report filter
Add the Dynamic Term Identifiers - select the current term of registration and the previous nine terms of enrollment
Add the Academic Department code field in the Enrollment by Subjects/Course dashboard and slicers

Release 2020.1.0 [June 2020]

Redesigned the ‘TRCL - Credits Trends’ tab. Added the following additional trends:
(1) Student Headcount
(2) Add, Drop, Net Credits
(3) Credits by Subject
(4) Credits by School
6/25 - Create a2 Procedures
Current procedure: a9_student_lifecycle_course_cu_all_pbi_su2020_v3 change to a2_enrollment_management_pbi
Current procedure: a9_student_lifecycle_course_cu_all_pbi_trcl_su2020_v3 change to a2_enrollment_management_trcl_pbi
Link both procedures to the report and test the data
Test all Tabs for the Clear Filter Slicers
Add a supporting documents link
6/25 - Check all tabs
Enrollment Summary
Credits by Programs and Schools
Credits by Subjects and Courses
Students by Citizenship
TOOK Credits Trends
TRCL Credits Trends
Create Instructions on How to use the ‘Enrollment Management’ report. Update all images
6/25 - Investigate why ‘a2_enrollment_management_trcl_pbi’ execution time is long. Need to reduce the execution time.
Current status:
Executime time: 1:10
# of Rows: 161,418
Created a temp procedure a2_enrollment_management_trcl_pbi_temp and reduced the execution time
Execution time: 28:00
# of Rows: 161,418
Drop a2_enrollment_management_trcl_pb and rename a2_enrollment_management_trcl_pbi_temp to a2_enrollment_management_trcl_pbi
Redesign the Slicers similar to TOOK Credits Trends

Remove underline from the word on slice title

Add the new procedures on the Procedure list and PowerBi List

Browser not supported