PeopleSoft Intermediate Reporting

PropertiesThe Properties box lists the query name, indicates whether the query is public or private and specifies if the query isset to be distinct. Distinct queries will eliminate duplicate rows of information. The caveat of this approach is thatquery must evaluate every row in the table to select the distinct row. That can consume a great amount of timeespecially if large tables or records are included in the query. The Distinct button significantly slows response timeand performance, but it will give you one row.Click on the Cancel button to return to the query.[17]

Save AsYou may save a query under another name by selecting the Save As hyperlink option on the all tabs located in QueryManger (excluding Preview). The window below will appear once you have clicked on the Save As button. Enter thenew query name and select Owner type as Public or Private. Note that you have not renamed the query instead youhave simply created a copy of the existing query with a different name.[18]

CREATING QUERIESTo give you an idea of how Query works, let’s create an example query to find all active staff, and faculty that are inhome department ID X. Field results should contain EMPLID, Job Code and Department ID.Navigation: Reporting Query Manager Create New Query[19]

Adding a Record (Table)The information for our example query is contained in the Job table. Type Job into the Search box and click on theSearch button.AddRecord toQueryShowFieldscontainedin thisrecordYou may view the fields contained on the this record by selecting the hyperlink entitled Show Fields or add therecord (table) to the query by clicking on the hyperlink entitled Add Record next to the Recname JOB. Selecting theShow Fields hyperlink will list the fields contained in this record (table).A data dictionary containing the primary records is located at the end of this guide.Since the record JOB is an effective-dated table, an effective date is automatically added to the query. The defaultcriteria is set to return an effective date less than or equal to the current date. The current date is the data row (injob for our example) with the most recent effective date that is closest to today’s (system) date, but not a futuredate. Only one row is the current row. The dialog box below should appear on your screen whenever you add arecord that is effective dated. Select OK to proceed with creating a query.[20]

You will be able to modify this criterion before running the query if needed.Effective DateEffective-dated records are those whose record definition includes the field EFFDT. This Effective Date field is usedthrough PeopleSoft and allows you to have a historical perspective of the data. Furthermore, you are able to seehow the data changes over time. Whenever a row of data is added to the table, a new effective date is specified andPeopleSoft retains the previous version of the row as history.Effective SequenceYou may also modify the effective sequence. The options for effective sequence include first, last or all. Theeffective sequence when paired with Effective Date enables you to enter more than one row with the same effectivedate. A unique sequence number is assigned to each row that has the same effective date. For example, supposethat you want to enter both a transfer and a pay rate change for an employee, and both actions are effective on thesame day. Enter the transfer on the job data pages as usual, and leave the Effective Sequence Number field as 0(zero). Then, insert a row to enter the change in pay rate. This time, the effective date is identical to the previousrow. Enter 1 in the Effective Sequence Number field.In most cases, you will want to select Last Effective Sequence since it contains the latest information. Byselecting First Effective Date you would be pulling the 0 effective sequence row and may not contain the latestinformation. By selecting all effective sequences, you would be pulling multiple rows for employees if they havemultiple effective sequence data.[21]

Adding FieldsUtilize the Query Tab to select the fields to be included in the query results by placing a check in the box next to thedesired field name. Note that this record (JOB) contains more fields than the fields that are listed on this one page.To navigate through all the fields, utilize the navigational icons. You may also add all fields to the query result set byselecting the Check All Fields button, doing this will place a check mark in the box for every field contained on thisrecord. For our example query, select the following fields: EMPLID, Job Code and Department ID.Select the sorticon to sort thefieldsalphabeticallyfrom the selectedrecordUtilize thenavigation iconsto view all fieldscontained in thisrecordA check markin this boxindicates thatthe field willbe includedin the queryresult set.Once you have selected the fields for your query results, select the Criteria tab to place conditions on the query.[22]

Removing FieldsTo remove a field from the query results, simply uncheck a box located next to the appropriate field.Check All Fieldsis a short cut toadd ALL fieldsto this Query.Uncheck AllFields willautomaticallyuncheck allfields in thisquery.Un-checkingthe field boxremoves thefield from thequery.Activity: Add EMPLID, JOBCODE and DEPTID to the query.[23]

Modifying Field PropertiesThe field tab allows you to modify field properties, delete fields from your query result set and assign sorting order.Field Property Icon DefinitionsNote: To add afield, you mustreturn to theQuery Tab.The Edit icon allows you to edit the fields column order, thesortingorder of the query as well as the heading text that appears on the result set.The delete icon, when selected, will remove the field from the query resultset.The Record/Sort icon controls column order as well as sorting order. You mayalso utilize the Edit icon to control this for each individual field.Edit Field PropertiesOnce fields have been selected, you have the option of editing the field properties to enhance the displayed resultsof the query.Column OrderHeadingOrder By (Sorting)Aggregate[24]

Select the icon Edit next to the field you wish to modify, to apply formatting to a field.Column OrderEnter or modify a number in the column box to modify the column order. For instance, EMPLID will be returned inthe first column in our query result set. If you wanted the EMPLID field to be the second column in the query resultset, you would need to enter a number 2 in the column box.HeadingField headings can be modified to more accurately reflect the field description. To modify a heading, you may entertext in the heading text box and select OK.[25]

Activity: Change the heading for EMPLID from “ID” to “Employee ID”.Select OK to return to the fields tab.The heading text is now listed as Employee ID for the field EMPLID.Reorder/SortWhile you can reorder columns and sort fields using the edit field properties dialog box at the individual field level,the reorder/sort function allows you to reorder and sort all the fields on a single page.Click on the Reorder/Sort icon on the Fields tab[26]

Enter a numeric value to assign a column order and/or a sorting order. Select OK to save your changes.Activity:Sort ascending by Department ID.Add field Employee Status to this query.Translate ValuesThe record you are querying may include fields that use the Translate table. If so, the field itself contains a shortcode of some kind, for which the Translate table provides a set of corresponding values.In your query results, you might want to display the translated value rather than the code (for example, Activeinstead of A). To tell PeopleSoft Query to make this substitution specify L as the translate value.To utilize a translate value, specify which translate value you want to appear in the query results: N (none), S (short),or L (long).[27]

An “N” in the XLAT column indicates that this field contains data on the Translate table.Select the Edit icon for EMPL STATUSTo display the translate value for the field EMPL STATUS, select Long as the translate value.CriteriaOur PeopleSoft database contains a lot of information and a complete list of all the information is unlikely to answerthe question that prompted your query. To selectively retrieve just the data you need, you will define selectioncriteria. Selection criteria refine your query by placing conditions on the data that is retrieved.[28]

To edit the effective dated criteria, select the Edit icon.Edit Criteria PropertiesYou are now able to modify the effective dated criteria by modifying the condition type or the expression type (2).Select the Condition Type to alter the effective dated rows to be returned by the query. Options include greaterthan, less than, equal to, between, etc.Expression (1)In most cases, the left-hand expression will be a field whosevalue you want to base the selection criterion on. You can alsoenter any expression text by right-clicking on the grid cell andselection Expression.Condition TypeSelect the appropriate operator from the drop-down list, suchas ‘equal to” or “greater than”.[29]

Expression (2)When you right-click in the second Expression column, a popup menu appears enabling you to choose from the availablevalue types. Examples of value types are constants, fields andruntime prompts.Once you have made your selection, click on the OK button to return to the criteria tab.Selecting a Condition TypeThe condition type determines how Query compares the values of the first expression to the second expression.Click on the drop-down button for the condition type and select the appropriate comparison operator.Condition TypeWhen it Returns a RowEqual toThe value in the selected record field exactly matches thecomparison valueThe value in the selected record field is greater than thecomparison valueThe value in the selected record field is less than the comparisonvalueThe value in the selected record field matches one of thecomparison values in a listThe value in the selected record field falls between twocomparison values. The range is inclusive.The comparison value is a subquery. If the subquery returns anydata, Query returns the corresponding row.The value in the selected field matches a specified string pattern.The comparison value may be a string that contains wildcardcharacters. The wildcard character that Query recognizes are:% Matches any string of 0 or more characters (C%)Matches any single character. ( ones)The selected record field doesn’t have a value in it.The value in the selected field appears as a node in a tree createdwith tree manager.Greater thanLess thanIn ListBetweenExistsLikeIs nullIn tree[30]

Expression 2 Type: Entering Comparison ValuesThe process for entering comparison values differs depending on what type of value you have entered. If you arecomparing one field to another, select Field in the Expression 2 Type box; if you are comparing the rows to aconstant value, enter the constant in Expression 2 (Choose Record and Field). To enter a comparison value, selectExpression in the Choose Expression 2 Type box.The table bellows describes the available types.FieldExpressionConstantSubqueryPromptIn ListCurrent DateTree OptionEffective SeqThe value in another field, usually a field in another record component.An expression you create which Query evaluates before comparing theresult to the value in the selected field.A single fixed valueThe data returned by a subquery.A value that a user enters when running the query.A list of values that you enter. This value type is available only when theselected operator is “in list” or “not in list”The current date on the database server.A selected set of tree nodes. This value type is available only when theselected operator is “in tree” or “not in tree”.Used on some effective-dated records, the effective sequence is asequencing number provided to further refine the effective date.When selecting the “between” operator, the list of value types is different that the list used for the other operators.[31]

Activity: Modify the effective date criteria to generate data as of 01/01/2008.Add CriteriaSelect the Add Criteria button on the Criteria tab to place conditions on your query. For example purposes, addcriteria to return all active staff, faculty and research associates that are in home department ID X.Click on the Record Alias.Fieldname magnifying glass to select the field name EMPL STATUS.[32]

The first 50 fieldsfor the JOB recordare displayed.Utilize thenavigational iconsto review theremaining fields.Select Find tosearch for aparticular fieldSelect EMPL STATUS to select the field to add to the criteria.[33]

Define the constant (Active) by clicking on the magnifying glass.Activity: Click on the Select Constant hyperlink next to A (Active) to add the criteria to the query.[34]

Select OK to add the criteria to the query.Select Add Criteria button to add condition pertaining to Employee Class.Logical criteria refers to any rows after the first row must include either an “And” or “Or” logical value in thiscolumn to specify whether you want the rows to meet this criteria in addition to other criteria or as an alternativecriterion. The first criterion does not have a value in this box. The default for subsequent criteria is And.[35]

Change the condition type to In List since you are including more than one possible constant for employee class.Select the magnifying glass in the Define Constant box to select the possible classes.Click on the Search button to view all options[36]

Click on the Last hyperlink to view the end of the value list. Vanderbilt University utilizes the SetID VANDY foremployee class.[37]

Once the employee classes have been selected and appear in the List Members box, select Ok (at the bottom of thepage) to return to the criteria tab.If you select a value in error, place a check mark next to the incorrect value in the List Members box and select theDelete Checked Values button to remove the value from the selection.[38]

Select OK to return to the Criteria tab.Adding PromptsAdding a prompt allows you to further refine a query when you run it. For example, suppose you wanted to changea query so that you could prompt the user to enter a value for the effective date. Prior to adding the prompt, thequery always retrieved rows for employees who are active as of the date the query is ran. Adding a prompt to thequery allows the user to enter any date, then the query can return employees based on the value provided whenrunning the query.When you run a query with a prompt, a dialog box appears for you to specify the required value. Enter the value intothe text box. The query uses the value that you enter as the comparison value for the criterion that included theprompt.If the field for which you are prompting has an associated prompt table (even if it is the Translate table), the EditTable list box shows its name.For our example, create a prompt for deptid.[39]

1. Select the prompt tab.2. Select Add Prompt3. Select Field Name by clicking on the magnifying glass[40]

4. Enter “DEPTID” in the Search By Box5. Click Search6. Select DEPTIDThe Edit Prompt Properties dialog box allows you to modify the heading text as well as the format.7. Select Ok to save the prompt.Add the prompt to criteria.1. On the criteria tab click “Add Criteria”[41]

2. In the “Record Alias.Fieldname” select DEPTID.3. Select prompt from the Choose Expression 2 Type box.4. Select the prompt you created by selecting the magnifying glass next to the prompt (Expression 2, DefinePrompt box).[42]

5. Select prompt by clicking on the hyperlink.6. Click OKThe prompt has been added to the query.You may also add a prompt directly on the Criteria Tab by following the instructions below.Add CriteriaSelect Prompt from the Expression 2 Type boxSelect New Prompt from the Define Prompt boxSelect Field NameSelect Record and Field NameSelect OK to add to criteria[43]

Deleting PromptsTo delete a prompt, first remove the prompt from criteria and then delete the prompt.Go to the Criteria TabDelete the prompt from criteriaOpen the Prompts TabDelete the Prompt[44]

WORKING WITH MULTIPLE RECORDSQuery Manager enables you to create queries that include multiple-record joins. Joins retrieve data from more thanone record, presenting the data as if it came from one table. PeopleSoft Query links the tables, based on commoncolumns, and links the rows on the two tables by common values in the shared columns.Joins are what make relational databases relational. Using joins, you define relationships among fields when youquery the records, not when you create the records. Because PeopleSoft records are highly normalized (they eachdescribe one kind of entity), it is easy to use Query Manager to create joins.The procedure for joining records differs depending on how the records being joined are related to each other.Query Manager recognizes three types of joins: record hierarchy, related record, and any record.This section discusses how to:Create record hierarchy joins.Create related record joins.Create any record joins.Creating Record Hierarchy JoinsA record hierarchy joins a parent table to a child table. (A child table is a table that uses all the same key fields as itsparent, plus one or more additional keys.)Child recordsof JobExample: VU JOB DATA is a child record of JOB.[45]

Creating a record hierarchy join:1. Choose the base record for your query and select the appropriate fields and criteria.Select theHyperlinkHierarchyJoin to viewthe childtables.2. From the Query page, click the Hierarchy Join link.All of the records that have a parent/child relationship with your selected record appear.3. Select the record to join.[46]

In our example, we are selecting VU EARNS DIST.The join is reflected on the Query page.[47]

Creating Related Record JoinIn a related record join, you can automatically join two records based on a relationship that has been predefined inthe record designer. For example, if a field has a prompt table defined for it, then PeopleSoft Query displays a joinlink to the right of the shared field.The following example shows that the DEPT TBL record can be joined to the Job record by DEPTID.1. Choose the base record for your query and select the appropriate fields and criteria.2. From the Query page, click the required Related Record Joi

PeopleSoft Human Resource Management System (HRMS) provides support for general human resource functions. . Note that PeopleBooks and PeopleSoft query training books have been utilized to build this guide. . PS/nVision PS/nVision enables users to import information directly from query into predefined Ex