Development (HE)
- Mohammed
- Bryon Vaughn
Technical Details
Current Release Version: 2023.1.0
Release Date:
Report Location: PowerBi / Apps / SPS Enrollment
Security Roles: Security Roles: Leadership, Admissions, and Enrollment
Requested Features
Current Cycle
Release 2023.1.1 [December 2023]
Features to Complete
- None
Features to Consider
- None
Features Ready for Release
- None
Previous Cycles
Release 2023.1.0 [October 2023]
- This version of the Historical Enrollment report uses the vw_sps_enrollment table. The procedure name is a2_hist_enrl_pbi.sql
- There are two blocks of codes in the a2_hist_enrl_pbi.sql procedure.
- (1) Historical Enrollment by Term
- Enrolled students count only once in a term
- (2) Historical Enrollment by Academic Year
- Academic Year = Fall of the current year and Spring and Summer of the next year (for example, AY21 = fa20, sp21, su21)
- For the MS and MPS programs, students count only once within the Academic Year. In case a student changes the modality or program with the Academic Year, we check the Max_Term in that Academic Year and select the Program_Code, Degree_Code, and School Code from that Max_Term. For example, If a student is in SPSUMA in Fa21, and SPSUSC in Sp22, then the student is considered a student in SPSUSC.
- For the MS and MPS programs credits, sum the Max_Credits from the Billing and Academic credits in that Academic Year.
- For NW/CN, If a student is NW in an Academic Year, then the student is counted as NW, otherwise CN.
- For NDG, CER, and CPF, if a student enrolled in one program in the fall, and then enrolled in another program in the spring then the student will be counted twice. For example, if a student enrolled in fa21 with SPNDGV, in sp22 with SPBUSS, and in su22 with SSUSP then the student will be counted three times. The credits will sum from the separate program.
Release 2022.1.0 [January 2022]
- Include the inactive program codes (Remove code: AND p.Program_Status_Code='A') both on the Term and the AY procedure
- Adjust the billing_program_code to remove the duplicate program code
- Use RTRIM to remove the blank spaces
- Removed the duplicate PID in term: 20183, AUAUL, HEGIS_Program_Code - (Note: Removed the SPS_Program, HEGIS_Program_Code from the top part of the code and added them at the bottom to correct it.)
- Update the a2_hist_enrl_u46_ay procedure - (Note: Removed the SPS_Program, HEGIS_Program_Code from the top part of the code and added them at the bottom with pgrm.HEGIS_Program_Code to correct it.)
- Created a Function, named it dbo.getAcademicYear to obtain the Academic Year dynamically, and updated it in the code. The field name is Academic_Year
- Added AY21 to the a2_hist_enrl_blt_ld_u46_ay and rebuilt the table
- Removed AY21 from the a2_hist_enrl_cur_ld_u46_ay stored procedure
- Added AY23 to the a2_hist_enrl_cur_ld_u46_ay stored procedure
- Added a function on a2_hist_enrl_u46_ay to calculate the Academic_Year dynamically. (Academic_Year=dbo.getAcademicYear(t1.Term_Identifier)) – commented on the hardcoded codes
- SQL Only: Added the ipeds.IPEDS_Long_Name field and joined the IPEDS_DEMO_DEMOGRAPHICS view to the a2_hist_enrl_u46_ay.
- 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
- Info Image set to Width = 75, Height = 50 and Y Position = 12
- Infor version text Width = 75, Height = 30 and Y Position = 55
- Report title Width = 500, Height = 75 and Y position = 12
- Remove the Slicers from the report pages and add them to the Filters pane
- After the design completion, publish the report on adcudev and test
- Compare data with the adcudev report and adcu PROD report.
- Added the Residency_State_Code to the available filters
- Added the 'Program_Status' filter
Release 2021.1.0 [November 2021]
- Some program names appear as uppercase rather than the mixed case.
Release 2020.1.0 [August 2020]
Object Type | Object Name | Object Details/Notes |
Table | historical_enrollment_ay | This table holds the static and dynamic historical AY data. The current two years of data (AY20, AY21) are dynamically pulled when the procedure runs and the rest of the AY data is static (data not changed in the table) |
Stored Procedure | a2_hist_enrl_u46_ay | All academic year calculations (Headcount, credits, FTE) are done within the procedure. |
Stored Procedure | A2_hist_enrl_blt_ld_u46_ay Note: Need to run this procedure only once during the initial table build. | This procedure deletes and recreates the 'historical_enrollment_ay' table and loads the historical (static) data to it by calling the procedure ‘a2_hist_enrl_u46_ay’. Note: Need to run this procedure only once during the initial table build. |
Stored Procedure | a2_hist_enrl_cur_ld_u46_ay | This procedure deletes and loads the current AY data into the 'historical_enrollment_ay' table by executing another procedure 'a2_hist_enrl_u46_ay'. |
Stored Procedure | a2_hist_enrl_main_u46_ay | Execute the procedure ‘a2_hist_enrl_cur_ld_u46_ay’ to update the dynamic data into the table ‘historical_enrollment_ay’. Then retrieve all data from the ‘historical_enrollment_ay’ table. Note: Currently, AY21 and AY22 are dynamic. Every time, the procedure is run, it deletes the AY21 and AY22 data and reinserts the data into the table. |
Stored Procedure | a2_hist_enrl_u46_term | Retrieve tuition bearing student headcount and credits for the students who took courses offered by SPS (By Term). Note: Data pulled directly from SIS |
- Updated the a2_hist_enrl_u46_term stored procedure, counted the sponsored programs from TSTA for the headcount and the credits, then used UNION to combine both procedures.(9/9/2021). Note: Sponsored programs to filter in SP (pgrm.HEGIS_Program_Code NOT IN ('N')
- Updated the a2_hist_enrl_cur_ld_u46_ay stored procedure added the code to add the AY22:
- Updated the a2_hist_enrl_u46_ay stored procedure, counted the sponsored programs from TSTA for the headcount and the credits, then used UNION to combine both procedures.(9/28/2021). Note: Sponsored programs to filter in SP (pgrm.HEGIS_Program_Code NOT IN ('N')