Transcription
Don't be Afraid:It's Just a Popsel!Sara McNure, Assistant RegistrarTrisha Knight, Systems Analyst IIGeorgia Summit 20061
It’s Just a Popsel!Using Banner 7zzzzWhat is a PopselSteps to Create PopselsApplication RulesPopulation Selection RuleszzzzzVariablesObjectsDynamic parametersExtracting PopulationTroubleshooting2
What is a Popsel?zzPopulation Selection is a Banner tool toselect groups of people (IDs) who sharecommon criteriaPopsels can be used for reporting, lettergeneration, or in processes3
Steps to Create Popsels1.2.3.4.5.6.7.Define your selectionUse Help/Dynamic Help Query to find yourtables & fields (GUAHELP/GURPDED)Create Application (GLRAPPL if needed)Create Variables (GLRVRBL if needed)Build the Selection Rules (GLRSLCT)Extract the Population (GLBDATA)View & Verify your Population SelectionResults4
Define your selectionzzzWhat is your population?Write it outStart simple then build on it5
Find your tables & fields usingGUAHELPEXAMPLE:SGBSTDN is the table andSGBSTDN TERM CODE EFFis the field6
GURPDED7
GURPDED Output8
Basic Application DefinitionRule - GLRAPPL9
Application Definition RuleWith Application Level Rule GLRAPPL‘&’ designates dynamicvalue10
GLRSLCT – SelectionDescriptionzzzzzDescribe clearlyManual – Enter student IDs in GLAEXTRLocked – Only creator can use, view, ormodify selectionDeleteApplication Level Rules Exist11
GLRSLCT - New feature withBanner 7Application Level RuleIndicator in GLRSLCT12
GLRSLCT Definition BlockzzzzThe ‘Select’ field must always reference a PIDM (ex.SFRSCTR PIDM)The ‘From’ field must list all tables that will be usedTo improve performance of GLBDATA, list multipletables in the ‘From’ field from most general to mostspecific (largest to smallest)Maximum tables you can use is 1513
GLRSLCT - Rules14
Using Dates in RulesUse ‘BETWEEN’ when a daterange is needed15
Using Dates cont.Remember: In Banner, most date fields arestored with hours, minutes, and secondsAnother Example:SPRADDR ACTIVITY DATE ’01-SEP-2006’ANDSPRADDR ACTIVITY DATE ’02-SEP-2006’16
Variables - GLRVRBLHow do you know you need a variable(sub query)?zzWhen maximum or minimum date, sequencenumber, or effective term are neededYou can do most anything you can do in SQLincluding COUNT, SUM, or AVG17
Variables zzAre Associated with the ApplicationCan be copiedIMPORTANT:The variable MUST be the last rule18
Create Variable - GLRVRBLTo retrieve themaximum StudentRecordLess than orequal to theTerm needed19
Using a Variable in Your RulesExample: Current studentrecord (SGASTDN)20
Objects – Defined in GLROBJT21
Objects used in GLRSLCTQuery and select Objectfrom Data Element22
Dynamic Popsels‘term’ is the DynamicParameter23
Dynamic Popsel in GLBDATAExample: Dynamic term is theSFRSTCR TERM CODE24
Copy PopselszzzChange the Selection IDModify carefullyWatch for unused tables25
GLBDATASinglePopsel26
Combining PopselsUnion/Intersection/Minus27
Combining PopselsUnion – combines results of both populationselections into onez Intersect – returns only the PIDMs that exist in bothpopulation selectionsz Minus – Be careful of order on this option, you willbe subtracting the PIDMs in the SECONDpopulation selection from the FIRSTNOTE: It is not necessary to run each populationselection separately before combining.MANUAL added IDs will not be included.z28
Use previous results in newPopselzzzYou will need to run each Popsel you will useseparatelyWhen you run GLBDATA you are addingyour Popsel results to the table GLBEXTRYou will need to link GLBEXTR KEY to yourPIDM field29
Using the table GLBEXTR inyour GLRSLCT rulesPulling together 3Popsels into 130
View your resultszzGLAEXTR – Can view and edit your ownPopselsGLIEXTR – Can view Popsels31
Troubleshooting What to do if your Popsel is notpulling correct population or notrunning?32
Your variable MUST be the lastruleVariable33
Error in GLBDATA .log file*ERROR* DURING PREPAREPARM2.ABORTINGSQLCODE 0936SQL ERROR ORA-00936: missing expressionX01 ROLLBACK SQLCODE 0000X01 COMMIT (1) SQLCODE 0000SQLCODE 0000ORA-01403: no data foundDQY-ABORT ROLLBACK SQLCODE 0000ORA-01403: no data found34
Make sure there are not anyunused tables in the ‘From’ fieldSPRHOLD is not used inrules35
Finding Problems with Popsels Detail ExecutionReport36
Using Parameter 8 – GLBDATADetail Execution Report options:z Y Display SQL, paragraph names andadditional informationz IDisplay SQL and values inserted into theGLRCOLR tablez S Display SQL only37
Using Parameter 8 – cont. SYSTEMS & COMPUTER TECHNOLOGYPOPULATION SELECTION EXTRACTCONTROL REPORTPAGE1 Program GLBDATA connected to ORACLEGLBDATA Version 4.2.0.1 Section: P01-GET-PARAMETERSPARM-PTR 0001 PARM-LEN 0019P01ASection: P01-GET-PARAMETERS PARM-PTR 0015 PARM-LEN 0022P01ASection: P01-GET-PARAMETERSPARM-PTR 0021 PARM-LEN 0022-------- DQY-S1 at end of P07-PROCESS-CONT ------SELECT DISTINCT(SGRASSI PIDM),SYSDATE FROM SATURN.SFRSTCR ,SATURN.SGRASSI WHERE SGRASSI PIDM SFRSTCR PIDM AND SGRASSI TERM CODE EFF '200608' AND SGRASSI GSTA CODE 'AC' AND SFRSTCR TERM CODE --------SQL Codegenerated byGLBDATA -------- DQY-S1 at end of P07-PROCESS-CONT ------SELECT DISTINCT(SGRASSI PIDM),SYSDATE FROM SATURN.SFRSTCR ,SATURN.SGRASSI WHERE SGRASSI PIDM SFRSTCR PIDM AND SGRASSI TERM CODE EFF '200608' AND SGRASSI GSTA CODE 'AC' AND SFRSTCR TERM CODE -------- PEOPLEQUERYDESCRIPTIONSELECTEDMAND INS GRADASSTmandatory insurance - GA150TotalRecordsSelected SELECTION COMPLETED.38
What’s wrong?39
GLBDATA.log file-------- DQY-S1 at end of P07-PROCESS-CONT ------SELECT DISTINCT(SGRASSI PIDM),SYSDATEFROM SATURN.SFRSTCR ,SATURN.SGRASSIWHERE SFRSTCR PIDM SGRASSI PIDM ANDSFRSTCR PIDM SGRASSI PIDM SUPERVISORAND SGRASSI TERM CODE EFF '200608' ANDSGRASSI GSTA CODE 'AC' ANDSFRSTCR TERM CODE '200608'40
Defect - #:1-WMF57SQL desc ---------------------------- ------------------- ----------------------------SGRASSI PIDMNOT NULL NUMBER(8)SGRASSI TERM CODE EFFSGRASSI CATEGORYSGRASSI GTYP CODESGRASSI SOFF CODESGRASSI STIPENDSGRASSI FTE VALUESGRASSI MIN CRSE LOADSGRASSI MAX CRSE LOADSGRASSI REQUIRED HRSSGRASSI GSTA CODESGRASSI GSTA DATESGRASSI DEGC CODESGRASSI LEVL CODESGRASSI COLL CODESGRASSI DEPT CODESGRASSI MAJR CODENOT NULLNOT NULLNOT NULLSGRASSI PIDM SUPERVISORSGRASSI ACTIVITY DATESGRASSI PROGRAMSGRASSI TERM CODE 2)VARCHAR2(4)VARCHAR2(4)This table has 2PIDMSNUMBER(8)NOT NULLDATEVARCHAR2(12)VARCHAR2(6)41
Defect WorkaroundCheckedManualJoined thecorrect PIDMs42
Another Known Defect inBanner 7zDefect #1-E9U9D - Last ID Displayed onGLAEXTR replaces current GLOBAL ID(7.x only)43
ALWAYS Check .log file forerrors!!!Program GLBDATA connected toORACLEGLBDATA Version 4.2.0.1*ERROR* DURING OPENXPARM2.ABORTINGSQLCODE 1652SQL ERROR ORA-01652: unable to extend tempsegment by 128 in tablespace TEMP44
Print Your ListWe have a local process to print list fromPopsel via Job Submission: YGPPOPS.The report displaysz IDz Namez Date of Birth (Optional)45
YGPPOPSz11-Sep-06GEORGIA SOUTHERN UNIVERSITYPage: 1List Population SelectionYGPPOPSzzzEAGLE ID LAST/FIRST/MIDDLE NAMEBIRTH DATE---------------- --------------------------------------------- X8568XXXXX3515XXXXX0059XXXXX7715XXXXX0788z* * * * * * * YGPPOPS * * * * * * *zzRun began on 11-Sep-06 at ** Run parameters *********zzApplicationSelectionCreatior idUser idBirth datez----------------------------------zTotal records listed: 8zRun ended on 11-Sep-06 at 06:21:58zzzzzzzzzzAgan, Norman DavidAyeni, Fatima NatashaChampion, Shannon RenaeCox, Jon HHines, John OtisJohnson, Chermaleta AundrellPapoutsis, NicholasRowe, Timothy Bradley: REGISTRAR: TK AI STUDENTS: CSDTK01: CSDTK01:NChose to not displayDOB46
Extract Data OptionzzzGUAOBJS – Data Extract OptionDisable Pop-up BlockerEnable ‘Prompting for File Downloads’on IE Browser Security Settings.47
Using Extract Data Option48
Using Extract Data. cont49
Using Extract Data. cont50
T Banner CBTs:zzzPopulation Selection for Banner 7 AdvancementPopulation Selection for Banner 7 Financial AidPopulation Selection for Banner 7 StudentSCT Banner Workbooks:zzzAdvancement Population SelectionPopulation Selection (Under each version of General)Student Population Selection51
Questions?52
Thank you for attending. We hopesome of this information will beuseful to you. We learned lots justtrying to pull this presentationtogether.Sara McNure: smcnure@georgiasouthern.eduTrisha Knight: t knight@georgiasouthern.edu53
SCT Banner Workbooks: zAdvancement Population Selection zPopulation Selection (Under each version of General) zStudent Population Selection . 52 Questions? 53 Thank you for attending. We hope some of thi
