Technical Details

Current Release Version: 2024.1.0

Release Date: 27 Aug 2024 

Report Location: PowerBI / Apps / SPS Enrollment

Security Roles: Enrollment

Requested Features

Current Cycle

Release 2024.1.1 [September 2024]

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

Previous Cycles

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