Background:

Sarah Loebis wanted Zicklin enrolled student report for 1159 based on following selections:

  • ID
  • Last Name
  • First Name
  • Cumulative GPA
  • Total Credits
  • Total Transfer Credits
  • Total Credits Attempted at Baruch
  • Total Credits Earned at Baruch
  • Total Graded Units
  • Total Grade Points
  • Major
  • Amount of credits they are registered for that term
  • All subjects grade

New request for adding additional fields to the query by sep 20, 2016.  see following:

  • Sex,
  • Residency,
  • Visa,
  • Ethnicities,
  • Citizenship

This is a request for a new CUNYFirst query that is needed for the Zicklin Program. The prompts would be: Institution, Career, Term & Student Group.

 

The information needed for the query see below:

 

Hi Sarah,

Please find attached report. The report includes:

Active and enrolled students

Term: 1159

Career: UGRD

Academic Plan: BBA (courses belong to zicklin)

Due to the active vs enrollment the attendance of students may vary because keep that mind all active students are not enrolled always.

 

 

Please see the Kace ticket as a reference:https://helpdesk.baruch.cuny.edu/admin Ticket reference: 45335

Final Report Delivery with requirements:

 

The query for Zicklin student CU_BAR_SR_00084 has been   shared with Sarah.  The query is working fine.

CU_BAR_SR_00084 Prompts are below:  

  • Institution: Bar01 
  • Career: UGRD

  • Term: 1159 (It's constant at this point)

    The query needs to be scheduled in order to get a report.  Since it carries a large data set therefore, before change or modify any thing please be sure of the query detail.



Query Development:

Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.

 

Records
Description of fields
TERM_TBL

EMPID

ACAD_PROG EMPID, ACAD_PROG, EFFDT, PROG_STATUS , ADMIT_TERM, EXP_GRAD_TERM
ACAD_PLANACAD_PLAN
CU_BIO_VWLAST_NAME, FIRST_NAME, EMAIL
EMAIL_FERPA_VW EMAIL_ADDR
STDNT_CAR_TERMCUM_GPA,  UNT_TAKEN_PRGRSS
CLASS_TBL_SE_VW CLASS_NB,  SUBJECT,  CATALOG_NBR, SESSION_CODE,  CRSE_GRADE_OFF
SF_STDNT_PRS_VW SEX
SCC_VISA_P_QVW
VisPmtType
SCC_ETH_GRP_VW
Ethnicity
RESIDENCY_OFF
Residency

 

Expressions:

 

'CAMP'
to_char (F.CUM_GPA, '99.9999')
to_char (F.UNT_TAKEN_FA, '999.9999')
'USA'

LISTAGG(CASE WHEN J.ETHNIC_GROUP IN ('1') THEN 'WHITE'

    WHEN J.ETHNIC_GROUP IN ('2') THEN 'BLACK'

    WHEN J.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'

WHEN J.ETHNIC_GROUP IN ('4') THEN 'ASIAN'

WHEN J.ETHNIC_GROUP IN ('5') THEN 'AMERIND'

WHEN J.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'

when J.ETHNIC_GROUP is null then 'Not Provided'

    ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY

  CASE WHEN J.ETHNIC_GROUP IN ('1') THEN 'WHITE'

WHEN J.ETHNIC_GROUP IN ('2') THEN 'BLACK'

    WHEN J.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'

WHEN J.ETHNIC_GROUP IN ('4') THEN 'ASIAN'

WHEN J.ETHNIC_GROUP IN ('5') THEN 'AMERIND'

WHEN J.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'

when J.ETHNIC_GROUP is null then 'Not Provided'

ELSE 'OTHER' END)

 

 

Prompt:

Institution, Term and Career have been mandatory for an user to input.

Criteria:

EFF_STATUS is equal to A.TERM_BEGIN_DT - Term Begin Date (EffSeq = Last) because the report is based on active and enrolled students.  Include majors from ACAD_PLAN like '%-BBA' .  PROG_STATUS - Academic Program Status from ACAD_PROG is 'AC'. STDNT_ENRL_STATUS - Student Enrollment Status from CLASS_TBL_SE_VW is 'E'.

 

 

 

  • No labels