Technical Details
Current Release Version: 2022.1.0
Release Date:
Report Location: PowerBi / Apps / SPS Enrollment
Security Roles: Security Roles: Leadership, Admissions, and Enrollment
Requested Features
Current Cycle
Release 2022.1.1 [April 2022]
Features to Complete
- None
Features to Consider
- Add state to the available filters
- SQL ONLY: update the Academic_Year in the storeced procedures (1) a2_hist_enrl_cur_ld_u46_ay (2) a2_hist_enrl_blt_ld_u46_ay (REPEAT THIS PROCESS IN EVERY VERSION UPDATE)
Features Ready for Release
- 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 the hardcoded codes
Previous Cycles
Release 2022.1.0 [January 2022]
- 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.
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')
0 Comments