INTRODUCTION TO PEOPLESOFT QUERY

1. INTRODUCTION TO PEOPLESOFT QUERY Revised: August 2008

2. OVERVIEW • PeopleSoft Query • Basic ad hoc reporting tool • Allows you to write ad hoc queries and also run queries shared by others • Allows data to be extracted to Excel

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

4. 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 which are accessible by anyone • Private Queries are queries that you have saved which are only accessible by you • When using a Public Query: • If you did not create the query: always save the query under a new name before making any changes • OK to Run a Public Query ‘as is’ without saving it under another name

5. NAVIGATING TO QUERY: Reporting Tools > Query > Query Manager

6. Using a Saved Query • Follow along with me on this exercise • Navigate to: Reporting Tools > Query > Query Manager Enter EM in the “Begins with” search box • Click on Search button

7. Note: • The list of query names & descriptions • Ownership (Public or Private) • Edit, Run to HTML, and Excel hyperlinks

8. You can open an existing query by clicking on the Edit hyperlink for the query you want to run • Using the “Action” drop down box you can delete, move to folder, rename or copy a query to someone else • Do NOT delete or rename a query that was created by someone else • Click on EMP_LIST_SEN_BRTH_JMS

9. This takes us to the Fields tab of the query • We’ll take a closer look at this later • Click the Run Tab

10. Query Results:

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

12. Alternate Execution Method • Click the HTML hyperlink for the query you want to run

13. Query Results • Note the report heading

14. Creating a New Query • Follow along with me on this exercise • Navigate to: - Reporting Tools > Query > Query Manager   - Click on Create New Query

15. Record Selection • The first step is to select a data record • Note the search options: • Search By (defaults to ‘Record Name’) • Use Drop down box to see other options • Enter Personal in the search box to find the Personal Data record

16. NOTE: • Record name description • “Add Record” hyperlink to select the record for this query • “Show Fields” hyperlink to view the fields in the record • Find the PERSONAL_DATA record & click Add Record

17. Note: • Folder icon • Alias Record • Hierarchy Join • Minus Button Fields: • Check box(es) • Key Symbol • Field names • Criteria icon • Join Record Names

18. Select the Fields • Click on the checkboxes for the following fields: • EMPLID • NAME • SEX • Birthdate • Click on the Fields tab

19. NOTE the following headings: • Agg • Heading Text • Add Criteria • Edit • Delete • Col • Record.Fieldname • Format • Ord • XLAT

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

21. Your edit choices: • Heading • No Heading • Text • RFT Short • RFT Long • Heading Text • Unique Field Name • Aggregate values • Translate Value • None • Short • Long

22. Edit the data as follows: Heading: Text Heading Text: Gender Aggregate: None Translate Value: Short Click OK

23. Click on Reorder/Sort • Move Birthdate to Col 3 • In the New Order By column: Make Sex sort order “1” • Click OK

24. Note the following changes: • The Birthdate field has moved above the Sex field • The sex field is now designated as the primary sort • The short translate value will be displayed rather than a code • Column heading will show as “Gender” • Edit a few more field properties for practice Edit field EMPLID Heading Text: Employee ID Order By: 1

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

26. Saving Queries • Once you have created a query, you may want to save it prior to running it. • Click SAVE (naming standards follow) • Enter: • Query Name • Description • Owner • Private • Public

27. Naming Standards • Spaces & special characters are NOT allowed • All query names should begin with “OX” followed by: • Department abbreviation (e.g. “BN” for Benefits)  see PS Query Help Home Page • Descriptive name • Initials of the person who created the query Example: OX_Department Abbr_Descriptive Name_Initials OX_Ben_ Employee_Listing_KP

28. Enter: Query Name: OX_GBLCO_EE_LIST_INITIALS Description: General Info About Employees Owner: Public • Click OK • Then click the Properties link (at bottom)

29. The “Properties” page allows you to maintain descriptive 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 (sporadic - current problem in Oxy PS environment) • Query Definition allows further documentation about the query • Status Box displays the history of the last save of the query • Click OK & then Run to execute the query

30. Examine your data • Note the “Download to Excel” option • Close this window by clicking the “X” in the upper right corner

31. Saving a query as a “favorite” After you create your query: • Click the Return to Search button • Enter the query name in the search box:

32. Saving your query as a “favorite” • Check the Select box • Click on the “Action” drop down arrow • Select Add to Favorites • Click GO

33. Key Points • There are six steps to creating a query: • Select the record(s)  i.e. Data Tables • Select the data field(s) • Set the query criteria • Edit the field properties (optional) • Save the query (optional) • 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 (for advanced query users)

34. You have been asked to create a query that displays the hire and termination dates of all employees and eliminate duplicates (see page 32). Individual Activity #1

35. Did you remember to: • Change field headings • Order by termination date in decending order • Click on the New Query hyperlink

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

37. Did you remember to: • Change headings? • Use short translate value for Relationship? • Order by EmplID and Contact Name? • Click on the New Query hyperlink

38. Selection Criteria • Next, we will add criteria rows to a query to return specific rows of data • Compare fields to find data matching a specific selection criteria

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

40. 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 Mar_Status translation type? • Name the query and give it a description? • When you’re ready, click the Criteria tab and then the Add Criteriabutton.

41. Choose Expression 1 Type: Used to specify what you are comparing • Field • Expression (i.e calculation) NOTE: Your selection of Expression 1 Type changes the display for Expression 1 on the right • Expression 1: • Select the field (or Expression) you want to compare • Field • Expression • Next we’ll consider Condition Type

42. Condition Types

43. Note the different Expression 2 Types: • Field • Expression • Constant • Prompt • Subquery • Click the Cancel button • Click the Query tab

44. Find the field: Sex • Click the Add Criteria icon (funnel)

45. Select Condition Type: equal to • Expression type: Constant • Expression 2: F • Click OK

46. You can apply criteria to fields that are not included 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

47. RESULTS

48. In addition to your current criteria, we now want to narrow the selection to retrieve only those employees who have the capital letter “D” in their first name, middle initial or last name. • From the Criteria tab, click the Add Criteria button

49. Select Expression 1 Type: Field • Expression 1: Click on the Search icon • 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

50. Click Save • Click Run NOTE the Logical drop down list (And/Or) for your criteria