Banner Training Reference Guide POPULATION SELECTION (PopSel) 101 Updated 6/11/2012 A portion of this document is: Copyright © Ellucian 2008-2012. This document is proprietary and confidential information of one or more of the Ellucian companies and is not to be copied, reproduced, lent, displayed used or distributed except under license from Ellucian. Third party copy centers are advised that clients of Ellucian that have licensed the underlying technology associated with this documentation have a valid license to copy and use this documentation in accordance with that underlying license. 1 2 Table of Contents Introduction ...................................................................................................................... 4 How Data is Stored .......................................................................................................... 4 Finding a PopSels [GLRSLCT] ....................................................................................... 6 Copying PopSel .................................................................................................................... 9 Running the PopSel [GPXDATA] .............................................................................. 11 Mapping your JobSub (J:drive) .................................................................................. 16 Viewing The Selected Data in INB ........................................................................ 17 [GLIEXTR or GLAEXTR] .................................................................................................... 17 Printing The Selected Data [GPOPSEL].............................................................. 18 3 Introduction The purpose of a Population Selection (PopSel) is to select the PIDIMs of persons or non-‐ persons in the system, based on rules that have been develop using SQL-‐type statements. Because the process only selects PIDMs, you cannot use it for selecting courses, gifts, rooms, pledges or any other information. Only PIDMs can be selected, and only persons or non-‐ persons have PIDMs. Population Selection involves three processes: • You must define your population, • Run the Population Selection Extract Program, and • You may view and edit the results of your selection. Once you have a list of PIDMs that meet your selection criteria, you can use it in combination with many Banner reports and processes, as well as with Banner Letter Generation How Data is Stored Banner Population Selection is a function that allows us to select people or organizations based on specific criteria. For example, because Banner stores a person’s sex and addresses, we can select all the people in the database who are male and have an address in Washington State. Since Banner stores a great deal of information, it makes it possible to select groups using both simple and complex criteria. In order to understand Population Selection, it is helpful to understand how Banner stores information. All data is stored on ‘Tables’. Think of a table as a spreadsheet, with columns and rows. Each column contains data for a field, and each row contains all the information for a record. For example, look at the table below: NAMES&IDS LAST NAME FIRST NAME MIDDLE NAME ID Spaulding John Forrest 578688818 Smith Mary Therese 003525454 Williams Tom Mitchell 952854785 Note how all last names are stored in the first column, and how all the information about John Forrest Spaulding is stored in one row, or record. 4 In order to store the vast amount of data used by Banner clients, the system has thousands of tables. The table SPRIDEN stores name and ID information, the table SPRADDR stores address information, the table SPBPERS stores personal information (gender, birthdate, ethnicity, etc.) just for a start. To make sure that the information on a specific record in one table is connected to the correct record in another table, Banner uses a field that is common to both and will have the same value for a record in every table. That field is called a PIDM. Every time a new person or non-‐ person is added to the system, Banner generates a unique PIDM. This number is used for every record in every table that pertains to the person created. Let’s look at that table again, but this time with the PIDM column added: PIDM LAST NAME FIRST NAME MIDDLE NAME ID 0000001 Spaulding John Forrest 578688818 0000002 Smith Mary Therese 003525454 0000003 Williams Tom Mitchell 952854785 And now let’s look at another table; the address table, SPRADDR: ADDRESSES PIDM STREET CITY STATE ZIP 0000001 1 Main Street Spokane WA 99202 0000002 2 Elm Street Billings MT 59102 0000003 3 Beech Street Seattle WA 98125 To display name and address information for Mary Smith, Banner can connect the two records using the PIDM. Thus, Mary Smith lives at 2 Elm Street, Billings, MT, 59102. Every table in Banner that contains information about a person or non-‐person uses the PIDM to identify the record. 5 Finding a PopSels [GLRSLCT] A. Access the GLRSLCT Population Selection Definition Rules Form B. Select the Application you want to work in. You can double click in this field to bring up a list of applications if necessary. C. Click the search icon next to the Selection ID field to bring up all PopSels that have been created under the Application you selected. D. Scroll through the list until you find the PopSel you want to look at. You can search the list by a specific user if needed. Simply perform an <Enter Query> function, type in the user’s name (you would need to know their Banner username and enter it in CAPS) in the CREATOR column and perform an <Execute Query> function. E. When you find the PopSel you want to look at, double click on the Selection ID. This will return you to the GLRSLCT form. 6 F. Click in the Description field to view the PopSel. G. You can get a general idea for what records the PopSel will pull by looking at the Data Element column. This will show you the ‘rules’ the PopSel is using to pull records. H. If you want to look at a different PopSel, perform a <Rollback> function to clear the form and return to the top or <Exit> to exit the form. 7 I. The Rules Block contains the specific parameters (rules) for the Population Selection. Parameter Description Example ( Used when linking 2 rules together Data Element The field names that you want SARADAP_TERM_CODE_ENTRY to use in the Population Selection. The ‘smallest’ field is named FIRST, and works down to the ‘largest’ field name Operator This is the ‘verb’ of the rule. It =, <>, IN, NOT IN, LIKE, NOT comes from the pull down list LIKE, etc. of available operators. Value Specific code values used to ‘200110’ qualify the person or non-‐ (‘200110’,’200120’,’200130’) person in the Population &term Selection. Enclose all values (codes) in single quotes. When using several values (codes), each one is single quotes, separate them all by If the naming sequence commas, and enclose the of a Data Element is not entire group in parentheses. clear to you go to the dynamic variables are also form you think the field used in this column, and be is from, make sure your prompted for specific values curser is on that fiend (codes) when the Population and go to the Help Selection is run menu and select ) Used in conjunction with the Dynamic Help Query. ‘(‘ column to link 2 rules That will show name of that field. together And/Or Links rules together. Use AND if you want the person to qualify for each rule created. Use OR if record can qualify for first rule OR second rule OR third rule, etc. When you’ve found the PopSel you want to run, you’re ready to move on. 8 Copying PopSel Instead of trying to re-‐invent the wheel, you should get into the habit of looking at other Population Selections that users have created to see if you can ‘borrow’ them for your own uses. Banner will allow you to run other users’ Population Selections. However, you will not be able to make any changes to their Population Selections. If changes are to be made, you will need to copy their Population Selection into your own User ID. Follow the steps below to copy Population Selections: A. Access the GLRSLCT form B. Enter in the Application the Population Selection has been created under C. Enter in the Selection ID for the Population Selection 9 D. Click in the Data Element field to bring up the Rules for this Population Selection E. Select ‘Copy’ from the Options Menu or Right Click and select Copy F. Enter in the same Application G. Enter in a Selection ID (what do you want to name this Population Selection?) 10 H. Click Insert Record on the shortcut toolbar I. You will be taken back to the GLRSLCT form and you should see your name as the Creator ID J. Make whatever changes you need and <Save> Running the PopSel [GPXDATA] Once you find the PopSel you want to run go to GPXDATA, you must run a process to extract the data (PIDMs only) from Banner. Follow the steps below to run the Population Selection extract process: A. Access the GPXDATA process B. Process field should say GPXDATA C. Click in the Values column 11 D. Enter in the Values accordingly IN CAPS Parameter Response 01 POPSEL Application ID Required-‐ Enter the “Application ID” for all of the specified “Selection ID”s. 02 POPSEL Selection ID (Primary) Required-‐ Name your primary “Selection ID”. 03 POPSEL Creator ID Required-‐ Enter the Creator ID of the person who created the Selection ID(s). NOTE: All ”Selection IDs” have the same “Application ID” and “Creator ID”. 04 Run this POPSEL Required-‐ Enter one or more “Selection ID”s for POPSEL(s) you want to run. 05 Include POPSEL with Primary Optional -‐Enter one or more “Selection ID”s for POPSEL results you want to include in your primary “Selection ID”. 06 Exclude COUPLES from Primary Optional -‐ – Enter one or more “Selection ID”s for POPSEL results you want excluded from 12 07 Exclude INDIVS from Primary 08 Intersect PIDMs with Primary 09 Clear/Trunc Primary Selection primary “Selection ID”. Both primary and corresponding spouses records are excluded from primary “Selection ID”. Optional-‐ Enter one or more “Selection ID”s for POPSEL results you want excluded from primary “Selection ID”. Only individual record is excluded from the primary “Selection ID”. Optional-‐ Enter one or more “Selection ID”s for POPSEL results you want intersected with the primary “Selection ID”. Required – Clear results of primary “Selection ID” before any processing begins. YES = Clear all except manually inputted POPSEL records. ALL = Clear all including manually inputted POPSEL records. NO = Do not clear primary “Selection ID” results. Default = YES. E. Click on Submit F. <Save> G. If there were any Dynamic Variables in the Rules of the Population Selection, the cursor will jump back to the Parameter block. All values (codes) for Dynamic Variables are required. Enter the values (codes) accordingly. Click on Submit again and then <Save> again to run the Population Selection with the Dynamic Variables 13 you specified. H. To view the number of records that were extracted for the Population Selection: 1. Access the GWASPOL form by selecting Options from the main menu bar or Right Click and select Review Output from the menu a. Select ‘Review Output’ OR 14 b. Double click on the gpxdata…lis file or highlight it and click the View/Open Spoolfile tab c. This will open another window in your browser (be sure your popup blocker is off) If you go to your spooler and there is an .in file the system is still working on the extract. Refresh and open the .lis 15 d. Close this window to return to the GWASPOL form. OR 2. Access your JobSub directory (J drive) to view and print files from your spooler. (See Mapping directions below) Mapping your JobSub (J:drive) If you have not mapped the J drive to your JobSub directory, do the following: a. Right click on ‘My Computer’ on your desktop b. Select ‘Map Network Drive’ from the menu c. Select the J drive from the pull down menu (make sure it’s not already mapped to something) d. Type ‘\\SJ\JOBSUB’ for the Path or Folder e. Select ‘Reconnect at logon’ f. Click OK or Finish Once this mapping is established, you can open Word or Excel and select the J drive to view the same files you would see in your spooler (GWASPOL) in Banner. 16 Viewing The Selected Data in INB [GLIEXTR or GLAEXTR] A. The Population Selection Extract Inquiry Form (GLIEXTR) displays the IDs in a population. The IDs can be sorted by Name or ID. This form is used for viewing only, you can’t update or change the results. B. The Population Selection Extract Data Form (GLAEXTR) displays the data (this time sorted by PIDM), and can be used to modify the results of a population extract. Only the User who extracted the IDs can access a Selection ID on GLAEXTR. You can add or delete records as needed. (Notice that the form displays deceased persons, as well as those who have requested confidentiality, and indicates if a record was created manually or by the system). 17 The purpose of the Population Selection process is not simply to create a list of names, but to create a set of records that can be used as a base for other Banner reports and processes. You will find that many reports and processes allow you to include the name of a selection (Selection ID, Application and Creator) in the parameters. Banner Letter Generation also allows you to generate letters to people and organizations in a Population Selection. Printing The Selected Data [GPOPSEL] To print a list, with ID and Name, of the records extracted from the Population Selection, do the following: A. Access the GPOPSEL process B. The Process field should say GPOPSEL C. Click in the Values column D. Enter in the values accordingly, IN CAPS: 18 Parameter 01 Application 02 Selection ID 03 Creator ID 04 User ID Response Enter the code of the Application which controls the Selection ID to be processed Enter the name of the Population Selection. NOTE: If you performed a Union, Intersection, or Minus, this Selection ID would be the NEW Selection ID you created Enter the Creator ID of the person who created the Selection ID Enter the User ID of the person who is running the Selection E. Click on Submit F. <Save> G. This will provide you with a gpopsel…log and a gpopsel…lis file (notice the Help Line at the bottom of the form to make sure these files are created) H. Select ‘Review Output’ from the Options Menu to access the GWASPOL form or access your JobSub directory to view the .lis file or Right click and select Review Output 19 OR To view the list, click on the View/Open Spoolfile tab I. To print the list, return to the GWASPOL form 1. Highlight the gpopsel…lis file 2. Click the printer icon on the shortcut toolbar or click the Print Spoolfile tab 20 3. Enter the appropriate parameters 4. Click the Print button to print list Additional trainings regarding this topic are: PopSel 201 where you learn more about writing SQL rules and, Letter Generation where you can gather information about the selected records. Go to www.gonzaga.edu/GUTraining to find more information on the trainings and register. If you have any questions please feel please contact Cassandra Marrs at [email protected] or (509) 313-6787. 21