1 / 102

INTRODUCTION TO PEOPLESOFT QUERY

INTRODUCTION TO PEOPLESOFT QUERY. AGENDA. Overview PeopleSoft Query Running Queries Writing Queries Advanced Topics Multiple Table Queries Prompted Queries. GROUND RULES. Frequently we’ll be navigating together as a class in order to all see the same screens at the same time

luistejada
Download Presentation

INTRODUCTION TO PEOPLESOFT QUERY

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INTRODUCTION TO PEOPLESOFT QUERY

  2. AGENDA • Overview • PeopleSoft Query • Running Queries • Writing Queries • Advanced Topics • Multiple Table Queries • Prompted Queries

  3. GROUND RULES • Frequently we’ll be navigating together as a class in order to all see the same screens at the same time • Don’t go exploring on your own • Let me know immediately if you are having trouble navigating to the next page • I’ll let you know when you can go ahead on your own

  4. OVERVIEW • PeopleSoft Query • Basic ad hoc reporting tool • Allows you to write ad hoc queries and run queries shared by others • Allows data to be extracted to Excel or in other formats • A more powerful version is available, but requires the PeopleSoft windows client component. • HRIS staff • Advanced Query users • Online Reference: • http://peoplesoft.oxy.com:88/hrbooks/eng/psbooks/tpsq/book.htm • Chapter 7 - Using Query Manager on the Web

  5. BASIC QUERY • Six steps to creating a basic query • Select the record(s) (data tables) • Select the data field(s) • Set the query preferences • Edit the field properties • Save the query • Run the query

  6. PEOPLESOFT SANDBOX • We’ll be developing our queries in the PeopleSoft Sandbox database. • This is a sample database supplied by PeopleSoft to allow us to get familiar with the PeopleSoft application. • Safer environment for query testing than the conversion database. • http://peoplesoftdr.oxy.com:88/servlets/iclientservlet/hr83snd/ • Use UserId PS and Password PS

  7. SAVED QUERY • You can access and run queries that you or others have saved in the past • Public Queries are queries that you or others have saved • Private Queries are queries that you have saved • When using a Public Query, • Always save the query under a new name before making any changes if you did not create the query • OK to Run a Public Query without saving it under another name

  8. Using a Saved Query • Follow along with me on this exercise • Navigate to Home > PeopleTools > Query Manager > Use > Query Manager • Enter OX_HR in the Search For box • Click on Search button

  9. Note: • the list of query names & descriptions • Ownership (Public or Private) • Delete, Rename and Run hyperlinks • You can open an existing query

  10. You can open an existing query by clicking on the query name hyperlink • Do not delete or rename a query created by another person • Click on OX_LIST

  11. This takes us to the Field tab of the query • We’ll take a closer look at this later • Click the Run button

  12. Query Results • Click on Excel SpreadSheet hyperlink

  13. Wow! • You can now format your report as you normally would with any other Excel spreadsheet, save the report or e-mail it to another person. • Close the window

  14. Create New Query • Follow along with me on this exercise • Navigate to: PeopleTools > Query Manager > Use > Query Manager • Click on Create New Query

  15. Record Selection • The first step is to select a record • Note search options • Search In • Search Type • Enter Personal in the search box to find the Personal Data record

  16. Folder icon to display fields • Record name description • Add record hyperlink to select the record for this query • Locate PERSONAL_DATA, click Add Record

  17. Note • Folder icon • Alias • Record • Hierarchy Join • Minus Button • Choose Fields • Checkbox • Key Symbol • Field • Add Criteria button • Join Record Name

  18. Select the Fields • Select the checkboxes for the following fields: • EMPLID • NAME • ORIG_HIRE_DT • SEX Select the Fields tab

  19. •Col •Field •Format •Ord •XLAT •Agg •Heading Text •Add Criteria Button •Edit Button •Minus Button

  20. Edit Field Properties • Locate the SEX field • Select Edit

  21. Column Number • Order By • Number • Descending • Heading • No Heading Text • Text • RFT Short • RFT Long • Heading Text • Unique Field Name • Aggregate • Translate Value • None • Short • Long

  22. Edit The Data Column Number: 3 Order By: 1 Heading: Text Heading Text: Gender Aggregate: None Translate Value: Short Click OK

  23. The SEX field has moved above the ORIG_HIRE_DT field, it is designated as the primary sort, will display the short translate value rather than a code and will display the column head as Gender • Edit a few more field properties for practice Edit field EMPLID Heading Text: Employee ID Order By: 1

  24. Select the View SQL tab • The query tool automatically generates SQL (Structured Query Language) code for you. • SQL will look familiar to those of you who wrote queries in the past using QMF and is useful in troubleshooting problems with a query.

  25. Saving Queries • Once you have created a query, you will need to save it prior to running it. • Click SAVE • Enter • Query Name • Description • Owner • Private • Public

  26. Enter Query Name: EE_LIST Description: General Info About Employees Owner: Private • Click OK • Then click the Properties tab

  27. The properties page allows you to maintain information related to the query • Query Name, Description and Owner defaulted from the Save Page • Query Type defaults to User • Distinct checkbox eliminates duplicate rows (current problem in Oxy PeopleSoft environment) • Query Definition allows further documentation about the query • Status Box displays the history of the last save of the query • Click Run to execute the query

  28. Examine your data • Note the Download Results options • Close this window by clicking the “X” in the upper right corner

  29. Key Points • There are six steps to creating a query • Select the record(s) • Select the data field(s) • Set the query preferences • Edit the field properties • Save the query • Run the query • For translate fields, the long or short descriptions can be displayed rather than the code • Queries can be saved as public or private • The SQL statement generated by a query can be viewed by selecting the View SQL tab

  30. You have been asked to produce a query that displays the hire and termination dates of all employees and eliminate duplicates. Individual Activity #1

  31. Did you remember to: • Change field headings • Order by termination date in decending order • When discussion over, click New Query hyperlink

  32. You have been asked to produce an updated list of employees emergency contacts. Individual Activity #2

  33. Did you remember to: • Change headings? • Use short translate value for Relationship? • Order by EmplID and Contact Name? • When discussion over, click New Query hyperlink

  34. Selection Criteria • We will add criteria rows to a query to return specific rows of data • Compare fields to find data matching specified conditions

  35. Create a basic query to capture employee data and save the query without running it. Selection Criteria

  36. Did you remember to make the following field edits? • Change Name to the first field and sort by name? • Use the Short Name for the PER_STATUS translation type? • Name the query and give it a description? • When we are all ready, click the Criteria tab and then the Add Criteria button.

  37. Choose Expression 1 Type • Used to specify what you are comparing • Field • Expression • Note that your selection of Expression 1 Type changes the display for Expression 1 • Expression 1 • Select the field or expression you want to compare • Field • Expression • Next we’ll consider Condition Type

  38. Condition Type

  39. Check the different Expression 2 Types • Field • Expression • Constant • Prompt • Click the Cancel button • Click the Query tab

  40. Find the field: Sex • Click Add Criteria button

  41. Select condition type: equal to • Expression type: Constant • Expression 2: F • Click OK

  42. You can apply criteria to fields that are not in the query output • You need to know how the data is stored in the database to ensure accurate results when the query is run • Click Save then Run to view the output

  43. In addition to your current criteria, we now want to retrieve just employees who have a first name, middle initial or last name that begins with the capital letter “D”. • From the Criteria page, click the Add Criteria Button

  44. Select Expression 1 Type: Field • Expression 1 click Find Value prompt button • Select Name field from the list • Condition type: like • Expression 2 Type: Constant • Expression 2: %D% • The Like operator is case sensitive and uses wildcard characters to search for data • Wildcard characters are: % Any string of zero or more characters __ (underscore) Any single character • Click OK

  45. Click Save • Note the And/Or drop down list • Click Run

  46. You now have a female employees who have a capital “D” in their name

  47. Produce a list of all employees whose Business Title contains the word “Manager”. Also, only show those who were hired after 1989. Individual Activity #3

  48. Results

  49. Criteria page

More Related