Power BI Report - Graduate Admission Report

Contact

• Report Updates
  • Department: Institutional Effectiveness
  • Org Chart Link: https://www.clarkson.edu/organizational-chart?depart=86
• Report Updates 
  • Department: Office of the Dean - CRC
  • User: Amy Nevin
  • Org Chart Link: https://directories.clarkson.edu/orgchart/index.php?username=anevin
• Data Structure Updates
  • Department: OIT
  • User: helpdesk@clarkson.edu

Definitions

• Academic Year
  • Definition: An academic year spans Summer to Spring. Academic Year 2023-24 includes Summer 2023, Summer Quarter 2023, Fall 2023, Fall Quarter 2023, Winter Quarter 2024, Spring 2024 and Spring Quarter 2024. 
  • SQL Logic: SELECT DISTINCT CONCAT(ACAD_YEAR-1, '-', SUBSTRING(ACAD_YEAR, 2, 2)) FROM PS_TERM_TBL FILL where FILL.STRM IN (SELECT DISTINCT ADMIT_TERM FROM PS_CU_GRAD_ADM) AND FILL.ACAD_CAREER IN (SELECT DISTINCT ACAD_CAREER FROM PS_CU_GRAD_ADM) ORDER BY 1 DESC;
• Academic Career
  
  • Definition: 
  • SQL Logic: SELECT ACAD_CAREER, DESCR FROM PS_ACAD_CAR_TBL where ACAD_CAREER IN (SELECT DISTINCT ACAD_CAREER FROM PS_CU_GRAD_ADM)
• Academic Program
  • Definition: 
  • SQL Logic: SELECT DISTINCT ACAD_PROG, DESCR FROM PS_ACAD_PROG_TBL FILL where FILL.ACAD_PROG IN (SELECT DISTINCT ACAD_PROG FROM PS_CU_GRAD_ADM) AND FILL.EFF_STATUS = 'A' AND FILL.EFFDT = (SELECT MAX(FILL_ED.EFFDT) FROM PS_ACAD_PROG_TBL FILL_ED WHERE FILL_ED.ACAD_PROG = FILL.ACAD_PROG) ORDER BY FILL.DESCR
• Academic Plan
  • Definition:
  • SQL Logic: SELECT DISTINCT ACAD_PLAN, DESCR FROM PS_ACAD_PLAN_TBL FILL where FILL.ACAD_PLAN IN (SELECT DISTINCT ACAD_PLAN FROM PS_CU_GRAD_ADM) AND FILL.EFFDT = (SELECT MAX(FILL_ED.EFFDT) FROM PS_ACAD_PLAN_TBL FILL_ED WHERE FILL_ED.ACAD_PLAN = FILL.ACAD_PLAN AND FILL_ED.EFFDT<=GetDate())
• Student Types
  • International
    • Definition: Student indicated on their application that they are a Foreign National for their citizenship status
    • SQL Logic: SELECT * FROM PS_CU_GRAD_ADM WHERE ISNULL(CU_ADM_INTL_STATUS, '') = 'FN'
  • Domestic
    • Definition: Student indicated on their application that they are a US Citizen or a Permanent Resident on their application
    • SQL Logic: SELECT * FROM PS_CU_GRAD_ADM WHERE ISNULL(CU_ADM_INTL_STATUS, '') <> 'FN'
• Prospects
  • Definition: Any student that has filled out a Request for More Information form for a specific term, or has applied to that term. 
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where ( (A.CU_ADM_INQL_DT IS not NULL and A.CU_ADM_INQL_DT > '1900-01-01') or (A.CU_ADM_APPL_DT IS not NULL and A.CU_ADM_APPL_DT > '1900-01-01'))
    
• Apps
  • Definition: Submitted an application. 
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_ADM_APPL_DT > '1900-01-01'
    
    • CU_ADM_APPL_DT is derived from the following logic:
      • update ADM
         SET ADM.CU_ADM_APPL_DT = PLN.ACTION_DT
        from PS_CU_GRAD_ADM ADM, PS_ADM_APPL_PROG PLN
        where ADM.EMPLID = PLN.EMPLID
         and ADM.ADM_APPL_NBR = PLN.ADM_APPL_NBR
         and PLN.PROG_ACTION = 'APPL'
         and PLN.EFFDT = (
            select MIN(PLN_ED.EFFDT)
            from PS_ADM_APPL_PROG PLN_ED
            where PLN_ED.EMPLID = PLN.EMPLID
                and PLN_ED.ADM_APPL_NBR = PLN.ADM_APPL_NBR
                and PLN_ED.PROG_ACTION = PLN.PROG_ACTION
         )
         and ADM.CU_ADM_APPL_DT IS NULL;
• Comp. Apps
  • Definition: Completed all the requirements for the application to be reviewed
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where  A.CU_ADM_COMP_DT IS not NULL  and A.CU_ADM_COMP_DT > '1900-01-01'
    
    • CU_ADM_COMP_DT is derived from the following logic:
      • Previous to Fall 2017 applications, if the date the application ended up in the Review Bin in Nolij
      • From Fall 2017 and on, in Slate, the date the application moved out of the Quality Review bin and into the workflow is the date used to determine if it was completed or not. 
• Admits
  • Definition: Has an admit decision on their application
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_ADM_ADMT_DT IS not NULL and A.CU_ADM_ADMT_DT > '1900-01-01'
    
    • CU_ADM_ADMT_DT is derived from the following logic:
      • update ADM
         SET ADM.CU_ADM_ADMT_DT = PLN.ACTION_DT
        from PS_CU_GRAD_ADM ADM, PS_ADM_APPL_PROG PLN
        where ADM.EMPLID = PLN.EMPLID
         and ADM.ADM_APPL_NBR = PLN.ADM_APPL_NBR
         and PLN.PROG_ACTION = 'ADMT'
         and PLN.EFFDT = (
            select MIN(PLN_ED.EFFDT)
            from PS_ADM_APPL_PROG PLN_ED
            where PLN_ED.EMPLID = PLN.EMPLID
                and PLN_ED.ADM_APPL_NBR = PLN.ADM_APPL_NBR
                and PLN_ED.PROG_ACTION = PLN.PROG_ACTION
         )
         and ADM.CU_ADM_ADMT_DT IS NULL;
    • NOTE: If application doesn't have an ADMT action, but has a DEIN or MATR, then the date will fill in using the DEIN or MATR, whichever is earlier as the ADMT date. 
• Denies
  • Definition: Current application status is in DENY
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_CURRENT_STATUS ='DENY'
    
    • CU_CURRENT_STATUS is derived from the following logic:
      • Update based on PROG_ACTION with the max effective dated row in PS_ADM_APPL_PROG for that EMPLID and ADM_APPL_NBR
• Paids
  • Definition: Student has a DEIN decision on their application
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_ADM_DEIN_DT IS not NULL and A.CU_ADM_DEIN_DT > '1900-01-01'
    
    • CU_ADM_DEIN_DT is derived from the following logc:
      • update ADM
         SET ADM.CU_ADM_DEIN_DT = PLN.ACTION_DT
        from PS_CU_GRAD_ADM ADM, PS_ADM_APPL_PROG PLN
        where ADM.EMPLID = PLN.EMPLID
         and ADM.ADM_APPL_NBR = PLN.ADM_APPL_NBR
         and PLN.PROG_ACTION = 'DEIN'
         and PLN.EFFDT = (
            select MIN(PLN_ED.EFFDT)
            from PS_ADM_APPL_PROG PLN_ED
            where PLN_ED.EMPLID = PLN.EMPLID
                and PLN_ED.ADM_APPL_NBR = PLN.ADM_APPL_NBR
                and PLN_ED.PROG_ACTION = PLN.PROG_ACTION
         )
         and ADM.CU_ADM_DEIN_DT IS NULL;
    • NOTE: If application doesn't have an DEIN action, but has a MATR, then the date will fill in using the MATR as the DEIN date. 
• Declines
  • Definition: Student withdrew their application, and hasn't deferred, or withdrew after they deferred admissions
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where ((A.CU_ADM_WADM_DT IS not NULL and A.CU_ADM_DEFR_DT is null) or (A.CU_ADM_WADM_DT IS not NULL and A.CU_ADM_WADM_DT > A.CU_ADM_DEFR_DT)) and A.CU_ADM_WADM_DT > '1900-01-01'
    • CU_ADM_DEFR_DT is derived from the following logic:
      • update ADM
         SET ADM.CU_ADM_DEFR_DT = PLN.ACTION_DT
        from PS_CU_GRAD_ADM ADM, PS_ADM_APPL_PROG PLN
        where ADM.EMPLID = PLN.EMPLID
         and ADM.ADM_APPL_NBR = PLN.ADM_APPL_NBR
         and PLN.PROG_ACTION = 'DEFR'
         and PLN.EFFDT = (
            select MIN(PLN_ED.EFFDT)
            from PS_ADM_APPL_PROG PLN_ED
            where PLN_ED.EMPLID = PLN.EMPLID
                and PLN_ED.ADM_APPL_NBR = PLN.ADM_APPL_NBR
                and PLN_ED.PROG_ACTION = PLN.PROG_ACTION
         )
         and ADM.CU_ADM_DEFR_DT IS NULL;
    • CU_ADM_WADM_DT is derived from the following logic: 
      • update ADM
         SET ADM.CU_ADM_WADM_DT = PLN.ACTION_DT
        from PS_CU_GRAD_ADM ADM, PS_ADM_APPL_PROG PLN
        where ADM.EMPLID = PLN.EMPLID
         and ADM.ADM_APPL_NBR = PLN.ADM_APPL_NBR
         and PLN.PROG_ACTION = 'WADM'
         and PLN.EFFDT = (
            select MIN(PLN_ED.EFFDT)
            from PS_ADM_APPL_PROG PLN_ED
            where PLN_ED.EMPLID = PLN.EMPLID
                and PLN_ED.ADM_APPL_NBR = PLN.ADM_APPL_NBR
                and PLN_ED.PROG_ACTION = PLN.PROG_ACTION
         )
         and ADM.CU_ADM_WADM_DT IS NULL;
• Matrics
  • Definition: Student was matriculated into their program
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_ADM_MATR_DT IS not NULL and A.CU_ADM_MATR_DT > '1900-01-01'
    • CU_ADM_MATR_DT is derived from the following logic:
      • update ADM
         SET ADM.CU_ADM_MATR_DT = PLN.ACTION_DT
        from PS_CU_GRAD_ADM ADM, PS_ADM_APPL_PROG PLN
        where ADM.EMPLID = PLN.EMPLID
         and ADM.ADM_APPL_NBR = PLN.ADM_APPL_NBR
         and PLN.PROG_ACTION = 'MATR'
         and PLN.EFFDT = (
            select MIN(PLN_ED.EFFDT)
            from PS_ADM_APPL_PROG PLN_ED
            where PLN_ED.EMPLID = PLN.EMPLID
                and PLN_ED.ADM_APPL_NBR = PLN.ADM_APPL_NBR
                and PLN_ED.PROG_ACTION = PLN.PROG_ACTION
         )
         and ADM.CU_ADM_MATR_DT IS NULL;
• Melt
  • Definition: Student Cancelled or Withrdrew their as a student before the census date
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_ADM_MELT_DT IS not NULL and A.CU_ADM_MELT_DT > '1900-01-01'
    • CU_ADM_MELT_DT is derived from the following logic:
      • UPDATE ADM
         SET ADM.CU_ADM_MELT_DT = CAR.WITHDRAW_DATE
        from PS_CU_GRAD_ADM_TMP ADM, PS_STDNT_CAR_TERM CAR, PS_CUIR_TERM_VW TERM
        where ADM.EMPLID = CAR.EMPLID
         and ADM.ACAD_CAREER = CAR.ACAD_CAREER
         and ADM.ADMIT_TERM = CAR.STRM
         and CAR.WITHDRAW_CODE in ('CAN', 'WDR')
         and CAR.WITHDRAW_DATE < TERM.CENSUS_DT
         and TERM.ACAD_CAREER = CAR.ACAD_CAREER
         and TERM.STRM = CAR.STRM
         and ADM.CU_CURRENT_STATUS in ('MATR')
         and (ADM.CU_ADM_MELT_DT IS NULL or ADM.CU_ADM_MELT_DT <> CAR.WITHDRAW_DATE)
• Grads
  • Definition: Student has a COMPLETE_TERM on their program they applied for originally
  • SQL Logic: SELECT COUNT(*) FROM PS_CU_GRAD_ADM A  where A.CU_ADM_GRAD_TERM <> ''
    • CU_ADM_GRAD_TERM is derived from the following logic:
      • update ADM
         SET ADM.CU_ADM_GRAD_TERM = PROG.COMPLETION_TERM
        from PS_CU_GRAD_ADM ADM, PS_ACAD_PROG PROG, PS_ACAD_PLAN APLAN
        where ADM.EMPLID = PROG.EMPLID
         and ADM.ACAD_CAREER = PROG.ACAD_CAREER
         and PROG.ADM_APPL_NBR = ADM.ADM_APPL_NBR
         and PROG.EMPLID = APLAN.EMPLID
         and PROG.ACAD_CAREER = APLAN.ACAD_CAREER
         and PROG.STDNT_CAR_NBR = APLAN.STDNT_CAR_NBR
         and PROG.EFFDT = APLAN.EFFDT
         and APLAN.ACAD_PLAN = ADM.ACAD_PLAN
         and PROG.COMPLETION_TERM <> ''
         and APLAN.COMPLETION_TERM = PROG.COMPLETION_TERM
         and PROG.PROG_ACTION = 'COMP'
         and (ADM.CU_ADM_GRAD_TERM IS NULL or ADM.CU_ADM_GRAD_TERM <> PROG.COMPLETION_TERM)

Instructions

1. Instruction 1
2. Instruction 2
3. Instruction 3
4. Instruction 4
5. Instruction 5