Technical Details

Current Release Version: 2024.1.0

Release Date 22 Oct 2024

Report Location: PowerBI / Apps / SPS Leadership

Security Roles: Leadership

Requested Features

Current Cycle

Release 2024.1.1 [December 2024]

Features to Complete

• Add the "Sponsered Program" filter to the enrollment-only report in PBI
• Replace the 'Target Defined' with 'Sponsored Program' in the 'Enrollment and Tuition' and ''Enrollment Only' pages.
•  Trends
  • Include selector for the targets to use for the trend analysis instead of defaulting to the latest available for the term
    • This can get complicated when the targets are not aligned across the years (e.g., the latest for 2022 was Q1, but for 2023 it was Q2)
  • BUG: Spring forecast charts are off, likely caused by the offset date method to keep numbers from spanning over two years.
  • 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 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

• Change top level slicers to multi-select by click (not CTRL)
• SQL Only:  Add @_output = ''tuition_only' output for the tuition data without the target
• SQL Only:  added REGI code exclusions for Summer 24

Previous Cycles

Release 2024.1.0 [October 2024]

• Tuition Melt
  •  Changed the Target Term from 20241 to 20243.
  • Note: Click on the Target Term visual, open the Filters page, and under the 'Filter on this visual,' change the Term_Identifier from 20241 to 20243. 
  • To change the Bookmarks, click View and select the 'Target Term' visual; then under the Bookmarks pane click 'Add' to add a report Bookmark. Delete the old 'Projected Melt' and name the new report bookmark 'Projected Melt'.
  • SQL Only: Change the Version_Name from 'Q3 Projection' to 'Q1 Projection' in the a2_evt_tuition_projection stored procedure

Release 2023.1.1b [August 2023]

• Enrollment Only
  • SQL-ONLY: Added in logic to include credits for non-standard credit programs such as ALP and Pre-College, and remove REGI courses from the counts

Release 2023.1.1a [May 2023]

• Trends
  • SQL-ONLY: 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)
    • Loading new SPCOMO with targets as the forecast since there is no history

Release 2023.1.1 [April 2023]

• Trends
  • Move pages out of bookmarks and add to the main navigation panel
  • The most recent year should include actuals through the current date, and projected fill from that point forward starting with the current fill position with the target fill rate pattern
    • Use the point in time actual count and divide that by the fill rate on the same day from one year ago to get the estimated final landing position
    • Forecast actuals from there through the end of the year using the target forecast fill rates
  • Separate page to show all years in the trend chart for actuals, with the projection pattern for the current year (Trends All Years)

Release 2023.1.0 [March 2023]

• 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 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
• a2_u_target_pbi (procedure)
• a2_enrollment_vs_target_pbi_tuition, a2_enrollment_vs_target_pbi_enrollment
• 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
• Hard-coded to 20201 term identifier