Transcription
Human Resources (HR) Query BasicsThis course will teach you the concepts and procedures involved in finding public queries,creating private queries, and running queries in PeopleSoft 9.1 Query Manager. Users will learnthe basics of Query Manager including, searching predefined HR Queries, managing queries,running queries, and exporting query results.Course ObjectivesBy the end of this course, you should be able to perform basic Query Manager functions: Access Query ManagerNavigate Query ManagerFind Public HR QueriesManage QueriesModify and Save Public QueriesRun QueriesAccessing HR Query in PeopleSoft 9.1HR Query is accessible through the NCSU MyPack Portal via a web browser.The portal can be accessed the following ways: Clicking on the MyPack Portal Link at the top of any NC State University web page Going to http://mypack.ncsu.edu URL.Once you have navigated to the MyPack Portal, Login with your Unity ID and Password.Portal LoginMyPack Portal login involves two steps:1. Select your Campus Affiliation.2. Enter your Unity ID and PasswordIf you experience problems with your Unity ID or Password while logging into MyPack Portalcontact the NC State Help Desk @ 919-515-HELP(4357)1
Query Tool NavigationFrom the Portal main page click on the Main Menu navigating to Human Resource Systems,Reporting Tools.The Query folder displays Query Manager, Query Viewer, and Process Monitor.User roles and permissions control access to Production and Reporting systems. Base useroptions will only display Query Manager and Query Viewer for the Reporting System. Query Manager: Allows users to create new queries and modify existing ones.Query Viewer: Provides read-only access to predefined queries. Users can search, run,print, and download query data.For quicker access to Query Manager select the MyLinks dropdown box to addthe page to your MyLinks from within MyPack Portal.2
Working with Existing QueriesUse Query Manager to execute, modify, rename, copy, and delete existing queries. Remember,Query Manager actions are based on your security. This means some functionality describedhere may not be available to you.Existing Query SearchThere are two methods for finding existing queries: Basic Search and Advanced Search. QueryManager opens in Basic Search mode. The default general search uses ‘begins with’comparison operator on the Query Name.Search for a QuerySelect Search Method: Advanced or BasicSelect Search By OptionIf using Advanced Search, Select Conditional Operator.Enter Search DataBasic or Advanced Search MethodBasic Search: Assumes that the criteria entered will be found at the beginning of a data string.Advanced Search: Select Advanced Search to open the search page. Use the ConditionalOperators in the drop-down list to define the search.Using Conditional Operators: In Advanced Search, Conditional Operators compare thesearch option with the value entered. (See appendix for further explanation)3
Defining Search by Option Access Group Name: Provides a list of queries based on user security access to thedatabase information. The search retrieves queries available to the security groupselected.Description: The common name used to describe the query.Folder Name: The folder where the query is stored. Enter the beginning of the foldername or use wildcards to search for queries in specific folders.Owner: Queries are saved as either Public or Private. Public queries can be executed,opened, modified, or deleted by anyone with proper security access. Private queries canonly be executed, opened, modified, or deleted by the individual who created the query.Type: Search for a query based on its function. The options for query type are Archive,Process, Role, and User. End-users will generally use the User query type.Uses Field Name: Search for queries using a specific field of data. A specific fieldname or the beginning of the field name can be entered. To be most specific, enter theexact field name.Uses Record Name: Search for queries using a specific record. A specific record nameor beginning of the name can be entered. To be most specific, enter the exact recordname.Search CriteriaEnter the terms you wish to search on in the Search field. Leaving the field blank will return amaximum of 300 queries, listed 50 per page.In the HR System, the (%) percent symbol is the most common wildcard character. Wildcardsare place holders for data when conducting a search. The wildcard expands your searchbeyond the ‘Begins with’ condition. %HR searches for data containing HR at the end of a string.%HR% searches for all data containing HR within the string.HR% searches for data containing HR at the beginning of a string.4
Public HR QueriesHuman Resources has provided a list of Public Queries which provides you with variousstandard reporting data. These queries can also be used as a starting point for you to customizeyour own private queries.To search for HR Public Queries:1. Select Query Name from the Search By drop-down box2. In the ‘begins with’ search field, enter HR%.Query Search ResultsResults are returned which meet the search criteria. You can edit, run to HTML, or run to Excelfrom the Search Select: Allows you to select multiple queries to perform an action. Check All selects allqueries listed. Uncheck All removes selections. Check box is not required to select Edit,HTML, or EXCEL.Query Name: The name of the query listed.Descr: Manually entered explanation of the queries actionsOwner: Public or Privately saved queries. Public queries require appropriate securityaccess to be opened, modified, or deleted. You can only modify, execute, open, ordelete queries created or owned by you.Folder: The folder the query is stored. Not required but folders are a good way to storeand filter queries for future searches.5
Filtering ResultsFolder View: Queries stored in folder help to organize and easily filter large result sets forqueries.Queries can be stored in folders when saved and Moved to a Folder from the Search Results.To move a query to a folder, click the Select checkbox and choose Move to Folder under theAction drop-down.6
Saving QueriesQuery manager allows you to save queries for you to use again later. In query manager youcan save a query as private or public. Private queries are for your express use, no one else hasaccess to the query. If you have the appropriate security you can save a query as public whichallows others direct access.How to Save a Query1.2.3.4.5.6.7.Select Save or Save AsDefine or Alter the Query NameDefine the DescriptionDefine FolderDefine Query TypeDefine the OwnerProvide Query DefinitionSelect Save or Save As7
Saving a QuerySelecting Save or Save As opens the Save Page. After clicking OK you commit the changes tothe query.Query NameChange the query name to something specific to you or your Division/Department. The Queryfield does not accept spaces so use underscores instead.DescriptionGive a short description of what the query does.FolderUse the folder as a way to organize your queries. This is an optional field.OwnerRequiring appropriate security permissions you can save your query as Public or Private. If theOwner drop-down is grayed out then you do not have permission to save the query as anythingbut private. Saving as a public query allows you to save queries for others to access. Saving asa private query only allows you to view.8
Activity 1Let’s take this opportunity to practice finding an existing query:1. Navigate to the Query Manager.2. Using the % wildcard option Search for queries which contain CLASS.3. Find the Query HR Class Query.4. Click on the Edit Link.5. Click SAVE ASa. Change the Query Name to XXX CLASS QUERY (replace XXX with yourinitials)b. Add a folder namec. Make sure the owner is privated. Click OKNOTES9
Working With RecordsMost of the queries you use in HR Query will already be provided to you as HR Public Queries.Sometimes those queries may not have a field you need. To customize the query you may needto add a record, also known as a table, to add the field to your query.In the HR System there are two types of records (tables), de-normalized and base. Access tothese records is based on your security profile. Most users will not have access to base tablesbut if you do it’s important to know how to identify them.Base tables require security access to view. These tables contain the data entered into the HRSystem over time. Most of the time you will not need base tables to complete your query tasks.De-normalized tables or denorm for short are views of multiple base tables brought together forease of use. They are identified by the prefix NC and contain a large number of fields. Theyprovide a four year snap-shot of the data entered into the HR System. This is important if youwant to list data over a date range. Anything with a date greater than years will not show in yourquery.Search By: To find a table you use the search field as you would to search for a query.Searching by record name is your best bet in finding the table you need.Show FieldsSelect Show Fields to preview the fields within the Record.Join RecordSelect Join Record to add additional records to the query.10
For a great resource for fields and records use the Public Query Documentation on the HRIMwebsite. It contains a list of Public Queries, what they do, and what tables are used.** Before customizing Public HR Queries please be sure to save and rename the query. **Working with FieldsFields are added to a query through the Query Page. Specific information regarding fieldproperties, display order, and sort order is defined on the Fields Page.Display the fields by clicking the folder under Chosen Records. Select the field by clicking in thecheckbox next to the field.Along the Gray Fields bar you can navigate through all of the fields within the Table.Find: Click it to search by field name to quickly locate a field.View 100: By default fields are grouped by 50, Click this link to list 100 fields at a time.Arrows: Scroll the Fields from first to last by clicking the arrows.Add FieldTo add a field, select the checkbox next to the field name. Clicking the Check All button willselect all fields. Selected fields display on the Fields Page.11
Column Display OrderThe HR System uses a default display order to fields appearing in the query results. Columnorder is displayed as listed on the Fields page. Use the Reorder / Sort button on the Fields pageto renumber the column order.To reorder your fields enter the field number under New Column. Once completed, the fields willbe displayed on the Fields page and your query results in the order defined.12
Changing Sort OrderA sort order is not defined for a query, results are displayed in the order the data was retrievedfrom the database. You change the data sort order by selecting the Reorder / Sort button. Use the Reorder/Sort button to access the Edit Field Ordering page. Enter the number for sort order in the New Order By field. To sort the field data in descending order, select the Order By Descending checkbox.13
Changing Column HeadingsColumn headings for query results use the RFT short name defined for the field in the HRsystem. Since customize the column heading in your query use the Edit Field Properties Page.Headings can be customized by selecting one of the following: No Heading: Column heading will be blank RFT Short: Short description for the column defined by the system RFT Long: Long description for the column defined by the system Text: User customized heading.You must select the Text radio button and change the Heading Text field. Otherwise, theselected heading type will be displayed.AggregateYou can aggregate your results by selecting one of the functions under Aggregate in the EditField Properties. By selecting Count, instead of returning the rows in the query results the querywill return a count of the rows returned by the query.14
TRANSLATE VALUESA translate value is a value used at the database to tie a code with a description. You can tell afield is defined as a translate value when the XLAT column contains a value. As an example,the EMPLOYEE CLASS field is a translate value.Each class is assigned a 3-letter code, the code is meaningless without the description. Byclicking the edit button on the Fields page you can change the field properties to Short or LongTranslate Value.15
DELETE FIELDSOn the Fields Page you can delete fields from a query. Select the delete iconfor the field tobe removed. Deleting a field does not remove it from any Expressions, Prompts, or Criteriadefined for the query.NOTES:16
ACTIVITY 2: WORKING WITH FIELDSSearch for the public query CLASS ACT2. Modify the query to list the contact information forActive Employees.Query Requirements:1. Select the fields: DEPTID, DESCR-Department, Name, ADDRESS1, ADDRESS2, City,STATE, POSTAL2. Using the Reorder/Sort button on the Fields page, order the columns: ID, NAME,DEPTID,DESCR, ADDRESS1, ADDRESS2, City, STATE, POSTAL3. Sort the output by Department ID (OUC), NAME4. Edit the Column headings: Employee ID, Department ID, Department, Street 1, Street 2,City, State, Postal5. Save the query6. Click RunNOTES:17
Working with CriteriaRunning a query without criteria returns all the data in a record for the fields you selected. Yourefine your query by giving instructions on how to filter your data. Types of criteria involvecomparing values in two separate fields or filtering data based on a constant value against afield. Data is filtered in the HR System by comparing data in Expression 1 to data in Expression2 using a Condition Type.DEFINE CRITERIATo add or edit criteria you have two options, the Criteria page or Query page. On the criteriapage you can use the Add Criteria button or edit by selecting the Edit button for the appropriateExpression.Add CriteriaEdit CriteriaCriteria definitions compare two different expressions. Define Expression 1 by Choosing a Field.To choose the fields select the magnifying glass iconDEFINE CRITERIA Expression 1: Define the data to be filteredSelect your Condition TypeExpression 2: Define the comparison data18
Expression 1: Filter DataField: If you are filtering a data field from a record use the Field option for Expression 1Expression: If you have created an expression to be filtered, use the Exression option.Expression 1Condition TypeExpression 2Condition TypeCondition Type is the comparison operator between Expression 1 and Expression 2. TheCondition Type selected determines the way Expression 1 is filtered by Expression 2.Between: The value of Expression 1 falls between and includes the two values defined inExpression 2.Not Between: The value of Expression 1 does not fall between and does not include the two valuesdefined.Greater Than: The value of Expression 1 is greater than the value of Expression 2.Not Greater Than: The value of Expression 1 is not greater than the value of Expression 2.In List: The value of Expression 1 is in a list of values defined in Expression 2.Not In List: The value of Expression 1 is not in a list of values defined in Expression 2.Is Null: The field selected for Expression 1 does not have a value. Null is different from 0 in that 0 isconsidered a value. If this condition type is selected, Expression 2 will not be defined. Key fields,required fields, character fields, and numeric fields do not allow null values.Is Not Null: The field selected for Expression 1 has a value. Null is different from 0 in that 0 isconsidered a value. If this condition type is selected, Expression 2 will not be defined.19
Condition Type OptionsLess Than: The value of Expression 1 is less than the value of Expression 2.Not Less Than: The value of Expression 1 is not less than the value of Expression 2Like: The value of Expression 1 matches a string pattern defined in Expression 2. Expression 2must contain a string of data containing wildcard characters.Not Like: The value of Expression 1 matches a string pattern defined in Expression 2. Expression 2must contain a string of data containing wildcard characters.Exists: The value of Expression 1 is contained in a subquery. All records matching are retrieved.Does Not Exist: The value of Expression 1 is not contained in a subquery. All records notcontained in the subquery are retrieved.20
Expression 2: Comparison DataExpression Types for Expression 2 are active depending on the Condition Operator selected. Field: If the comparison data is contained in a record, use the Field option to define Expression2. Use the Lookup Tool to search for a field used in the query. Expression: If the data being compared is based on an expression, select the Expressionoption and build the expression using SQL. Add Prompt: Select Add Prompt if the expression will be calculated based on data entered bythe user. Add Field: Select to add a field to the expression. Constant: Compares Expression 1 to a constant value.Subquery: Compares Expression 1 to data returned in a subquery.Prompt: Compares Expression 1 to user-entered data in a prompt.In List: Compares Expression 1 to a list of values. This option is only available when theCondition Operators In List or Not In List is selected. Current Date: Compares the date value in Expression 1 to the current system date. Tree Option: Compares Expression 1 to selected tree nodes. This option is only available whenthe Condition Operates In Tree or Not In Tree is selected. Effective Sequence: Used on some effective-dated records.21
NOTES:22
CLASS ACTIVITY 3Develop a query from the NC ASSIGN EMP VW record that lists all active SPA, full-timeemployees with a FTE greater than .75.1. Search for Query CLASS ACT3, Save as XXX CLASS ACT3 (replace XXX with yourinitials)2. Order (Sort) the output by DEPTID, then by JOBCODE3. Select Active Employee Criteriaa. On the Criteria tab, Select the Add Criteria buttonb. In Expression 1, Choose the Field EMPL STATUS (Payroll Status)c. Select the In List condition type from the Condition Type drop down boxd. Under Expression 2, Add the List Members for Active Employees.e. Click OK4. Select SPA Employee Classa. On the Criteria tab, Select the Add Criteria buttonb. In Expression 1, Choose the Field: EMPL CLASSc. Condition Type can remain equal to unless you need to look for Police Officersin your Unit (Employee Class SLE)d. In Expression 2, Enter SPA in the Constant field.e. Click OK5. Add the FTE criteriaa. On the Criteria tab, Select the Add Criteria buttonb. In Expression 1, Choose the Field: FTEc. Select the greater than condition type from the Condition Type drop down boxd. Under Expression 2, enter .7499.e. Click OK6. Save and Run Query23
EFFECTIVE DATE CRITERIAEffective dated records provide a history of that data. When records are added or updated anew Effective Date is assigned to the record.In Query Manager, if the Effective Date is a Key Field the Effective Date criteria areautomatically defined. By default the criteria retrieves the most recent or current record.24
EFFECTIVE DATE CONDITION TYPESSpecific Condition Operators are used to compare Effective Dates. Below are the ConditionTypes available when Effective Date is selected in Expression 1Eff Date : Retrieves records where the Effective Date is less than the value entered inExpression 2.Eff Date : Retrieves records where the Effective Date is less than or equal to the valueentered in Expression 2.Eff Date : Retrieves records where the Effective Date is greater than the value entered inExpression 2.Eff Date : Retrieves records where the Effective Date is greater than or equal to the valueentered in Expression 2.First Effective Date: Retrieves the first instance of the record entered.Last Effective Date: Retrieves the last instance of the record entered.25
EFFECTIVE DATE COMPARISONDefine Expression 2 using one of the following options: Field: Compares a date field from a record with the Effective Date in Expression 1. Usethis option to retrieve records that were effective on the same date as another record.Expression: Compares the results of an expression with the Effective Date inExpression 1.Constant: Compares the Effective Date in Expression 1 against the date entered inExpression 2. Use this option to retrieve data as of a certain date.Current Date: Compares the Effective Date in Expression 1 against the current systemdate.MULTIPLE CRITERIA STATEMENTSAs criteria are added to the query, Query Manager compiles the criteria relationshipsusing standard Logical Operators. The Criteria Page is used to display and edit thecriteria logic between statements.Joining CriteriaFor a field, criteria statements are created individually and then joined to form the overall logic ofthe criteria. As you add criteria to the Query Manager uses the AND logic operator to joinstatements. You will notice there is no logic operator on the first statement on the Criteria Page.Here are the Logic Operator options available to use between statements: AND: Retrieves records where all criteria conditions are met.AND NOT: Retrieves records meeting the condition of the previous criteria but omitsrecords meeting the next condition.OR: Retrieves records where any of the conditions are met.OR NOT: Retrieves any records meeting the conditions of the previous criteria and anyrecords that do not meet the next conditions.26
The above criteria statements will evaluate and retrieve data in the following order:1. To pull the most recently updated records the Table will be searched for an effectivedate less than or equal to the current date; and2. The Employee Status is Active, on Leave of Absence, or Leave With Pay (A,L,P); and3. Action equal to HIR (Hire).The results for the query above will retrieve records for employees that are active with a Hireaction in their current record as of the date run.27
ACTIVITY 4: EFFECTIVE DATE CRITERIALet’s create a query to list all active employees within your department that were hired betweentwo dates. Search for the query CLASS ACT4, save the query as XXX CLASS ACT4 (replaceXXX with your initials).1. From the Query Tab, Select the fields: EMPLID, EFFDT, DEPTID, DESCR –Department2. Select Active Employee Criteriaa. On the Criteria tab, Select the Add Criteria link ( the funnel icon)b. In Expression 1, Choose the Field EMPL STATUS (Payroll Status)c. Select the In List condition type from the Condition Type drop down boxd. Under Expression 2, Add the List Members for Active Employees.e. Click OK3. Enter the Hire Actiona. Under Expression 1 Select the ACTION Fieldb. Make sure the Condition Type is equal toc. Type in the constant HIR into Expression 24. Enter the Date Range criteriaa. From the Criteria Tab click on the Edit button for the EFFDT criteriab. Change the Condition Type to betweenc. In Expression 2 enter two dates you want to know about. (Don’t forget thatDenorm tables only go back four years.)28
REORDERING CRITERIAQuery Manager adds the criteria relationships and evaluates them in the order they aredefined. There are times when you will need to reorder the criteria to apply grouping to thecriteria. Select the Reorder Criteria button from the Criteria Page Enter the New Position for the criteria on the Edit Criteria Ordering page Click OKGROUPING CRITERIACriteria statements are evaluated based on the rules of logic for the order of operations.Parentheses are used to group criteria to be evaluated first. AND Statements are evaluatedbefore OR statements.Order of Operations1. Grouped statements2. AND3. OR29
On the Criteria Tab select the Group Criteria button. Enter the Left and Right Parentheses around the statement to be grouped. Click OKThe above criteria will be evaluated in the following order:1. Records with null CONTRACT BEGIN DT or CONTRACT END DT ; and2. Records will be searched for an effective date less than or equal to the Current Dateto pull back the most recent record ; and3. Filter those records for those with Active Employee Status and Employee Class isEPA (EFX, ES1, ES2, ENF, ECX) ; and4. The Anticipated Appointment Length does not include At-Will or Tenured. (1,2)30
DISTINCT CRITERIASometimes data matches more than one criteria in a query, this causes duplicate rows in yourdata. To prevent duplicates use the distinct check box under properties.31
DELETING CRITERIAFrom the Criteria Tab select the Delete icon for the criteria statement you want toremove. Deleting the criteria does not remove any fields or records from the query.Running Your QueriesWithin Query Manager there are two ways to run your queries; from the main Query Searchpage you can run your queries to HTML, Excel, and XML.While creating/editing your query you can click the Run tab to preview your results. Satisfiedwith the preview you can download the results to Excel or XML.Run Tab Results Download32
Query Search Results DownloadUSING PROMPTSPrompts are used to narrow query results. They allow you to enter query variables before aquery will run. After the variables are entered you are able to view results. Date prompts havespecific formats such Century and offer a calendar to enter the date into the prompt. Tableprompts offer a lookup table to enter criteria.CREATING PROMPTSTo create a prompt, access the Prompt page and click on Add Prompt button.In Edit Prompt Properties, you can select the Type, Format, and Customize the HeadingText.33
The final step is attaching the prompt to the criteria you want to enter when you run the query. Add/Edit the Criteria you want to attach the prompts Set the appropriate Condition Type Change the Expression 2 Type to Expression ( For 2 prompt date fields use Expr-Expr) Use the Add Prompt link34
Prompt ConstructionWhen creating date prompts remember that the De-normalized tables go back four years of thecurrent system date. If you need to run your query further back than four years you will need torun your query from a Base table.35
Employee Class Codes (As of 10/01/2012)Employee Class Codes (Prior to 10/01/2012)36
Employee Status37
Conditional Operators BeginswithBetweenResults are less than the criteria entered.Results are less than or equal to the criteria entered.Results are exactly equal to the criteria entered.Results are greater than the criteria entered.Results are greater than or equal to the criteria entered.Results begin with the criteria entered.Results fall within and include the range entered. Separate range withcommas.Contains Results contain some form of the criteria entered. Entering beg wouldreturn Beginning, Beg., etc.InResults presented have one or more of the criteria entered. Separatecriteria with commas. Entering Ledger and Bank would produceresults containing either ledger or bank.Not Results do not include the criteria entered. Entering Ledger willprevent Ledger from returning.38
creating private queries, and running queries in PeopleSoft 9.1 Query Manager. Users will learn the basics of Query Manager including, searching predefined HR Queries, managing queries, running queries, and exporting query results. Course Objectives . By the end of this course, you should be able to perform basic Query Manager functions:
