Transcription
PeopleSoft Query TrainingPeopleSoft Version: 9.0
I. INTRODUCTION .3OVERVIEW .3OBJECTIVES .3II. REVIEWING THE TYPES OF QUERIES .4III. BASIC QUERY CONCEPTS .5OVERVIEW .5RUNNING A QUERY: BROWSER .5IV. FINDING AND SELECTING AND RUNNING A PREDEFINED QUERY .6V. CREATING A BASIC QUERY .7TERMINOLOGY .7CREATING A BASIC QUERY.7VI. CREATING A QUERY USING MULTIPLE TABLES .14
University of Wisconsin SystemHRS ProjectPS Query TrainingI. IntroductionOverviewThe Query Designer provides PeopleSoft users with a simple, intuitive way to create andrun queries. The results can be viewed online or exported to an Excel spreadsheet or aCSV text file.You will learn basic functions of the query tool and how to create queries: selectingspecific records and fields. You will learn how to modify column headings and how toretrieve the short or long descriptions for the translate value rather than the field name.Once you know the basics, you will learn how to retrieve information based on criteriarequirements such as equal to, greater than, in a list, between and like. You will also learnhow to add runtime prompts. Runtime prompts give the user the ability to enter specificvalues for a designated field. The values are used as criteria for retrieving the informationfor your report. You will learn to create queries using effective dates because PeopleSoftuses effective dating in order to show data in a historical perspective.In addition, you will explore how to create complex queries using multiple tables usingpredefined joins.Objectives Reviewing Query Types Finding, Selecting and Running a Predefined Query Create Simple Queries Specify Criteria for Retrieving Data Creating Complex Queries using Multiple Tables Sorting and Ordering Query ResultsUW Query Training.docPage 3 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingII. Reviewing the Types of QueriesPeopleSoft Query provides the following different types of queries:User queriesCreate and run queries to retrieve data from the database directly from Windows-basedQuery Designer, or the web-based Query Manager/Query Viewer applications.Process queriesWrite queries that are intended to run periodically by batch processes, most likely usingPeopleSoft Application Engine and the Query API (application programming interface).Role queriesWrite queries that PeopleSoft Workflow uses to determine to whom to sendemails, forms, or worklist entries.UW Query Training.docPage 4 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingIII. Basic Query ConceptsOverviewYou will learn the basic features and concepts of Query, including how to run apredefined query, how to create a query from one record, how to modify your viewpreferences and how to manipulate data in your query.Running a Query: BrowserPS query provides powerful querying capabilities within the PeopleSoft InternetArchitecture. Through your browser you can define and modify queries, run queries andschedule queries to be run on a regular basis.The query viewer is the primary place for the end user to run and view queries. Once thequery is found you can choose to run the query immediately in the browser or schedule itto be run at a later time.UW Query Training.docPage 5 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingIV. Finding and Selecting and Running a Predefined QueryStep 1. Navigate to the Query ManagerReporting Tools Query Query ManagerUW Query Training.docPage 6 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingV. Creating a Basic QueryTerminologyRecord DefinitionsThe record definitions are the design specifications that determine the structure ofyour PeopleSoft application data tables and online processing.TablesThe table is made up of columns and rows. Columns determine how the data willbe stored. Rows represent the actual data stored in the database.Creating a Basic QueryStep 1. Navigate to the Query ManagerReporting Tools Query Query ManagerUW Query Training.docPage 7 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 2. Click on Create New QueryThis will open the Find an Existing Record search page where you select the table thatyou want to useIf you weren’t sure of the name of the table, you can leave that field blank. This is not themost efficient way to search because it will bring up the 1st 300 tables.Step 3. Enter UW HR in the begins with box and click on theStep 4. Find UW HR ALLJOB VW table and click on theAll the fields in the table will be available for selection.UW Query Training.docPage 8 of 20iconicon.
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 5. Choose the following fields:BUSINESS UNITDEPTIDEMPLIDNAMEEMPL RCDHIRE DTEXPECTED END DATEHR STATUSEMPL CLASSACTIONACTION REASONJOBCODEUW JOBCODE DESCRCOMPRATEFTEStep 6. Click on the Fields tabUW Query Training.docPage 9 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 7. Click on theAdd Criteria icon for HR STATUSThe Edit Criteria Properties page is displayed.Step 8. Enter HR Status in the Constant box and click on theicon.Be sure to enter the HR status as it is configured in the system, as PeopleSoft query iscase sensitive.UW Query Training.docPage 10 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 9. Click on theUW Query Training.docAdd Criteria icon for EMPL CLASSPage 11 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 10. Enter your employee classification in the Constant box and click on theicon.Step 11. Click on the Run tab.The query results will automatically be displayed in a grid format.UW Query Training.docPage 12 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingThe results of your query can now be saved as an Excel spreadsheet.Step 12. To save the query click on theicon.Step 13. Enter a query name and click on theicon.Queries can be saved Private or Public queries. If you are saving the query as a publicquery, you will want to use the naming conventions developed by the organization.UW Query Training.docPage 13 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingVI. Creating a Query using Multiple TablesStep 1. Navigate to the Query ManagerReporting Tools Query Query ManagerUW Query Training.docPage 14 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 2. Click on Create New QueryThis will open the Find an Existing Record search page where you select the table thatyou want to useStep 3. Enter UW HR in the begins with box and click on theStep 4. Find UW HR ALLJOB VW table and click on theUW Query Training.docPage 15 of 20icon.icon.
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 5. Choose the following fields:BUSINESS UNITDEPTIDEMPLIDNAMEStep 6. Click on the Records tab. Find UW HR ADDRES VW and click on theicon.UW Query Training.docPage 16 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingThe Select join type page is displayed.Using Query Manager, you can create a join between two records (any record join) byselecting your initial base record, defining its output fields and associated criteria, andthen returning to the Record page to select the second record.If you have AutoJoin Wizard option enabled, then PeopleSoft Query automaticallyattempts to join the new record to the existing record by looking for matching columns onthe two records. This function does not always create the correct join.Step 7. Click onicon.The Auto Join automatically brings back the fields it has determined that the join shouldbe made on.Step 8. Click on theicon.Step 9. Choose the following fields from UW HR ADDRES VW:ADDRESS TYPEADDR TYPE DESCRADDRESS1ADDRESS2ADDRESS3ADDRESS4UW Query Training.docPage 17 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 10. Click on the Fields tab.Step 11. Click on theUW Query Training.docAdd Criteria icon for BUSINESS UNITPage 18 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 12. Enter your business unit in the Constant box and click on theicon.Step 13. Click on the Run page. The query results will automatically be displayed in agrid format.UW Query Training.docPage 19 of 20
University of Wisconsin SystemHRS ProjectPS Query TrainingStep 14. Enter a Query Definition and save the query as a private query.UW Query Training.docPage 20 of 20
PeopleSoft Query provides the following different types of queries: User queries . Create and run queries to retrieve data from the database directly from Windows-based Query Designer, or the web-based Query Manager/Query Viewer applications. Process queries. Write queries that are intended to run periodically by batch processes, most likely using
