Population Selection and maybe Expert Rules

1
Population Selectionand maybe Expert Rules

• Bruce Foote, University of St. Francis
• Tracy Hall, Eastern Illinois University

2
Forms/Processes/Reports

• Rules Form (GLRSLCT)
• Data Extract (GLBDATA)
• Extract Inquiry (GLIEXTR)
• Extract Data (GLAEXTR)
• Applicant Report (RORAPLT)

3
Operators

• ,ltgt,gt,lt,gt,lt
• LIKE, NOT LIKE
• IN, NOT IN
• IS NULL, IS NOT NULL
• BETWEEN

4
,ltgt,lt,gt,gt,lt

• RCRAPP1_CURR_REC_IND Y
• RORSTAT_AIDY_CODE 0910
• RPRAWRD_PAID_AMT ltgt 0
• RPRAWRD_PAID_AMT gt 1000
• SHRLGPA_GPA lt 2.00
• RORENRL_TERM_CODE 201010
• RRRAREQ_TREQ_CODE ENTINT

5
Point of clarification
SHRLGPA_GPA lt
2.00 AND SHRLGPA_GPA_TYPE_
IND O
AND SHRLGPA_LEVL_CODE UG
(Sometimes statements in and of themselves
dont mean anything unless other statements are
included as part of the overall population
selection)
6
LIKE, NOT LIKE

• RORSTAT_PGRP_CODE LIKE IND
• RPRAWRD_FUND_CODE NOT LIKE F

7
IN, NOT IN

• SARADAP_TERM_CODE_ENTRY IN (201010,201020,201
  030)
• RORSAPR_SAPR_CODE NOT IN (SUSP)

8
IS NULL, IS NOT NULL

• RORSTAT_ALL_REQ_COMP_DATE IS NOT NULL
• RORSTAT_PCKG_COMP_DATE IS NULL

9
BETWEEN

• RORSTAT_PCKG_COMP_DATE BETWEEN 01-JAN-2009 AND
  30-MAY-2009

10
Date Considerations

• Do not use () comparison
• Dates may contain hours, minutes, and seconds
• Use lt,gt,ltgt,lt or gt
• RRRAREQ_STAT_DATE gt 01-JAN-2009 AND
  RRRAREQ_STAT_DATE lt 01-FEB-2009

11
PIDM/Aid Year Joins

• Whenever you are dealing with situations that
  involve multiple aid years or multiple records
  within an aid year, it is necessary to join the
  PIDM and specify the aid year.

12
PIDM/Aid Year Joins
SELECT RRRAREQ_PIDM FROM RRRAREQ,
RORSTAT RRRAREQ_AIDY_CODE aidy
AND RORSTAT_AIDY_CODE
RRRAREQ_AIDY AND RORSTAT_PIDM
RRRAREQ_PIDM AND RRRAREQ_TREQ_CO
DE (LNAPP)
AND RRRAREQ_TRST_CODE status
AND RORSTAT_PCKG_REQ_COMP_DATE IS NOT NULL

13
Dynamic Prompts

• RORSTAT_AIDY_CODE aidy_code
• RORSTAT_PCKG_COMP_DATE gt pckg_comp_date
• RORSTAT_BGRP_CODE bgrp_code
• Saves time and effort for data that is
  consistently different and may change from term
  to term or year to year.

14
Using Variables

• RORSTAT_PIDM NOT IN (SUB TRACK2)
• A.GURMAIL_DATE_PRINTED (SUB
  TRACK1)
• SGBSTDN_TERM_CODE_EFF (SUB
  SUB_CURR_STU_REC)
• MUST be the last rule in selection

15
Using Objects

• Common set of coding
• Created on GLROBJT
• View on GLIOBJT
• Enter as a Data Element using INCL or click on
  Data Element button from within GLRSLCT and
  double-click on object

16
GLROBJT
17
GLRSLCT
Click on Data Element
18
Select appropriate object
19
Data elements are automagically populatedbig
time saver
20
Where Do I Get Data Elements?
I would advise against printing as this is
currently 711 pages long
21
Sample Output
22
Combining Population Selections
23
Union

• Meets the rules for Selection ID 1
• OR
• Meets the rules for Selection ID 2
• Placed in Selection ID 3

UNION A or B No duplicates
24
Intersection

• Meets the rules for Selection ID 1
• AND
• Meets the rules for Selection ID 2
• Placed in Selection ID 3

25
Minus

• Meets the rules for Selection ID 1
• Removes
• Meets the rules for Selection ID 2
• Placed in Selection ID 3

26
FAFSA_1
SELECT RRRAREQ_PIDM FROM RRRAREQ,RORSTAT RRRAREQ_
AIDY_CODE aidy_code
AND RRRAREQ_AIDY_CODE
RORSTAT_AIDY_CODE AND RRRAREQ_PIDM
RORSTAT_PIDM
AND RRRAREQ_TREQ_CODE FAFSA
AND RRRAREQ_STAT_DATE gt FAFSA_date
AND RORSTAT_TRK_LTR_IND Y
27
FAFSA_2
SELECT GURMAIL_PIDM FROM GURMAIL GURMAIL_AIDY_COD
E aid_year_code AND GURMAIL_LETR_CODE FA_TRA
CKING
28
The Resulting FAFSA_3

• All students pending tracking letters
• From a definitive FAFSA date
• Who have never received a tracking letter

29
Creating Manual Selections
30
Creating Manual Selections
31
Using Population Selections

• Letters (Tracking, Award, SAP)
• Reports (Tracking, Award)
• Processes (Batch Posting, Corrections)

32
Other Things To Consider

• When dealing with any population selection that
  is used to select students for letter or E-Mail
  generation add the following line
• SPBPERS_DEAD_IND IS NULL
• Im aware its morbid, but
• (Check to make sure this is where your
  institution captures this information!)

33
Questions

• ??????????????????????????????????
• If we have further time I can discuss expert
  RORRULE creationif not I have included the
  information that you can use to collaborate with
  your IT department to develop some of these more
  complicated rules

34
Simple RORRULEFails undergraduate SAP
35
By clicking on Options, Compiled/Expert SQL
Code you can view the rule in expert mode. Once
you make a change here, the RORRULE will always
be in expert mode
36
This Expert RORRULE is a Tracking Rule that
places students nowhere on Admissions radar
into a distinct Tracking Group.