Technical Details

Current Release Version: 2024.1.4

Release Date: 20 Aug 2024

Report Location: PowerBI / Apps / SPS Admissions

Security Roles: Admissions, Leadership

Requested Features

Current Cycle

Release 2024.1.5 [September 2024]

Features to Complete

Factor in the Decision Release Date when comparing CTD values to give a more accurate depiction of the state of past pipelines at a specific point in time
- Particularly useful to ensure that the fraud remains in the submits until they are actually flagged as fraud, and are then removed

Features to Consider

Add a target point to the current year in the KPI Tracker to show the ultimate goal
- Include a trend-line for the current year to see how we are tracking toward the target
Add a view to show week over week progress for the tracked milestones
- See the following example for how it is being done manually for APAN
Add in an option to change the historic yield and selectivity percentages based on a multi-year average of all available years for the matriculant projection

Just add one more option to the basis year slicer called AVERAGE, and when selected switch the calculation to measures based on the collection instead of the single year

Add in Help Tooltips for major features to make it more clear how to use functionality within the user interface
- CTD/EOT filter
- Top term year filter
- Comparison term year filter
- Pipeline status gauge
- Projected and actual gauges

All Years
Program Details

“What If” scenarios for individual rates to see how altering will change the overall numbers and outcomes

Selectivity, Submit Rate, RFI to App Start, Start to Submit

Change the Commit Date to the Deposit Received date where there is a value instead of the Decision Released date

Requires adding the Deposit date to the data feed from Slate and an update to the procedure for which date to pull into the report

Add the historic split between domestic and international to the basis parameters and include in the matrix

Use this value to alter the matriculant target when the report is filtered on domestic vs. international

Calculate the expected melt rate from history and add it to the grid

Update the overall projection based on the applied melt rate for each program

New columns (requires updated data pipelines)

Fraud rejects
Admit to ALP
Admit to future term
- Currently handling as incoming deferrals in the enrollment term, and regular admits in the application term
HBCU

Matric Projected Calculations
- Add subtotal to the column
  - Requires an update to the method for calculating from a filtered measure

Exclude deferrals to a future term from the projections for the current term
- This is a very small fraction of the counts, but removal will yield more accurate projections

Add in an “Unfinished Rate” to show how many applications tend to be abandoned before completed for decision

This can be used to give a sense of how many submitted apps will move to the next phase based on history
This will require a new data point that separated Pending from the Incomplete applications

Create a detail page to show a more pointed view of the data for a selected program code
- Visualizations
  - RFIs by Term
  - Actual vs Target Enrollment
Split the Pending category into Awaiting Decision and everything else to allow a better sense of how many can be acted on immediately vs. waiting for completion

This will likely alter the calculation and language for the Action column

Other requested demographics with filters

City, institution, work experience, age, gender, undergraduate GPA

Add red/green color coding to matriculant actuals column compared to matriculant target
Add ability to select the target to apply to the view
- Currently the latest issued target is displayed, but it would be nice to be able to see how the pipeline performed against the original targets

Features Ready for Release

Previous Cycles

Release 2024.1.4 [August 2024]

The Enhanced page for the year 2024 of CTD shows the incorrect date.
- Note: On the Dax (__param_YearToView), change the maximum value from 2023 to 2024.
Admit, Commit, Decline, and Waitlist counts don't match with the top and bottom porting of the Enhanced report page
The bottom measures are correct, use the same measure on the top and bottom
Measures added to the top, Admitted By Date Bottom, Committed By Date Bottom, Declined By Date Bottom and Waitlist By Date Bottom

Release 2024.1.3 [July 2024]

Added the 'Total Apps' and the 'Waitlist' Column to the Summary page.

Release 2024.1.2 [June 2024]

Data feeding changed from manual feed to auto-feed.

Release 2024.1.1 [April 2024]

ASR Export Capabilities and Scrolling in Bottom Chart

Release 2024.1.0 [March 2024]

Pull the data from Slate with the new columns and manually create a table. Name it APPL_SLATE_NEW.
Created a view named a1_admissions_summary_dev. Removed the table APPL_SLATE and added APPL_SLATE_NEW to the a1_admissions_summary_dev view
Added new fields called [Enrollment Payment Transaction Date] and [Commit (Decline) Date] in the a1_admissions_summary_dev view.
Created a stored procedure and named it a2_asr_full_year_dev
Changed vw_admissions_summary to a1_admissions_summary_dev in the a2_asr_full_year_dev procedure
In the Admissions Summary v2023.1.7 -dev-final Power Bi report, change the data source on ALL Years query to 'execute cereports.dbo.a2_asr_full_year_dev NULL'
Create a new measure as Admitted By Date Bottom, Waitlist By Date Bottom,

Release 2023.1.6 [October 2023]

Change the date that the report flips to viewing the Fall term for the upcoming year to start October 1st
- E.g., currently the report turns over at the start of the new year, 1/1/2024 would be the first day that the previous year comparison is before the end of the most recent Fall term

Release 2023.1.5 [October 2023]

Year selectors in the default view of the Summary page do not include 2024

Release 2023.1.4a [September 2023]

Restore dropped Domestic filter lost in last release
Updated default views to Spring term
Updated bookmarks to ensure latest views are reflected for ASR email release

Release 2023.1.4 [August 2023]

Add Potential Matrics to the menu of the report
Add logic to the title for the Potential Matrics to change the tile if Domestic or International filtering exists
- Add bookmarks to three views: All, International, Domestic

Release 2023.1.3c [August 2023]

SQL-ONLY: Remove enrolled students from the ghost indicator counts
More bookmark updates for Spring 2024 reporting

Release 2023.1.3b [August 2023]

Update bookmarks for Spring 2024 reporting

Release 2023.1.3a [July 2023]

KPI Tracker: widen the year selectors to keep it from scrolling
Minor cosmetic changes to the year selectors on the Summary page, and text callouts for fraud and potential matrics
SQL: Add two years to the data pulled for history (6 total)
Updates bookmarks to pull latest terms

Release 2023.1.2 [May 2023]

BUG: Fixed issue with drill-through page not showing selected comparisons years other than 2021

Release 2023.1.1 [April 2023]

Minor cosmetic changes to the year selectors on the Summary page
SQL: Add two years to the data pulled for history (6 total)

Release 2023.1.0 [February 2023]

Comma is missing in Awaiting Materials value (all pages)
Update bookmarks to remove outdated views, and to switch Spring 2023 to EOT

Release 2022.1.8 [December 2022]

Add a column that combines term commits + deferral commits - the total number of applications flagged as unlikely to matriculate
- Remove the Program Code column to make room for the new value
Include the Ghost Application field from Slate in the daily warehouse import
Include a new change management page covering the modifications for the "ghost application" field
SQL-ONLY: Use the SIS Application_Received_Date as the default submit date instead of the Submit Date from Slate
- The SIS date is more accurate, since that will include the date that the application fee was paid and the app sent to SIS, which can often be delayed
Projection Basis
- Remove CTD/EOT visual and set underlying filter to always EOT

Release 2022.1.7b [November 2022]

The DeferredDuplicate records are not being suppressed in the Enhanced and Summary pages.
Add program description to the filter panel on the Summary page

Release 2022.1.7a [November 2022]

BUG: Waitlist is not being removed from the In Review data point.
Ensure that all submit, admit, and commit counts include deferrals ONLY in the application term

Release 2022.1.7 [November 2022]

Check the negative values in the Pending column in the lower matrix
Convert a2_atm_enrollment procedure to use universal a1_sps_enrollment view
- New name: a2_asr_enrollment
- Refactor to remove unused columns and simplify grouping logic
Convert a2_atm_deferrals procedure to use the a1_admissions_summary view
- New name: a2_asr_deferrals
- Refactor to remove unused columns and standardize deferrals using a single approach

Release 2022.1.6 [October 2022]

Split the Pending column into Awaiting Materials and In Review data points
- Include the split in the latest term only since there is no clear date to associate with historically; keep the pending category for all previous terms to show volume comparison
Move to new procedure: a2_asr_full_year
- Add CUID to the procedure output
- Add Year column to the output
  - Simplify the __lookup_year table in Power BI to create a simple table based on the values in Year

Release 2022.1.5b [September 2022]

BUG: Future years were causing issues with the diff counts in the top view
Gracefully handle new years in the year lookup tables to eliminate manual edit requirement
Updated logic to look back differently depending on the time of year and terms
Update report for 2023
- __lookup_year measure: Remove 2018, add 2023
- Reset filters for Enhanced and Summary pages to Spring 2023 defaults
- Add Spring 2023 bookmark for Summary page

Release 2022.1.4a [August 2022]

SQL-ONLY: Added exclusion for cases where Slate has a person-scoped Deferral tag on the profile, but the Fall 2022 application is not actually a deferred app (same application and enrollment term).

Release 2022.1.4 [May 2022]

Update default views to filter on Fall 2022 and Masters
Fraud changes
- Added dynamic footer message to reflect whether fraud is included or not based on the state of the IsFraud filter
- Updated all of the bookmarks to include new fraud display defaults
- Set the default filters to remove fraud from all views
- Create a change notice page explaining the fraud removal, and showing the impact of fraud on the pipeline
  - Bookmark the page for easy addition to the weekly send

Release 2022.1.3b [May 2022]

SQL-ONLY: Switch underlying query to use the shared a1_admissions_summary view
Fine tune the sizing to fit the summary to a single page for the print-ready version

Release 2022.1.3a [March 2022]

Tweak the row padding of the summary page to accommodate the new SCOM online program row in the printed Masters view

Release 2022.1.3 [March 2022]

SQL-ONLY: Added 'AI' (Admit to ALP) that meet the commit criteria to the Commit column counter for the current term
Add a pipeline fill rate visual for submits and commits to the KPI Tracker report page (hidden but bookmarked)

Release 2022.1.2 [February 2022]

Rename to Admissions Summary across the report views
- Split into Enhanced and Summary report views
Replace the CPF degree code in the program lookup query with CPA/CPF
Change ASR Started column header to Started / Unsubmit
Move the card displaying the version of the targets information so that it does not block the export ellipsis for the top matrix
Removed the per row filters on the yield measures to fix issues with display in the projection basis review page
SQL-ONLY: Fold SPCEDR and SSHRP into the SPNDGH code for historic College Edge numbers

Release 2022.1.1 [February 2022]

Add a note on the main report page where the targets were sourced (e.g., Original Budget, Q1, Estimate, etc.)
Add a hidden page for building a print-ready version of the ASR for weekly distribution
SQL-ONLY: Pull matriculant data from U46 with tuition-bearing credits to align the numbers with the EvT report
- The only concern would be if there are tuition-free programs that need to be shown in the ASR; currently there are none
SQL-ONLY: Fix cases where there is no decision date in the Admissions data pipeline (mostly ALP); use the SIS Manual_Decision_Date as the default
SQL-ONLY: Pass the Apply_Term into the Entering_Term when it is NULL

Release 2022.1.0 [January 2022]

Add CTD/EOT back to Basis Review page; set it to EOT by default
Ensure the grids are high enough in the layer hierarchy to allow the hover options button to stay visible
BUG: When a year other than the most current is selected, the CTD comparison date for the top section of the main report page is behind by one year from where it should be when the new year turns over (e.g., Fall 2021 should be 01/2021 in January)
BUG: The details sub-report page in CTD mode does not use the same comparison date as the main report page when calculating the difference YOY in the rate indicators, so the differences are not accurate

Release 2021.2.5 [December 2021]

BUG: The lower view is showing EOT numbers regardless of the CTD/EOT flag setting
Removed CTD/EOT slicer from the Basis Review page

Release 2021.2.4 [December 2021]

Updated the year logic to properly shift when the new year rolls over
Rate RFI App Starts measure used in the basis review page is broken with new data structure
With improved performance of the main view, update the procedure to pull the most recent five years of history
- Ensure that the query is evergreen and automatically moves to the correct year based on the current data
Add a note to the previous year grid that the right side values are all EOT regardless of selection
Update banner elements to align with latest design brief

Release 2021.2.3 [December 2021]

Cosmetic updates to bring the report in line with the latest SPS design standard
Trimmed down the dataset footprint to reduce the overall memory requirements on the server-side
Removed matriculant projection details from the aggregated view to improve performance
- Projection data can be viewed per program at the detail page level

Release 2021.2.2 [December 2021]

Update link to new Confluence documentation
Make a pass to improve performance, particularly when switching between years and CTD/EOT
- Update any measure that is using multiple IFs to use a single SWITCH

SQL-ONLY: Limit the number years pulled to 2020+ in the procedure to improve performance
Removed CUID column to reduce database size by nearly 50%

Release 2021.2.1 [October 2021]

All Years

Add in a filter for IsFraud to view the pipeline with or without fraud apps

Add in the “official” estimate against the target from Finance to the program details view (when published)
- a_u_target_atm_pbi [deprecated]
- Requires an update to the table structure to allow for a specific version of the targets to use (e.g., Original, Q1, Q2, etc.)
- Display the latest set of targets in the ATM, along with the version name to be clear of the source

Standardize on central U_TARGETS table

Remove dependency on the Reference table for Enrollments and remove from the data model

Release 2021.2.0 [October 2021]

Program Details Page
- Submit Rate
- Admit Rate
- Commit Rate
- Reject Rate
- Fraud Rate
- How many are coming from:
  - Commits * commit yield
  - Uncommits * uncommit yield
  - Commits * commit yield
  - Uncommits * uncommit yield
- How many came from:
- Target Apps = Enrollment Target / Yield Apps

Create a drill-through report page to replace the Action column, showing the prediction details in a more visual format
Show how the current term is tracking against the prediction term for the following indicators
Show the details behind the projected number (left side gauge)
Show the details behind the actual matriculant number (right side gauge)
Include a total application target based on historic submit rates, and current progress to goal

Matriculant Projection
- Adjust the projection measure to use all relevant indicators from the funnel to predict outcomes
  - Used in the Projected result gauge
  - Used in the Actual result gauge

The calculation currently looks only at commits to predict the number of matriculants, which is not useful for early in the admission cycle

Include ‘By Date’ calcs for all of the rate measures to calculate based on the CTD for better comparisons of how the current term is performing at the specific point in time
Add a toggle button on the lower half to show the EOT numbers for the years being compared, instead of just the CTD numbers

Adjust the top half deltas to calculate based on the new selection

Use the comparison year (YearToView) for all predictive calculations and remove the separate slicer (YearToModel) from the UI

This should make it more intuitive, and also allow for comparisons beyond just the pre-defined three year period
Need to adjust all measures that use the YearToModel slicer

Shift the table structure to pull all years into a single table instead of separate tables per year to ease the transition from year to year and enable wider range of YOY comparisons
- Show the comparison date in the view header to make it clear which date is being used for the comparison
- This will allow the report to run without requiring modifications and redeployment with each new year

Add a year selector to filter the lower view based on the comparison year
Update all page elements and fields on the unified year view to use the new unified table
Update the ASR Build page to use the unified table
Remove the individual year pages and tables from the report
Update code to make the report independent of the year (e.g., all calculations on dates are -365, -730, etc.) and remove references to the actual year (e.g., use Term - Current Year, Term - Prior Year)

Projection Basis Review (Internal-only for now)
- ATM Data Analysis Research Notes
- Fields in orange are end of term since there is no value until there are matriculants at the end of the cycle

Include new rates including admit, commit and reject/fraud rates
Include new yields including total apps, submit, and admit yields
Using the new report page, perform a deep analysis of the previous years to determine which indicators can reliably predict action
Use By Date fields so it can be split between CTD and EOT
Add projection column and a variance percentage against the actuals (when available)

SQL-ONLY: Ensure that the SIS decision code is included by default if one exists, otherwise resort to the Slate/Salesforce value
Handle “Admit to Future” cases
- SQL-ONLY: Code mods for full year procedure (a2_atm_full_year)
- SQL-ONLY: Calculate the commit status using deposits or program code for Admit to Future cases (a2_atm_deferrals)

Show everything through the admit in the applying term
Show commits in the enrollment term as committed deferrals

Action Column [DEPRECATED]
Include the new AD deferral status in the filtering on the Enrollments From Defer column in the previous year to get the numbers to align in terms after the change in coding deferrals
Add commas to all columns in the 2022 page view
Actual enrollments from the current and past terms do not adhere to the filtering on region

This is causing problems with the projection calculations when they are looking at the domestic/international yield percentages
The issue was that the Enrollments table was not linked to the Country lookup table in the relationship map
Pulling the country code from RESI instead of APPL for more consistent filtering

Add the projected number of matriculants instead of just the delta from the target

This would allow us to better see how many students will be coming when the filters are applied because they will not depend on the target, which is not divided into demographic segments

Release 2021.1.8a [September 2021]

Update the coloring of the subheadings for more contrast with the sliders
Update the ASR Build Tool bookmark to replace the old filters that were applied
Validate the PY calculations; there are still cases, particularly in SUMA, where they don’t seem right (look at Submit PY)

The issue was the DeferDuplicate was not being filtered in the calculated measure; added the filter to the overall datasets for both years in the view

Release 2021.1.8 [September 2021]

Change fonts to Segoe UI throughout for consistent rendering
Hide extraneous filters from view
Remove underscores in filter name labels
Add DeferDuplicate = 0 to the ASR build tool page for all visualizations
Update the table views to group into a matrix by degree code for grouping
Also allows the exported data to include the degree code for reporting on the dataset

Release 2021.1.7 [August 2021]

Find out why some of the records in the matriculant stage have a blank value for the domestic field

All actual matriculants should have a a location value
This is a product of mismatched country codes in the lookup table

RFIs in the PY are not looking at the year to date, but are a total for the term
Add 2021 to the prediction basis selector (for use in the 2022 year)
Make the Domestic value TRUE/FALSE to be more intuitive in the filter
Changing the Domestic to TRUE/FALSE broke the Projected and Action measures, refactored to resolve the issue
SQL-ONLY: Remove the a7 scoped procedures from the DB server

All a2 procedures should now be in use

Hide the old single term report pages
Update the consolidated view to be for a specific calendar year

Add a new copy of the consolidated view for 2022

Hide any exposed filters that should not be visible
Update the Action column to use the Matriculant Delta measure for consistency
Remove the subtotal from all of the PY delta columns (not consistently accurate)
Remove the PY column from the previous year view to simplify the report view

Color code the full application funnel (similar to ASR)

Release 2021.1.6 [August 2021]

Add a consolidated view with a term selector to simplify the updates to the report

Fix issues with the data model properly filtering on deferrals and targets

Convert server procedure to production naming convention and document in the procedure repository (from a7 to a2)

Add all procedures to official active procedure list
Ensure latest report uses new procedures

New report page to show the basis values for all of the calculations that take place for projections for 2021, 2020 and 2019

For validation and informational purposes
Add all of the EOT values and percentages for every term in the underlying datasets for terms that can be used as a basis
Add a slicer to show the basis values for any given term (Spring, Summer, Fall)
Hide the report page, but create a bookmark to access

Strip out the subtotal values for all of the calculated percentage measures (yields, selectivity, submit rate)
Commit yield rates differ pretty dramatically across the domestic and international segments for many programs
- Pull from the APPL / DEMO tables

Update the calculations for ΔProjected and Action measures to split rates across the two segments
2019 and earlier (from Salesforce) does not have Country values

Filters (requires data model changes)
- Disable all of the other filters so only the most relevant are displayed
- Will need to add student status to the export for APPL_SLATE
- Include in all year tables, deferrals, enrollments, and outcomes (RFIs)
- Include in all year tables, deferrals, enrollments, and outcomes (RFIs)
- Domestic split is simply USA and not USA
- Country table should use three letter code, but map it to the user friendly country name for selection

Use the filter navigation instead of taking up report real estate
Part-time/full-time/non-credit
Country, region and domestic vs. international

The ALP actuals do not appear to be pulling correctly for the current term

Filtering on Entering_Term and ALP (among others) are not fully coded with an entering term; removed filter to get actuals

FIX: There is something wrong in the 2019 prediction flip calculations
WORKAROUND: The subtotal calculation at the bottom of the ΔProjected column is not correct

Issue with the way the calculated measure performs in subtotals, removing the value from the table for now and will push the bug to MS

Remove the calculation subtotal in the Action column since it is not something that should be rolled up

Release 2021.1.5a [July 2021]

SQL-ONLY: Add non-deposit cases for deposits to the a7_atm_enrollments procedure

Release 2021.1.5 [July 2021]

Admit/Defer Counts

The ASR shows raw admits, including deferrals or future term admits; ATM now replicates this behavior

ΔProjected and Action Calculations
- Committed Yield %
- Non-Committed Yield %
- Update all calculations to use the split yields (when applicable)

Create two new yield rate segments for a more refined projection

Add a column to show the number of DECLINED in the pipeline

Include in the YOY visual to see if there is an increase or decrease in declines
Include the declines in the calculation for the action column as they should not be part of the formula to determine potential matriculants

Exclude future term deferrals from the commit counts for the current term (leave as admits)
BUG: When switching to 2019 basis, the new and committed matriculant counts still reflect 2020; only switches the yield percentages

Change the Enrollments[TSTA_Term_Identifier] filter to point to use the selected term rather than the page default

Update the Slate export to exclude all records in the “Inactive Applications” round
Add a column to the atm_full_year procedure to set a flag if the record is considered a duplicate application from the old multiple application deferral method

Dupe applications using old method have a reply code of “D” and a Deposit code of “W”

Release 2021.1.4a [July 2021]

SQL-ONLY: Include IS NULL in the Deferral <> “D” exclusion so the nulls are not excluded
SQL-ONLY: Actual enrollment counts for AYI in the previous year for Fall are incorrect

Release 2021.1.4 [May 2021]

SQL-ONLY: Removed the filter to validate that a Program Code is present in the APPL_SLATE export as it was causing some applications to be excluded from the counts in Admit to ALP scenarios
Remove the PY delta for pending and rejected applications from the main view

Keep calculated measures for inclusion in a detail page (Pending Diff YYYY and Rejected Diff YYYY)

Fixed issue with overcounts in the Actual Enrollment column for the current year based on Entering Term and TermID mismatch
Create an ASR build view of the term data as another report tab and hide it by default
- Three previous terms: Spring, Summer, Fall (one year ago)

Create a bookmark to open the ASR Build page content
Page should allow selection of the term identifier to display
Should be a table broken into the same categories from the ASR to make it easy to export and then copy and paste into the Excel report

Release 2021.1.3 [May 2021]

VALIDATE: Commits are undercounted as compared to ASR archive data

This was due to multiple decision dates from the previous method for coding deferrals with multiple applications.
Modified the Slate export to pull the FIRST decision release date to eliminate this issue.

Add more detail to the ACTION column for cases where the target is expected to be exceeded

Percentage over the target, count over the target

Exclude deferral applications that are being double counted from the old methodology for coding deferrals with two applications
Need to populate the Committed Deferrals column from for current term (currently a placeholder)

Create new procedure to pull deferrals for all terms (a9_atm_deferrals)
Add Total, Committed, and Enrolled counts from the deferrals entering in the current term

Show actual enrollment in the current year (where available) to make it clear where we actually landed

Requires a second pull of the enrollment data and a filter on the selected term

Release 2021.1.2 [April 2021]

Add a slicer to allow the user to choose to use either 2019 or 2020 as the basis for the current term predictions (e.g., delta projected and action)
Add a Submit Rate measure to show how the percentage of submitted apps to unsubmitted
- This is a calculation of the total number of apps * the previous year’s submit rate - the total submitted apps

The idea is to give an indication of how many unsubmitted apps could potentially be submitted by the end of the cycle
Include new text in the Action column giving the predicted number of submits to expect
Hook the Submit Rate into the new Prediction Year selector calculations

Include the values used in the calculations in the Action text

Release 2021.1.1 [April 2021 DB ONLY]

Add citizenship country and status to the underlying query from Slate

Update the table import to include the new columns

Updated procedure to join APPL table on Person_ID and TermIdentifier (instead of Program_Code) to align numbers for cases where the application was for one program, but the actual final matriculation was for another program.

Release 2021.1.1 [March 2021]

Clean up the titles for the term sections on each report to better indicate the data
Add notes to the footer to explain the CTD and EOT data sections
Add the RFI term of intent to the Slate extract for RPT_MKT_SLATE_RESPONSES to allow filtering of RFIs by term

Remove old date filtering for RFIs

Add RFIs to previous term section for all report pages
Add Summer 2021 report page
CEREPORTS SQL procedure
- Requires updates to any records that are not properly synced
- SHGEN1,SHGEN2 → SHGEN1
- SHGEN3,SHGEN7 → SHGEN3
- SPGRAD,SSSPL → SPGRAD
- SPGRDV,SSGSP → SPGRDV
- SPNDGV,SSUSP → SPNNDGV
- SPNDGH,SSHSP → SPNDGH
- SPGFNS,SPGFND → SPGFNS

Change count logic to use the SIS data for more accurate representation
Consolidate the following program codes into a single grouping

Release 2021.1.0 [January 2021]

Add the RFI totals to the beginning of the report to show the top of the funnel

Include RFI requests by program over the last six months

RFIs need to roll up into the ALALP aggregate category
There is no way to get RFI data from history to compare the rate
The Pending application counts appear to be off for the previous year section of all report pages
Declines are not included in the Admit counts
The conditional formatting for the Total column in the Fall 2021 report is not correct

Release 2020.1.4 [December 2020]

Update all PY delta calculations to replace null values with a zero; currently when there is a null, no calculations are performed
Include programs with no defined targets in case there are numbers to be seen
Add Action text to reflect when a target is not defined
Do not show rows in the tables when there are no applications in the pipeline
[SQL ONLY] Added ‘Merit Fellowship’ admits to the admission counts for programs

Release 2020.1.3 [October 2020]

Factor commits into the calculation toward the target; currently only looks at admit and PY yield rate, but we should consider the commit total as a weighted indication that the student will matriculate

New formula to calculate the projected matriculant and action statement:
(Total Commits + Total Deferred Commits) +
((Total Admits - Total Commits) * PY Yield %)
For example, all commits are expected to matriculate, and the previous year yield rate of the uncommitted admits are also expected to matriculate.

Add a Committed Deferral column to the current term to show the other inputs toward the target

Adjust the Action calculation to add the deferrals to the admits

Update the APPL_SLATE table

Remove counter columns that are no longer in use
Add columns to allow for proper deferral counting using new Slate fields

Update the SQL procedure to use the new deferral values to pull counts
Add the export option to the Fall 2020 visualization in the Fall 2021 page

Release 2020.1.2 [September 2020]

Shade the EOT values in the previous year section to make it clear they are NOT CTD

Yield, selectivity, actual enrollments

Strip out all deferrals from the calculations in the current term
Fix the CPF filter not showing properly in the 2021 data
Fix ‘AYI - Online’ values not showing up correctly
Fix issue with ALP in the current term not showing
Add a filter to show/hide rows with zero targets defined
Suppress showing an action narrative when there is a zero target value in the current year data
Streamline the APPL_SLATE data load process
Look closely at the Salesforce table data for Spring and Fall 20 to ensure there are no dupes with the imported Slate apps from those terms
Remove any rows from APPL_EXTENDED where the application was migrated to Slate (submitted and deferrals)
Update the atm_targets table to include the Spring 2021 targets

Update the Spring 2021 targets to include new deferrals (20% projected to matriculate) and the new Q1 projection

Remove the aggregated totals to simplify the view
Add report page for Spring 2021
Add report page for Fall 2021
Remove the Fall 2020 report page to simplify interface
Show zero targets by default
Change report behavior to always show CTD information for all of the primary columns (application funnel points)
- Final enrollments, selectivity, yield, targets

Removed EOY and CTD button and bookmarks since CTD is the default view
Show all of the EOY data points without looking at CTD

Release 2020.1.1 [July 2020]

Need a button to switch back to non-CTD; default view should also be set to non-CTD (currently it looks like it is CTD at startup)
Combine the Program Description and Code into a single measure to ensure a unique row for cases where the description is identical
Add a new report page to show target performance history across all programs
- Targets
- EOT enrollments

Backfill all of the historic enrollment targets into the u_targets_atm table
Add measure to group into user friendly terms (e.g., Spring, Summer, Fall)
Add measure to group by fiscal year
Output field values for five years

Release 2020.1.0 [July 2020]

Display all active programs for the past two years
- The third year data will be used to show +/- for past year only, no detailed counts displayed to ease readability

Underlying recordset should contain all applications for three years * three terms

Pipeline status data points include
- Pending/Under Review (catch-all for anything not covered by others)
- Rejected
- Withdrawn
- Waitlist
- Admits (including to ALP)
- Commits (including from deferrals)

Unsubmitted apps
Submitted apps

Default view displays the End of Cycle counts

Add a bookmark to show Cycle to Date counts

Pull in the generic targets data rather than combining with the enrollments since they do not need to be blended; this will simplify the filtering requirements
Break each page of the report into a specific term for ease of reading and to simplify the design and dataset requirements; use filters to section the data for each page
Show the counts for the current term through the current date, and each previous term should be filtered to cap application created date for the represented year

2018 dataset has a calculated column called ‘OneYearAgo 2018’ and the page is filtered to include only those records from 2018
2019 dataset has a calculated column called ‘OneYearAgo 2019’ and the page is filtered to include only those records from 2019

Calculate the actions to be taken based on the yield rate for each program for the previous year’s term

Sample data question to answer:
“If ERM is aiming for 421 new students this fall, and expects a yield of 50%, it needs to accept 842 students. If ERM believes that 50% of its applicants are worthy of admission, it needs 1,684 applications” [from Louise]
Calculate the expected yield rate based on the previous year’s data (e.g., admits/matriculants) and store in a measure for use in the presentation

Add selectivity as a calculated measure (admits/submitted apps) to the 2019 dataset

Use measure to update the recommended actions for 2020 with more detailed instructions

Create stored procedure to pull counts from the APPL_EXTENDED table for Salesforce sourced data by calendar year
- Combine AL* program codes into a single ALALP code

a7_atm_full_year (@year parameter)

Include a separate page for each term (Fall, Spring, Summer) and configure the filters to ensure the correct data is used in the calculations and presentation
Split out matriculants from deferrals and new applications, calculate yield based only on the new applicants for the term
Add the waitlist count to the Action calculation for projected applicants in the pipeline
Cap projections based on a yield maximum of 100% (no >100% yields)

Factor in the deferrals to get a more accurate yield percentage for normal admits to the current term

Blend in the Salesforce data that overlaps with Slate term data for 2020 (e.g., some SF apps for programs were running in Slate terms, particularly in non-degree)
Resolved: ERM - Online, HCM -- Online, among others, are missing despite having targets defined for Fall 2020
Display programs that are new and do not have previous term data for comparison (e.g., Wealth and Insurance Management)

Since there is no historical data, none of the projections can be done so maybe this report is not the best place to show these programs, but can display the current state without actions

Do not show rows in the current term where there is no defined target or it is set to zero
When there is no previous year data (e.g., for new programs) use a standard selectivity and yield percentage for the projection data (start with 50% for both values)