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 • 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

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

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

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

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

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

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

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

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

Query Results • Click on Excel SpreadSheet hyperlink

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

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

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

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

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

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

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

Edit Field Properties • Locate the SEX field • Select Edit

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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.

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

Condition Type

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

Find the field: Sex • Click Add Criteria button

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

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

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

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

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

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

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

Results

Criteria page

INTRODUCTION TO PEOPLESOFT QUERY