DARTS : IPEDS Fall Enrollment Report

Purpose

This report is used for a part of IPEDS fall enrollment report displaying headcounts by institution for the chosen term. It returns student ethnic group, gender, level, and enrollment status.

The report is as of census day, however, the most recent term will not be ready on the census day as cohort table is not completed.

Report Location

Power BI App (IR Share) : Restricted to IR. Row Level Security is set by institution.

Power BI App (UMS Reports) : Open all UMS personnel. This version contains fewer pages.

Please contact us at DARTS@maine.edu for requesting new access.

Report Name

IPEDS Fall Enrollment Report

Report Type

Power BI

Pages

There are multiple pages on the report. Please click the list of page names on the left column of the report site. Only one term can be selected at a time on each page.

Undergraduate - Headcount by category, gender, and ethnicity. This page can be viewed by full-time/ part-time status or CIP code when filtering on the slicer at the top of the page.
Graduate - Headcount by gender, ethnicity and full-time/ part-time status. This page can be viewed by CIP code when filtering on the slicer at the top of the page.
Gender (For restricted to IR version only) - Gender unknown and another gender data on this page.
Distance Education - Headcount by distance education status. This page can be viewed by CIP code when filtering on the slicer at the top of the page. The data is available from fall 2021 and forward due to the change in UM_DIST_CLASS (Summer 2021) field.
Age - Headcount by age group, academic level, gender, and full-time/ part-time status. This page can be viewed by CIP code when filtering on the slicer at the top of the page.
Residence of first time - Headcount by state of residence when student was first admitted for first-time degree/ certificate -seeking undergraduates.
Data Validation (For restricted to IR version only) - This page was created for data validation purposes. You can search by EMPLID or narrow down data by filtering each slicer. You can download the data to Excel.
Data Validation distnce ed (For restricted to IR version only) - This page was created for data validation purposes. You can download the data to Excel.

Click the logo above for certification details, including any known issues, of this report.

HELP IS AT HAND!

Link to compose email for help with website.

Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.

Historical Reports:

This report was historically ran with IPEDS Student Report SQR (umipdstu.sqr) and UM IPEDS Distance Ed Report SQR (umipedde.sqr).

Report Development Notes (restricted):

Location (UMS Data Governance> Data Advisory Committee (DAC) > Workgroup folders > Completed Project Workgroup > Data Certification: IPEDS Fall Enrollment Report> Proposals & Process)

Outside Resource:

IPEDS Fall Enrollment Survey Materials 2021-22

Data Fields Generated

Below is an example of "Undergraduate" page on the report.

Data Source

Here is the list of tables/ views used for this report.

The UM_STUD_CENS_VW was used for most of the fields. It is filtered with A.ACAD_CAREER <> 'NCRD' , A.ROW_CHECK = 'Y' , and A.STRM>='1710'
The ACAD_PLAN_TBL was used for CIP code.
The UM_STDNT_COHORT was used for calculating Category.
The UM_STD_ENR_C_VW was used for Distance education page.
The UM_APPL_EXTRACT was used for Residence of the first time undergraduate page.
The EXT_DEGREE was used to get graduation date for the first time undergraduates
The SCC_PERS_BIOG was used to get another gender data.

The SQL for getting fields for student demographic is below. UM and UMM are reported under one institution and students were combined based on their degree-institution. For example, when a student were enrolled in both UM and UMM, the demographic information was pulled from the institution where the student is degree-seeking. The credit hours were summed up.

SQL for getting student related fields

select
  a.EMPLID,
  a.STRM,
  a.institution,
  MIN(a.SEX) SEX,
  MAX(a.UM_ETHNIC_GRP_DESC) UM_ETHNIC_GRP_DESC,
  MIN(a.ACAD_CAREER) ACAD_CAREER,
  MAX(a.ACAD_PLAN) ACAD_PLAN,
  MAX(c.CIP_CODE) CIP_CODE,
  MIN(a.UM_DEGREE_NONDEG) UM_DEGREE_NONDEG,
  MAX(a.birthdate) birthdate,
  MAX(a.um_last_sch_type) um_last_sch_type,
  MIN(e.degree_dt) HS_Graduation_dt, 
  MIN(d.Um_Cohort_Type) Um_Cohort_Type,
  MAX(b.state) Adm_State,
  MAX(b.COUNTRY) ADM_COUNTRY,
  MAX(a.STATE) STATE,
  MAX(a.COUNTRY) COUNTRY,
  SUM(a.Um_Unt_Tak_Prg_Rc) UNT_PRGRSS
  from sysadm.ps_um_stud_cens_vw a
  left outer join sysadm.ps_um_appl_extract b
    on b.INSTITUTION   = a.INSTITUTION
   and b.acad_career   = a.acad_career
   and b.emplid        = a.emplid
   and b.adm_appl_nbr  = a.adm_appl_nbr
   and b.stdnt_car_nbr = a.stdnt_car_nbr
  left outer join sysadm.ps_acad_plan_tbl c 
    on c.institution = a.Institution 
   and c.acad_plan= a.acad_plan 
   and ((c.effdt = (SELECT MAX(c2.EFFDT) 
                      FROM SYSADM.PS_ACAD_PLAN_TBL c2
                     WHERE c.INSTITUTION = c2.INSTITUTION
                       and c.ACAD_PLAN = c2.ACAD_PLAN
                       and c2.EFFDT <= SYSDATE)) or c.effdt is null)
  left outer join sysadm.ps_um_stdnt_cohort d 
    on a.INSTITUTION = d.INSTITUTION 
   and a.ACAD_CAREER=d.ACAD_CAREER 
   and a.emplid = d.EMPLID 
   and a.strm = d.Um_Cohort_Strm 
   and d.um_cohort_code='RETENTION'
   and ((d.action_dt = (SELECT MAX(d2.action_dt) 
                          FROM sysadm.ps_um_stdnt_cohort d2
                         WHERE d.INSTITUTION = d2.INSTITUTION
                           and d.ACAD_CAREER = d2.ACAD_CAREER 
                           and d.EMPLID = d2.EMPLID
                           and d.UM_COHORT_STRM = d2.UM_COHORT_STRM
                           and d.um_cohort_code = d2.um_cohort_code
                           and d2.action_dt <= SYSDATE)) or d.action_dt is null)
  left outer join (select emplid, min(degree_DT) as degree_dt from sysadm.ps_ext_degree f
                   where f.degree_status = 'C' and f.degree in ('HSD', 'GED', 'HISET') 
                   group by f.emplid) e on a.emplid = e.emplid
where a.INSTITUTION NOT in ('UMS05','UMS04')
   and a.strm > = '1710' 
   and a.strm like '%10'
  group by a.EMPLID, a.STRM, a.institution

union

select
  a.EMPLID,
  a.STRM,
  'UMS45' as INST_COMBINED,
  MIN(a.SEX) SEX,
  MAX(a.UM_ETHNIC_GRP_DESC) UM_ETHNIC_GRP_DESC,
  MIN(a.ACAD_CAREER) ACAD_CAREER,
  MAX(a.ACAD_PLAN) ACAD_PLAN,
  MAX(c.CIP_CODE) CIP_CODE,
  MIN(a.UM_DEGREE_NONDEG) UM_DEGREE_NONDEG,
  MAX(a.birthdate) birthdate,
  MAX(a.um_last_sch_type) um_last_sch_type,
  MIN(e.degree_dt) HS_Graduation_dt, 
  MIN(d.Um_Cohort_Type) Um_Cohort_Type,
  MAX(b.state) Adm_State,
  MAX(b.COUNTRY) ADM_COUNTRY,
  MAX(a.STATE) STATE,
  MAX(a.COUNTRY) COUNTRY,
  a3.UNT_PRGRSS
  from sysadm.ps_um_stud_cens_vw a
  left outer join (Select
                     a2.EMPLID,
                     a2.STRM,
                     SUM(a2.Um_Unt_Tak_Prg_Rc) UNT_PRGRSS
                     from sysadm.ps_um_stud_cens_vw a2
                    where a2.INSTITUTION in ('UMS05','UMS04')
                   group by a2.EMPLID, a2.STRM) a3
    on a.emplid       = a3.EMPLID
   and a.strm         = a3.strm
  left outer join sysadm.ps_um_appl_extract b
    on b.INSTITUTION   = a.INSTITUTION
   and b.acad_career   = a.acad_career
   and b.emplid        = a.emplid
   and b.adm_appl_nbr  = a.adm_appl_nbr
   and b.stdnt_car_nbr = a.stdnt_car_nbr
  left outer join sysadm.ps_acad_plan_tbl c 
    on c.institution = a.Institution 
   and c.acad_plan= a.acad_plan 
   and ((c.effdt = (SELECT MAX(c2.EFFDT) 
                      FROM SYSADM.PS_ACAD_PLAN_TBL c2
                     WHERE c.INSTITUTION = c2.INSTITUTION
                       and c.ACAD_PLAN = c2.ACAD_PLAN
                       and c2.EFFDT <= SYSDATE)) or c.effdt is null)
  left outer join sysadm.ps_um_stdnt_cohort d 
    on a.INSTITUTION = d.INSTITUTION 
   and a.ACAD_CAREER=d.ACAD_CAREER 
   and a.emplid = d.EMPLID 
   and a.strm = d.Um_Cohort_Strm 
   and d.um_cohort_code='RETENTION'
   and ((d.action_dt = (SELECT MAX(d2.action_dt) 
                          FROM sysadm.ps_um_stdnt_cohort d2
                         WHERE d.INSTITUTION = d2.INSTITUTION
                           and d.ACAD_CAREER = d2.ACAD_CAREER 
                           and d.EMPLID = d2.EMPLID
                           and d.UM_COHORT_STRM = d2.UM_COHORT_STRM
                           and d.um_cohort_code = d2.um_cohort_code
                           and d2.action_dt <= SYSDATE)) or d.action_dt is null)
  left outer join (select emplid, min(degree_DT) as degree_dt from sysadm.ps_ext_degree f
                   where f.degree_status = 'C' and f.degree in ('HSD', 'GED', 'HISET') 
                   group by f.emplid) e on a.emplid = e.emplid
where a.INSTITUTION in ('UMS05','UMS04')
   and a.UM_DEGREE_NONDEG = (select min(a4.UM_DEGREE_NONDEG)
                               from sysadm.ps_um_stud_cens_vw a4
                              where a.EMPLID      = a4.EMPLID
                                and a.STRM        = a4.STRM
                                and a4.INSTITUTION in ('UMS05','UMS04'))
   and a.strm > = '1710' 
   and a.strm like '%10'
group by a.EMPLID, a.STRM, a3.UNT_PRGRSS

UM's UM_FT_OVERRIDE logic

SELECT
Distinct
A.EMPLID,
A.STRM,
'UMS45' as INST_COMBINED,
A.ACAD_CAREER,
A.DOC_MA,
A.Candidacy,
B.TOTAL_CREDIT,
C.TOTAL_THESIS,
(CASE WHEN B.TOTAL_CREDIT >= 12 THEN 'Full-Time'
      WHEN A.DOC_MA in ('Doctorate', 'Masters') AND B.TOTAL_CREDIT >= 9 THEN 'Full-Time'
      WHEN A.DOC_MA in ('Doctorate', 'Masters') AND B.TOTAL_CREDIT >= 6 AND C.TOTAL_THESIS>= 3 THEN 'Full-Time'
      WHEN A.DOC_MA = 'Doctorate' AND A.Candidacy = 'Y' AND C.TOTAL_THESIS >= 1  THEN 'Full-Time'
      ELSE 'Part-time' END) as UM_FT_OVERRIDE
FROM (Select
distinct 
a1.EMPLID,
a1.STRM,
a1.FORM_OF_STUDY,
(CASE WHEN a1.FORM_OF_STUDY = 'CAND' THEN 'Y' ELSE 'N' END) as candidacy,
a1.acad_career,
a1.ACAD_PLAN,
a1.DEGREE,
(CASE WHEN a1.DEGREE IN ('PHD', 'EDD') THEN 'Doctorate'
                       WHEN a1.ACAD_PLAN LIKE '%-PHD' THEN 'Doctorate'
                       WHEN a1.ACAD_PLAN LIKE '%-EDD' THEN 'Doctorate'
                       WHEN a1.ACAD_CAREER = 'GRAD' THEN 'Masters'
                     ELSE ACAD_CAREER 
                   END) as DOC_MA,
'UMS45' as INST_COMBINED
from sysadm.ps_um_stud_cens_vw a1
where a1.INSTITUTION in ('UMS05','UMS04')
   and a1.UM_DEGREE_NONDEG = (select min(a4.UM_DEGREE_NONDEG)
                               from sysadm.ps_um_stud_cens_vw a4
                              where a1.EMPLID      = a4.EMPLID
                                and a1.STRM        = a4.STRM
                                and a4.INSTITUTION in ('UMS05','UMS04'))
   and a1.strm > = '1910' 
   and a1.strm like '%10') A
LEFT JOIN (SELECT
        EMPLID,
        STRM,
        SUM(UNT_PRGRSS) as TOTAL_CREDIT
        FROM sysadm.ps_um_std_enr_c_vw
       WHERE INSTITUTION IN ('UMS04','UMS05') 
         AND STRM > = '1910'
      GROUP BY EMPLID, STRM) B
  ON A.EMPLID = B.EMPLID
 AND A.STRM   = B.STRM

LEFT JOIN (SELECT
             EMPLID,
             STRM,
             SUM(UNT_PRGRSS) as TOTAL_THESIS
             FROM sysadm.ps_um_std_enr_c_vw
            WHERE INSTITUTION IN ('UMS04','UMS05') 
              AND (CATALOG_NBR LIKE '%699%' OR SSR_COMPONENT = 'THS')
              AND STRM > = '1910'
           GROUP BY EMPLID, STRM) C
  ON A.EMPLID = C.EMPLID
 AND A.STRM   = C.STRM

Field on report	Field used from table or Calculation (alias A= UM_STUD_CENS_VW , B= UM_APPL_EXTRACT , C= ACAD_PLAN_TBL , D= UM_STDNT_COHORT, E= UM_STD_ENR_C_VW, F = EXT_DEGREE, and G=SCC_PERS_BIOG )	More information
Campus	A.INSTITUTION Students who enrolled both UM and UMM are combined based on their degree-institution.	Crosswalk table created in Power BI.
Term	A.STRM	Fall terms only
Level	each if [A.ACAD_CAREER] = "UGRD" then "Undergraduate" else "Graduate"	Sort order: Undergraduate =1, Graduate =2
Full/ Part time	if [UM_FT_OVERRIDE] = "Full-Time" then "Full-Time" each if [A. ACAD_CAREER] = "GRAD" and [UNT_PRGRSS] >= 9 then "Full-Time" else if [UNT_PRGRSS] >= 12 then "Full-Time" else "Part-Time"	UNT_PRGRSS is SUM(Um_Unt_Tak_Prg_Rc) from UM_STUD_CENS_VW UM_FT_OVERRIDE is based on below. These students are added as full-time. For Masters and Doctoral students: 9 credits or a minimum of 3 thesis credits out of 6 total credits. For Doctoral students in candidacy: At least 1 thesis credit.
GENDER	each if [A.SEX] = "M" then "Men" else "Women"
Another gender	A.SEX <> "U" and G.SCC_GENDER_ID NOT IN (blank, MA, PN, WO, or US)	Crosswalk for SCC_GENDER_ID
ETHNICITY	A.UM_ETHNIC_GRP_DESC	Sort order: NonResident Alien Hispanic/Latino American Indian/ Alaskan Native Asian Black/ African American Native Hawaiian/ Pacific Islander White Two or More Races Unknown
Category (First-time/ Transfer-in/ Continuing/ Non-degree)	each if [UM_COHORT_TYPE] = "FYR" then "First-time" else if [UM_COHORT_TYPE] = "EFY" then "First-time" else if [UM_COHORT_TYPE] = "TRF" then "Transfer-in" else if [UM_DEGREE_NONDEG] = "Non-Degree" then "Non-Degree" else "Continuing"	Sort order: First-time Transfer-in Continuing Non-Degree The logic has been changed from the legacy Student SQR report, and only works for undergraduate students as it is sourced from the Cohort table. The prior SQR used admit_type from UM_STUD_CENS_VW, which was creating issues for some students with multiple applications. For more information, please go to Issues on UM_STUD_EXTRACT.
Degree/ certificate seeking	each if [Category] = "Non-Degree" then "Non-Degree/non-certificate-seeking" else "Degree/certificate-seeking"
CIP CODE	C.CIP_CODE of A.ACAD_PLAN	Picked MAX (C.EFFDT) within institution and acad_plan
CIP (2 digits)	First two characters of CIP CODE field above
Age	Based on A.BIRTHDATE as of census RoundDown(([Term.Census date]- [A.BIRTHDATE])/365.25)	Age category follows IPEDS instructions each if [Age at census] = null then "Age unknown" else if [Age at census] < 18 then "Under 18" else if [Age at census] <= 19 then "18-19" else if [Age at census] <= 21 then "20-21" else if [Age at census] <= 24 then "22-24" else if [Age at census] <= 29 then "25-29" else if [Age at census] <= 34 then "30-34" else if [Age at census] <= 39 then "35-39" else if [Age at census] <= 49 then "40-49" else if [Age at census] <= 64 then "50-64" else "65 and over"
Distance Education Category	steps to get the category (E= UM_STD_ENR_C_VW) Created new column for 0 or 1 based on E.UM_DIST_CLASS (1 indicates as distance : each if [E.UM_DIST_CLASS] = "NOT DIST" then 0 else 1) Get total course count by student, term, and institution Get total distance course count by student, term, and institution Identify Distance category each if [Distance Count] = [Total Count] then "Distance only else if [Distance Count] = 0 then "In-person only" else "Both"
Distance Education Location	It is based on A.STATE and A.COUNTRY from UM_STUD_CENS_VW each if [STATE] = " " and [COUNTRY] = " " then "Unknown" else if [STATE] = " " and[COUNTRY] = "USA" then "Unknown USA" else if [STATE] = "ME" then "same state" else if [COUNTRY] = "USA" then "not in the same state" else if [COUNTRY] <> "USA" then "outside USA" else "check"	This data can be used for NC-SARA report. Go to "data validation -distance ed" page to download the data.
Residence of first-time	It is based on B.STATE and B.COUNTRY from UM_APPL_EXTRACT (renamed B.STATE to ADM_STATE and B.COUNTRY to ADM_COUNTRY) Created a state lookup based on state abbreviation Get state description based on B.STATE field (new field called "Admission Description") Created a category following IPEDS report category each if [ADM_STATE] = null and [ADM_COUNTRY] = null then "Residence unknown" else if [Admission Description] = null and [ADM_COUNTRY] = "USA" then "State Unknown" else if [ADM_COUNTRY] = "USA" and [Admission Description] <> " " then [Admission Description] else if [ADM_COUNTRY] <> "USA" then "Foreign Counties" else "check"
Enrolled within 12 months of high school graduation	It is based on F. DEGREE_DT from EXT_DEGREE table with F.degree_status = 'C' and F.degree in ('HSD', 'GED', 'HISET')