BUG: Forecasted data does not include cases where there was not actual enrollment in the program in the previous term/year (SPCOMO in 2022, for example)
The most recent term should include actuals to the current date, and projected fill from that point forward based on current fill and past patterns
Include headcount, average credits, and tuition as alternate data points
Filtering
Allow switching view between daily, weekly and monthly
Data Extracts
Include other tables in the extract page
UI to switch between between data to be extracted
Engage EBIS to see if we can enable PBI Automate to save files to Google Drive
Features to Consider
Include a new report page that shows the progression of tuition over time for all programs
a2_tuition_over_time procedure to aggregate all tuition from U_46
Based on new U_46_dates view that includes the transaction date for all tuition transactions
Allow filtering on program and degree code
Add a visualization that can show the change of tuition over time for those cases where people are watching the numbers daily for any sign of movement
Requires a new procedure to pull tuition totals as of a particular date, probably as a weekly snapshot as a separate table
Add in the calculated revenue to the enrollment only view to allow it to show the projected revenue based on the enrollments
Use the methodology that Andy currently uses to calculate for the daily email
Features Ready for Release
None
Previous Cycles
Release 2023.1.0 [March 2023]
Enrollment Trends
a2_trcl_trends.sql procedure
Pull all of the TRCL history for the past four years
Include the following data points
Transaction Date: Date of TRCL transaction
Program Code: Billing Program Code
Term Identifier: Term of the transaction
Term Name: The user friendly name of the terms to view (Summer, Spring, Fall)
Daily Credits: Net credits added/dropped per day
Final Total Credits: Final credits at the end of the enrollment period for the term
Percentage of Final: The percentage of the final credits as of the transaction date
Running Credits: Total number of credits for the enrollment period as of the transaction date
Target Credits: The latest defined credit target for the program by term
Add a new page showing the enrollment trends for a specific term name (Spring, Summer, Fall) from the latest available term to the past four years
Include actuals from all terms with active enrollment
Compare the trend for each year against the projected trend based on the previous year fill pattern
Filtering
Allow filtering on program and term name
Allow filtering date range
Data Extracts
Add a new hidden report page that allows for exporting full data rather than a subset from the visualization pages
Add bookmark to allow access
Tables to extract
Full data behind the new Enrollment Trends report
Release 2022.1.2 [December 2022]
Add a Projected Tuition Melt page (hidden but bookmarked)
Include a static table of tuition rates and projected tuition totals since the values do not conform to full targets with new and continuing splits
Show actuals to date blended with the static tables
Calculate projected credits based on tuition rate (projected tuition / tuition rate)
Target Data Page
Add Program Description slicer
Normalize the target data table to eliminate more than one name per program across terms
Add SPS Program field to the underlying data and filter panel
Update enrollment values in gauges to whole numbers
Release 2022.1.1 [November 2022]
Allow users to select the source Target Period for all pages of the report
In the right nav section that shows the current target, make that a drop-down slicer
Requires updating all procedures to pull the full range of target periods instead of just the latest
Minor tweaks to sizing of the Target Term buttons to fit up to seven rows
Update the underlying query to pull only the previous six years of history to lock the layout of the year selectors, and to reduce the data that is contained in the report
Consolidate the tuition and enrollment procedures into a single procedure with a parameter to define the output
a2_enrollment_vs_target_pbi
Add a term lookup table to the procedure output to allow selecting a term regardless of the underlying data
Needed to make the switching of target groups more intuitive
Release 2022.1.0e [October 2022]
SQL-ONLY: Add a1_sps_enrollment view to a2_enrollment_vs_target_pbi_enrollment SP
SQL-ONLY: Old SP saved as W:\System Folders\Stored Procedure\mji3\a2_enrollment_vs_target_pbi_enrollment.sq - 10-31-2022 t
SQL-ONLY: Rename a2_enrollment_vs_target_pbi_enrollment_new to a2_enrollment_vs_target_pbi_enrollment
Release 2022.1.0d [August 2022]
SQL-ONLY: Fix the issue with credits in Enrollment Only not aligning with the Enrollment Management report
Release 2022.1.0c [July 2022]
SQL-ONLY: excluded the REGIK0200 and REGIK0202 billing credits from SHGEN5 for Summer 2022 (a2_enrollment_vs_target_pbi_tuition) (ZD Ticket #37382)
Release 2022.1.0b [May 2022]
SQL-ONLY: Switch the WHERE clause for Enrollment Only to use Faculty_Code='SPEC' instead of School_Code to align the logic with the Enrollment Management procedure
Release 2022.1.0a [March 2022]
BUG: Fixed issue with filtering on degree code showing incorrect results
Release 2022.1.0 [March 2022]
Added filter panel, and SPS Program to the available filters list for all pages except Target Data
Create lookup tables to allow the banner slicers to be shared across all pages of the report
Cosmetic updates to bring the report in line with the latest SPS design standard
Report should align to top of window, is currently vertically centered
Release 2021.1.3 [November 2021]
Update graph to show tuition, headcount, and credits, target vs. actual
Update procedures and the u_targets table to replace "Columbia Summer" with "Summer Session"
Remove the SPS Program filter
Standardize all fonts to Segoe UI
Cosmetic updates to layout and sizing of elements to align with style guidelines
Release 2021.1.2a [November 2021]
SQL-ONLY: Update the procedure to include targets when there is no tuition; appears to have dropped out with the target version control code
Release 2021.1.2 [October 2021]
Update the u_targets table to allow for versions of targets
No changes required
Return only the highest target sequence for each program code, status and term grouping
Add a slicer to show/hide programs with no targets defined
Add a slicer to the target data page to allow selecting the budget source
Make Actual vs. Target visual consistent (actual on left) for all report tabs
Update all fonts to Segoe UI for consistent rendering
Remove visual header from all elements that do not need it
Release 2021.1.0 [January 2021]
Add a report page that shows the status column
Tuition by Status
Fix calculation of the Diff Avg Credits column in the Tuition view
Add the HEGIS Y/N indicator to the tuition view
Requires an update to the stored procedure
Add high level visualization to show trends of tuition and enrollment targets vs actuals
Update the term selector to allow multiple terms for trend analysis
Use new selector formatting in the header for Program and Degree filters
Add version and link to documentation to header
Resize the Term slicer to eliminate scrolling requirement
Make the Term slicer consistent on the Targets page
Sort the term selection slicer in reverse order to show the latest terms at the top of the list; these are the most often used values
Release 2020.2.5 [October 2020]
Changed the method for calculating the average credits for both actual and targets to use an in-report formula vs. the SQL and target table calculations
Release 2020.2.4a [October 2020]
SQL-ONLY: Added ‘SD5P’ exclusion to the rows being returned in the tuition procedure
SQL-ONLY: Fix issue with doubled AYI target tuition counts
Release 2020.2.4 [July 2020]
Remove the saved NDG filter on the Enrollments report page
Actual and Target Credit Averages need to be calculated using a DAX measure to resolve the issue of halving the average across CN and NW credit average counts on the Enrollment Only report page
Release 2020.2.3 [June 2020]
Fix the Actual Credit Average to use SUM instead of AVG
Release 2020.2.2 [June 2020]
Ensure that comma formatting is set for all of the numeric values; currently inconsistently applied across the reports
[SQL-ONLY] Ensure that all joins on text fields convert to lowercase to resolve issues with inconsistent case in strings; use target table strings whenever they exist first, then fall back to PRGM for null cases
Release 2020.2.1 [April 2020]
[SQL-ONLY] Fix issue with Student_Status = RE causing counts to be off on the aggregates (ALP Student in 20201)
[DATA ONLY] Update target table to consolidate the HSP for online
Release 2020.2.0 [April 2020]
Need a new page of the report to show enrollment and credits ONLY; the purpose is to show progress toward targets in advance of actual tuition collection, which comes much later in the cycle
Group all of the non-SPS offerings into a single “Columbia Summer” bucket for the degree code and switch the slicer to use that field instead of the description; only for Summer term identifiers, use “Others” for other terms
Add a HEGIS code field and a slicer to allow hiding non-SPS courses
Ensure the defined NonSPS groups are aggregated in the SQL for matching against the targets
All other NonSPS enrollments will be gathered into a “Other Schools” bucket and displayed without targets
Removed duplicative degree section from the tuition page
Now showing Non-SPS courses in the tuition bearing columns for all terms to allow for Summer tuition from outside SPS; this will display some stray tuition from Non-SPS sources in non-Summer terms which are either errors to be cleaned, or valid tuition paid to SPS
Updated join to show cases where there are defined targets but no current tuition accrued in u46
Combine cases where the student_status = “RE” to place them in the “CN” status
Cleaned up the SQL procedure to strip out some duplication and focus more on the u46 view; no legacy calculations to pull headcount and credits
Lift hard-coded term restriction
Add a term selector to the interface
BUG: There are some non-tuition bearing credits appearing in the matrix output (e.g., auditors are showing enrollments > 0 but the tuition amount is $0); updated the SQL to pull rows with tuition only
Release 2020.1.0 [March 2020]
Initial launch of the report to production; leadership access only