Transcription

PS QUERY 101ctcLink PeopleSoft Query 101 BasicsData servicesJuly 2016 – Updated June 2018Paula McDaniel, Data Analytics Learning and Education InstructorWashington State Board of Community and Technical Colleges, Data ServicesctcLink PS Query 101 Basics by Paula McDaniel is licensed under a Creative Commons Attribution-NonCommercialNoDerivatives 4.0 International LicenseBased on a work at /ctclink-peoplesoft-reporting.aspxi

PS QUERY 101Table of Contents Course Goal . 1 Course Learning Objectives . 1 How to Use This Manual . 1The PS Query 101 Training Guide is comprised of three sections. . 1Each section will include: . 1 Section 1 Introduction . 2 Introduction to PeopleSoft Query . 2What is a Relational Database? . 2ctcLink PeopleSoft Pillars and Modules . 4Query Development Life Cycle . 6Phase I Develop Query in PCD . 6Phase II Request Migration to Production (PRD) . 7Phase III Data Services Review and Migration to Production (PRD) . 7Phase IV Modification of Existing Query . 7PS Query Terms . 7 Section 1 Review . 9 Section 1 Knowledge Test . 9 Section 2 Introduction . 13 Using PeopleSoft Query . 13Accessing PS Query . 13Allow Pop-Up Windows . 13Using PeopleSoft Query Viewer . 14Searching Using Query Viewer . 14Query Viewer Options . 16Using PeopleSoft Query Manager . 17Searching Using Query Manager . 17Searching Using Wildcards . 18Query Manager Options . 18 Using PeopleSoft Schedule Query . 20Using PeopleSoft Schedule Query through Query Viewer/Manager . 21Scheduling a Query to Run Through Query Viewer or Query Manager . 21 Create Your Own Scheduled Query through Query Viewer/Manager . 26Scheduling through Query Viewer/Manager – Simplified Instructions for a New RunControl ID . 26Criteria to Use in the Query . 26Scheduling through Query Viewer/Manager – Simplified Instructions for an Existing RunControl ID . 27

PS QUERY 101Criteria to Use in the Query . ‘;[27Using PeopleSoft Schedule Query through the Schedule Query Menu Path . 28Scheduling a Query to Run Through the Schedule Query Menu Path . 28 Create Your Own Scheduled Query through the Schedule Query Menu Path . 33Schedule Query Menu Path – Simplified Instructions for a New Run Control ID. 33Criteria to Use in the Query . 33Schedule Query Menu Path – Simplified Instructions for an Existing Run Control ID . 35Criteria to Use in the Query . 35 Section 2 Review . 36 Section 2 Knowledge Test . 36 Section 2 Exercises Campus Solutions . 38 Section 2 Exercises Human Capital . 39 Section 2 Exercises Financials . 40 Section 3 Introduction . 41 Creating Queries . 41Finding Records to Use . 41Tab Overview and Creating a Simple Query - Detailed Instructions . 42The Records Tab . 43The Query Tab . 45The Prompts Tab . 47The Fields Tab . 47The Criteria Tab . 52View SQL Tab . 53Run Tab . 53Saving the Query and Hyperlink Options . 54Query Creation Stages Review . 56 Create Your Own Simple Query . 57Criteria to Use in the Query . 57 Creating Joins in PS Query . 58Join Types . 58Cartesian Joins . 58Standard Join . 59Outer Join . 60Creating a Join - Detailed Instructions . 61Join Methods . 61 Create Your Own Join . 64Creating a Join - Simplified Instructions . 64Use the following criteria when completing this hands on exercise. . 65

PS QUERY 101 Using Criteria in PS Query . 65Adding Criteria Using the Criteria Tab – Detailed Instructions. 66Criteria Properties . 67Logical Operators for Criteria . 72Grouping Criteria with Parentheses . 72 Create Your Own Criteria . 75Adding Criteria Using the Criteria Tab - Simplified Instructions . 75Criteria to Use in the Query . 75Adding Criteria Using the Add Criteria Icon - Simplified Instructions . 76Criteria to Use in the Query . 76 Using Prompts to Extend the Life of a Query . 77Creating Prompts through the Prompts Tab – Detailed Instructions . 79Create the Prompt . 79Adding the Prompt as Criteria . 82Creating Prompts with the “Add criteria” icon - Detailed Instructions . 85 Create Your Own Prompt . 87Required Prompt with Lookup List through the Prompts Tab – Simplified Instructions . 87Criteria to Use in the Query . 88Create the Prompt . 88Add the Prompt as Criteria to the Query . 89Optional Prompt with Lookup List Through the Add Criteria Icon – SimplifiedInstructions . 89Criteria to Use in the Query . 89 Course Review . 90 Simplified Instructions . 92Scheduling through Query Viewer/Manager – Simplified Instructions for a New RunControl ID . 92Scheduling through Query Viewer/Manager – Simplified Instructions for an Existing RunControl ID . 93Scheduling through Schedule Query Menu Path – Simplified Instructions for a New RunControl ID . 94Creating a S