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: PopulatedSELECT by getting the ACAD_YEAR field from PS_TERM_TBL. Visually its changing the display to showDISTINCT CONCAT(ACAD_YEAR-11, ,'-', 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

• FormDefinition: 1Any Namestudent -that Linkhas filled out a Request for More Information form for a specific term, or has applied to that term. 
• FormSQL 2Logic: NameSELECT -COUNT(*) LinkFROM 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

• MailingDefinition: 1Submitted Namean -application. Link
• MailingSQL 2Logic: NameSELECT -COUNT(*) LinkFROM 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

• QueryDefinition: 1Completed Nameall -the Linkrequirements for the application to be reviewed
• QuerySQL 2Logic: NameSELECT -COUNT(*) LinkFROM 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

• SourceDefinition: FormatHas 1an Nameadmit -decision Linkon 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

• OtherDefinition: SlateCurrent Objectapplication 1status Nameis -in LinkDENY

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

• OtherDefinition: SlateStudent Objecthas 2a NameDEIN -decision Linkon 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; DeclinesNOTE: 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.  MatricsDeclines

Definition: Student withdrew their application, and hasn't deferred, or withdrew after they deferred admissions MeltSQL 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