DARTS : IPEDS 12-month Enrollment Report

Purpose

This IPEDS 12-month enrollment report is a part of IPEDS data collection in Fall.

It is displaying unduplicated student headcounts by institution for the chosen IPEDS year (July 1- June 30). It returns student ethnic group, sex, level, and enrollment status.

Report Location

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

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

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

Report Name

IPEDS 12-month 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.

1. Undergraduate: This page is corresponded to the "Part A - Unduplicated Count for Full-time Undergraduate Students section" on IPEDS instruction. Headcount by category, sex, and ethnicity. This page can be viewed by full-time or part-time status. 
   
   
2. Graduate: This page is corresponded to "Part A- Unduplicated Count for Graduate Students" on IPEDS instruction. Headcount by sex and ethnicity. 
   
   
3. Distance Ed/ Credit hours: This page is corresponded to "Part A– 12-month Enrollment by Distance Education Status" on IPEDS instruction. Headcount by distance education status and level (undergraduate/ graduate), and degree-seeking status for undergraduate students. It also contains "Part B - Instructional Activity" on IPEDS instruction, which has the total credit hours and FTE.
   
   
4. Gender/ Dual Enrolled: This page is corresponded to "Part A-Gender Unknown or Another Gender than Provided Categories" and "Part C – Unduplicated Count of Dual Enrolled Students" on IPEDS instruction. Another gender data is not available yet.
   
   
5. EMPLID Search (For restricted version only): This page is used for data validation. The data can be searched by EMPLID. 
   
   
6. Data Validation (For restricted version only): This page is used for data validation. The data can be searched by IPEDS year, level, full/part time, and degree/non-degree status.
   
   
7. Credit hours validation (For restricted version only): This page is used for data validation for credit hours including cross-listed courses hours.

Data Source Table / Views

A couple of tables and views have been created for this report in SQL Server. 

1. CS_Multi_Term_Tbl : This is the base table and has list of courses students enrolled along with students' demographic data.  IPEDS_YEAR was added based on course start date from July 1 through June 30 of the year. For example, IPEDS_YEAR of "2021" indicates class start date is between 7/1/2020 and 6/30/2021. New IPEDS year will be loaded after summer census. 
   PeopleSoft table used are:
   1. UM_STUD_CENS_VW - Student Reporting Extract - getting student's demographic data
   2. UM_STD_ENR_C_VW - Student Enrollment Table - getting student's course enrollment data
   3. CLASS_TBL - Class Table - getting course start date
   4. UM_STDNT_COHORT - Student Cohort - getting cohort type (first-time/ transfer)
   5. SQL for CS_Multi_Term_Tbl (IPEDS Year 2021 example) Expand source

      SELECT DISTINCT
        A.STRM,
        '2021' IPEDS_YEAR,
        A.INSTITUTION,
        A.UM_INST_HOST,
        A.EMPLID,
        A.ACAD_PROG_PRIMARY,
        A.UM_DEGREE_NONDEG,
        A.UM_ETHNIC_GRP_DESC,
        A.Acad_Level_Bot,
        A.Um_Unt_Tak_Prg_Rc,
        A.SEX,
        A.ACAD_CAREER,
        B.STDNT_ENRL_STATUS,
        B.CRSE_CAREER,
        A.ADMIT_TERM,
        A.ADMIT_TYPE,
        A.RESIDENCY,
        A.ACAD_PROG,
        A.ACAD_PLAN,
        A.UM_ACAD_PLAN_DESCR,
        A.CITY,
        A.STATE,
        A.STATE_DESCR1,
        A.POSTAL,
        A.COUNTRY,
        A.COUNTRY_DESCR1,
        B.SUBJECT,
        B.CATALOG_NBR,
        B.CLASS_SECTION,
        B.CLASS_NBR,
        B.DESCR,
        B.ACAD_ORG,
        B.UM_ACAD_ORG_DESCR,
        B.UNT_PRGRSS,
        B.UNT_PRGRSS_FA,
        B.CAMPUS,
        B.LOCATION,
        B.DESCR_LOCATION,
        B.um_class_dlv_mode,
        B.um_dist_class,
        B.UM_DIST_ATTR_FLAG,
        B.INSTRUCTION_MODE,
        E.START_DT,
        F.Um_Cohort_Type,
        A.Degree,
        CASE 
          WHEN F.Um_Cohort_Type in ('FYR', 'EFY') then 'First-time'
          WHEN F.Um_Cohort_Type = 'TRF' then 'Transfer-in'
          else 'Continuing'
        end as cohort_category,
        'CS_IPEDS_MT' DI_Job_Pid,
        sysdate DI_Create_Date,
        sysdate DI_Modified_Date
        FROM SYSADM.PS_UM_STUD_CENS_VW A
        LEFT OUTER JOIN SYSADM.PS_UM_STD_ENR_C_VW B
          ON A.EMPLID = B.EMPLID
         AND A.INSTITUTION = B.INSTITUTION
         AND A.STRM = B.STRM
        LEFT OUTER JOIN SYSADM.PS_CLASS_TBL E
          ON B.INSTITUTION = E.INSTITUTION
         AND B.STRM = E.STRM
         AND B.CLASS_NBR = E.CLASS_NBR
        LEFT OUTER JOIN SYSADM.PS_um_stdnt_cohort F
          on A.INSTITUTION = F.INSTITUTION
         and A.ACAD_CAREER = F.ACAD_CAREER
         and A.emplid = F.EMPLID
         and A.strm = F.Um_Cohort_Strm
         and F.um_cohort_code='RETENTION'
         and ((F.action_dt = (SELECT MAX(F_ED.action_dt) 
                                FROM sysadm.ps_um_stdnt_cohort F_ED
                               WHERE F.INSTITUTION = F_ED.INSTITUTION
                                 and F.ACAD_CAREER = F_ED.ACAD_CAREER
                                 and F.EMPLID = F_ED.EMPLID
                                 and F.UM_COHORT_STRM = F_ED.UM_COHORT_STRM
                                 and F.um_cohort_code = F_ED.um_cohort_code
                                 and F_ED.action_dt <= SYSDATE)) or F.action_dt is null)
       WHERE A.STRM BETWEEN '2030' AND '2130'
         AND E.START_DT BETWEEN TO_DATE('20' || substr('2030', 1, 2) || '-07-01','YYYY-MM-DD') AND TO_DATE('20' || substr('2130', 1, 2) || '-06-30','YYYY-MM-DD')
       

2. CS_MTT_Emplid_Listing_VW : Getting a list of EMPLID with student's first term based on the hierarchy for each IPEDS year. The hierarchy used is fall > spring > summer of the first year > summer of the second year. UM and UMM data have been combined.

   SQL for CS_MTT_Emplid_Listing_VW Expand source

   select distinct
     d.emplid,
     d.ipeds_year,
     d.INST_COMBINED,
     substring(d.min_strm, 2, 4) First_Full_STRM
     from (SELECT
             a.EMPLID,
             a.IPEDS_YEAR,
    CASE WHEN a.INSTITUTION = 'UMS04' THEN 'UMS05' ELSE a.INSTITUTION END as INST_COMBINED,
             min(c.strm_order) min_strm
             FROM [UMS_CS_DM_DEV].[dbo].[CS_Multi_Term_Tbl] a
             left outer join (select
                                b.emplid,
           b.ipeds_year,
           b.INSTITUTION,
           substring(b.STRM, 3, 1) + b.STRM strm_order
                                from [UMS_CS_DM_DEV].[dbo].[CS_Multi_Term_Tbl] b) c
               on a.EMPLID = c.Emplid
              and a.ipeds_year = c.ipeds_year
              and a.INSTITUTION = c.INSTITUTION
           group by a.EMPLID, a.IPEDS_YEAR, CASE WHEN a.INSTITUTION = 'UMS04' THEN 'UMS05' ELSE a.INSTITUTION END) d

   
   

3. CS_MTT_Distance_Count_VW : It indicates student's distance education status. In order to get the status, it compared the total course count for distance only with total course count during the IPEDS year per student by institution. UM and UMM data have been combined.

   SQL for CS_MTT_Distance_Count_VW Expand source

   SELECT
   b.EMPLID,
   b.IPEDS_YEAR,
   b.INST_COMBINED,
   b.DISTANCE_COUNT,
   b.TOTAL_COUNT,
   (CASE WHEN b.DISTANCE_COUNT = b.TOTAL_COUNT THEN 'Distance Only'
         WHEN b.DISTANCE_COUNT = 0 THEN 'In-person Only'
         ELSE 'Both'
          END) as DIST_CATEGORY
   FROM (
         SELECT
         EMPLID,
         IPEDS_YEAR,
         CASE WHEN INSTITUTION= 'UMS04' THEN 'UMS05' ELSE INSTITUTION END as INST_COMBINED,
         SUM(CASE WHEN UM_DIST_CLASS = 'NOT DIST' THEN 0 ELSE 1 END) as DISTANCE_COUNT,
         COUNT(*) as TOTAL_COUNT
         FROM [UMS_CS_DM_DEV].[dbo].[CS_Multi_Term_Tbl]
         GROUP BY
         EMPLID,
         IPEDS_YEAR,
         CASE WHEN INSTITUTION= 'UMS04' THEN 'UMS05' ELSE INSTITUTION END
        ) b

4. CS_MTT_Student_Demographic_VW : Returning student's demographic data (sex, ethnicity, acad_career, um_degree_nondeg) and credit hours for student per term. When a student enrolled in both UM and UMM, the data from student's degree institution per term has been selected.

   SQL for CS_MTT_Student_Demographic_VW Expand source

   select
     a.EMPLID,
     a.STRM,
     a.IPEDS_YEAR,
     a.INST_COMBINED,
     a.SEX,
     a.UM_ETHNIC_GRP_DESC,
     a.ACAD_CAREER,
     a.UM_DEGREE_NONDEG,
     a.COHORT_CATEGORY,
     a.UNT_PRGRSS,
     case
       when a.ACAD_PROG_PRIMARY in ('NDEC', 'NECN') then 'Y'
   	else 'N'
     end as Early_Coll
     from (select
             f.EMPLID,
             f.STRM,
             f.IPEDS_YEAR,
             f.institution as INST_COMBINED,
             f.acad_prog_primary,
             MIN(f.SEX) SEX,
             MAX(f.UM_ETHNIC_GRP_DESC) UM_ETHNIC_GRP_DESC,
             MIN(f.ACAD_CAREER) ACAD_CAREER,
             MIN(f.UM_DEGREE_NONDEG) UM_DEGREE_NONDEG,
             MAX(f.COHORT_CATEGORY) COHORT_CATEGORY,
             SUM(f.UNT_PRGRSS) UNT_PRGRSS
            from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] f
           where f.INSTITUTION not in ('UMS05','UMS04')
          group by f.EMPLID, f.STRM, f.IPEDS_YEAR, f.institution, f.acad_prog_primary
   
   union
   
   select
     c.EMPLID,
     c.STRM,
     c.IPEDS_YEAR,
     c.acad_prog_primary,
     'UMS05' as INST_COMBINED,
     MIN(c.SEX) SEX,
     MAX(c.UM_ETHNIC_GRP_DESC) UM_ETHNIC_GRP_DESC,
     MIN(c.ACAD_CAREER) ACAD_CAREER,
     MIN(c.UM_DEGREE_NONDEG) UM_DEGREE_NONDEG,
     MAX(c.COHORT_CATEGORY) COHORT_CATEGORY,
     e.UNT_PRGRSS  from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] c
     left outer join (Select
                        d.EMPLID,
                        d.STRM,
                        d.IPEDS_YEAR,
                        SUM(d.UNT_PRGRSS) UNT_PRGRSS
                        from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] d
                       where d.INSTITUTION in ('UMS05','UMS04')
                      group by EMPLID, STRM, IPEDS_YEAR) e
       on c.emplid       = e.EMPLID
      and c.strm         = e.strm
      and c.IPEDS_YEAR   = e.IPEDS_YEAR
    where c.INSTITUTION in ('UMS05','UMS04')
      and c.UM_DEGREE_NONDEG = (select min(aaa.UM_DEGREE_NONDEG)
                                  from [UMS_CS_DM_PRD].[dbo].[CS_Multi_Term_Tbl] aaa
                                 where c.EMPLID      = aaa.EMPLID
                                   and c.STRM        = aaa.STRM
                                   and aaa.INSTITUTION in ('UMS05','UMS04'))
   group by c.EMPLID, c.STRM, c.IPEDS_YEAR, e.UNT_PRGRSS, c.acad_prog_primary) A

Another table, Credit hours based on UM_INST_HOST, was created in Power BI to validate cross-listed courses hours. The fields are from CS_Multi_Term_Tbl and UM and UMM were combined.