This article guides you through the creation of a PeopleSoft® Query specific to HR data.
Detailed Information
Join the PS Query Group! Have you joined the UMS PS Query User Group yet? You can ask questions or share tips and more.
Table of contents
Query Planning
In our sample query, we’ll proceed as if we were asked to produce a list of employees for a department and needed the following information: Employee ID, Name, Job Code, Regular/Temp Status, Employee Class, FTE, and Employment Status.
Planning can be simple; you may just need to determine which fields you need and which records those fields are in. For more complicated queries, you may need to decide which tactic to take beforehand. A query asking for all vacation time taken by employees in the engineering college might be better approached by finding all of the engineering employees and then their vacation time. A query with a wider scope might be better approached by first finding reported vacation time and then finding the employees that match that criteria.
Tip: There are many great public queries available in Query Manager that have already been written. It might be easier to edit the existing query rather than creating a completely new query.
Back to Top
Considerations
There are basic questions that should be answerable before writing queries:
- What are ‘Active’ employees?
- Employees might be on leave, on sabbatical, suspended, or on work break. Should they be counted?
- Should you count temps? Students? Graduate Assistants?
- What constitutes your faculty? Coaches? PATFA? Non-represented part-time?
- When you are asked for numbers, do they want headcounts or FTE?
- Always consider who will be reading your data. Will they understand the codes or the terms used?
The PATFA issue:
- Some are temporary, and some are regular
- Many have multiple Active Jobs; this includes different HR Departments and Campuses.
- The FTE listed on each Job Record has not been consistently or accurately maintained.
- They all have a union code
- They are in status ‘Active’ while on break. This accommodates the population of PATFA Union Service Lists for a total of 6 Academic Year Semesters, regardless of continued teaching.
- The term "adjunct" is not used in HR. They are ‘faculty’.
There are many more issues depending on the data you are looking at. Each campus should decide how it wants to cover these issues and adhere to standards so that data retains some consistency from one query to another.
Back to Top
Finding Your Data
Before you create a query, you need to know which record (table) your data lives in. It is the most difficult part of writing a query is just trying to find the data you want to query on. Please read the Finding Your Data page for more information. In this specific example, all data can be found in a JOB table.
To start our query, we want to click the ‘Create New Query’ link in the Query Manager. Upon starting a new query, the tool instantly asks you to pick a record. For most queries, you will want to start with a record that will give you as many of the fields as you need, or holds the fields that have the majority of the criteria. In this example, type "JOB" in the search box and the list of records that contain "JOB" will show up.
We want the JOB record, so click ‘Add Record’ to use it in your query. The ‘Show Fields’ will show all of the fields in the record if you are unsure about the record.
Whenever you use a record that has effective dating, you will get this warning.
What is important to know when you see this message is that the database will automatically give you the most recent row for the criteria. If you remove the effective dating criteria, this query will return every job row ever created for the employees you select. Please read the Effective Date page for more information.
For now, simply click OK.
Back to Top
Select Your Fields
For simple queries, you can do almost everything you need right from this screen. (The "Query" tab)
Simply put a checkmark in the box next to the fields you want, and remove the check mark for any fields you found you did not need in the query. If you don’t mind a lot of data, you can even click all of the fields in a record. But be careful, there are limits on the amount of data you can return in one query. You can even remove the entire record by clicking the "Uncheck All Fields" icon.
Pressing the icon of a minus folder will collapse the fields. The little icon in the top right corner labeled “A-Z” will put the field names in alphabetical order. It will make it easier to find the specific fields you need for your query.
For our query, you’ll need to find the following fields in the JOB record:
- EMPLID
- DEPTID
- JOBCODE
- EMPL_CLASS
- EMPL_STATUS
- FTE
- and the field that determines if an employee is regular or temporary.
What doesn’t exist in the JOB record is any personal information on the employee. One of the goals of databases is not to be redundant; it doesn’t make any sense to store data in multiple places, even if it would be more convenient for the query writers. So while there may be many job rows for an employee, it would waste space in the database to store the employee’s name (and other pieces of personal information) every time an update was made; instead, each row has an EMPLID. The name is stored (with any changes) in a separate record. For our purposes, we will use the record PERSON_NAME to get the employee’s name.
Navigate to the tab labeled ‘Records’ and search for "PERSON_NAME". Click ‘Join Record’
When you add the additional record, it asks you how you want to make the join. You can either do a standard (inner) join or a left outer join. It is also asking you which record to join. Please read the Join Types page for more information.
In this example, we choose a standard join and join to the "JOB" record.
After selecting the records to join, it will then ask you for the fields to join. The tool automatically detects the fields and gives you suggestions.
At this point, it might be useful to note the aliases, or record indicators. If you look closely, you’ll notice that JOB was assigned an alias of A. What the screen above is telling you is that it will join two records: JOB, which it will call A, and PERSON_NAME, which it calls B, and will perform the join by making sure the EMPLID in both records is equal. That is exactly what we want, so clicking the button labeled ‘Add Criteria’ will allow us to select fields from PERSON_NAME. When you select a name (from the many choices!), use NAME_PSFORMAT for our example.
Tip: Be careful when doing joins. Sometimes, the Query Manager is too helpful and tries to put too many fields in the join criteria. You always need at least one piece of join criteria, but too many will lead to zero data being returned.
Back to Top
Add Your Criteria
We will go to the "Fields" tab to add the first criterion to our query. The little funnels allow us to add a criterion to a specific field. Note that we can only pick the fields we previously selected here. If we wanted to add criteria based on fields that we do not want displayed, we should return to the "Query" tab and use the funnels there.
The criteria screen presents us with many choices. Basically, we need to form an equation in the form of X (CONDITION) Y. If X and Y meet the condition, then we want the data; otherwise, that data will not be returned by our query.
X is called ‘Expression 1’ in Query’s terms, and by using the funnel, it has already pre-filled that side of the equation with the DEPTID field from record alias A (JOB).
Back to Top
Condition Type
-
Equal to/Not equal to - The most basic condition and do a check for equality. For numbers, it is compared to any available decimals, e.g., 10.00000001 is not equal to 10, and any text must match in length and case.
-
Between/Not between - Perfect for date ranges, and in some cases, when looking at financial transactions to find a range of activity between two levels. Note that the between includes the values you use. Using between and looking for job rows dated between 1/1/2006 and 12/31/2006 will find entries on those dates as well as those between them.
-
Greater than/Less than - Great for finding large payments or transactions less than zero. Asking for dates greater than 1/1/2006 will not find anything on that day, only starting 1/2/2006 and later.
-
Not greater than/Not less than - “Not greater than” means “less than or equal to,” and “not less than” means “greater than or equal to.” Got that?
-
Is null/Is not null - Dates can be null -- this lets you find them or ignore them. See the Zero/Null/Blank discussion in Section VI.
-
In list/Not in list -Allows you to pick among a list of options, we’ll explain more fully below.
-
Like/Not Like - Like allows us to match strings based on partial matching. For example, using Like and ‘O%’ for our constant criteria will match every value that begins with an O, which would be perfect to find every department on the Orono campus.
-
Exists/Does not exist - This can only be used with subqueries; basically, it asks if X exists (or doesn’t) in another query.
-
In tree/Not in tree - No trees are used in the Human Resources database. It is used in Financials.
For more information, please go to the Changing Selections in PeopleSoft Query for Variables in Criteria page.
Back to Top
Save and Run the Query
Use the ‘Save’ button at the bottom of the page to save your query. The minimum you are required to do is provide a name for the query. If you are saving a ‘private’ query that only you can see. Please read the PS Query Best Practices page for more information.
Now you can run your query by clicking the "Run" tab on the right side of the query menu.
Back to Top
Cleaning Up the Query
Translation
If you return to the "Fields" tab, you’ll notice that there is a column labeled XLAT. This indicates whether or not the database will translate the value of the field into a human-readable form. An N indicates that translate is available, but not being used. Use the edit button to translate the field.
Let’s translate EMPL_STATUS to make our query more readable. All you need to do is choose if you want the ‘short’ or ‘long’ translation of a name. In almost all cases, the short name will be enough. When you return to the Fields tab, you’ll notice that the XLAT column now indicates S for a short translation.
Only fields that have a value in the XLAT column can be translated. However, a query can also suggest tables for possible translation. If you return to the Query tab and look at the fields in the JOB record, you’ll notice that certain fields have a recommended job option to the right of the field name.
In this case, let’s join JOBCODE to its own data table. We can add the DESCR field and use it to provide more information in our query.
While our original specification did not specify one way or the other, it is probably safe to assume that we only want active employees. This gives us a chance to use another criterion type. Add criteria for the EMPL_STATUS field and pick the condition of ‘in list’. You can’t type your Expression 2 here; you have to add your items from a list. Click the magnifying glass to get your choices.
When you select your choices, normally, Query will give you a list of possible values. In this case, it also provides the long and short translations of the field's values. Click ‘Add Value’ for any values you want to include in your query. For ‘active’ employees, I try to remember the mnemonic A, L, P, S, and W. Other combinations may be more appropriate for your campus or your specific needs. When you’re done selecting values, the criteria should look like below:
Column Order
One thing you can do to improve the readability of your query is to reorder the fields. I like to have key fields, such as EMPLID and NAME, to the left. It also makes more sense to have the JOBCODE close to its translation field. To change the order of the fields, navigate to the Fields tab and click the button labeled ‘Column Order.’
Sort
The Fields tab also has an option for sort order. You can get your data pre-sorted from the query and not need to sort it later when you bring the data to Excel or other software on your computer. In the screenshot below, you can see the NAME_PSFORMAT field has been designated as the primary sort and that it will sort Ascending (starting with A). The reason we selected the NAME_PSFORMAT is that the fact that it places the last name first, which lends itself to sorting.
Prompt
An important consideration when writing queries is that a query should be reusable. One of the easiest ways to enhance a query is to add a prompt value, which will allow you to change one or more pieces of criteria without having to re-edit the query. This also allows individuals who can not edit queries to use your query for multiple situations. Since our query reports on the employees in a department, we’ll change the query so it asks which department we would like to report on.
Navigate to the Criteria tab and click the ‘Edit’ button next to the DEPTID field. Then change the Expression 2 type from Constant to Prompt. In this case, we want a New Prompt, so we will click that link.
You might want to change the ‘Heading Text’ to "DeptID" or "Department." It will give a more readable prompt to the user. Click "OK".
Please read the Adding a New Prompt Using PeopleSoft Query page for more information.
Back to Top
Other functions in PS Query
Column Headings
When editing columns, there is a third option that allows you to change the heading of a column. Again, you want to click on ‘Edit’ next to a field, and in this case, we will be changing the heading for NAME_PSFORMAT, as by default it prints the unintuitive ‘LN, FN’ designation. Change the heading to ‘Text,’ which means it will use the value we type in the ‘Heading Text’ field. and type "Name" in the Heading Text box.
Aggregates
Query can do some calculations for you. When you are looking at the fields tab, one of the options is to click the ‘Edit’ button (this is also how you get to the translate options) and create an aggregate calculation on a field. This allows you to apply one of the aggregates to a column in the table. Consider our earlier example – we are returning a unique EmplId and Name for each row. Adding an aggregate to FTE, to summarize the total FTE count in the department, would do nothing to our query as is – it would only sum the FTE for each individual and produce the same results. However, if we were to remove every field except department and FTE and turn on the ‘Sum’ aggregate, our output would look like this:
Now, imagine if we were to remove the criteria to prompt on the department. Then our query would summarize the FTE for every DEPTID available (depending on security.) However, you should really consider if this query would really be the best for the job, or it would be better to start over and write a new query.
The other aggregates are also useful:
- Count -count the number of values that match each row returned. Changing our query to ‘Count’ instead of ‘Sum’ FTEs will effectively return a headcount for a department.
- Average -produce an average (arithmetic mean) of the values.
- Min/Max -find the minimum and maximum values – you might use this to find the lowest and highest paid individuals in a job code or salary grade.
Aggregates are useful, but in some ways, they are very limiting. All of these functions and many more are available in any spreadsheet software, and it may be useful to bring raw data back and summarize and analyze data there rather than directly in a query.
Note that aggregates should only be used on fields that make sense to be aggregated; there is no concept of a ‘Min’ name field or an ‘Average’ date in a database.
Back to Top
Expressions
There are two major uses for expressions:
- Do simple calculations on fields
- Use database features not directly accessible from the Query interface
The second option will not be covered in the course, but is one of the most powerful features of the query.
The first option allows you to do simple manipulations of the data to produce new values. Consider a request to determine what the impact of a 2% raise on all of the salaries in a department would be. You would first find the field that gives an employee's salary, and then create a mathematical expression. Navigate to the ‘Expressions’ tab and click ‘Add Expression’.
Be mindful of the expression type and length. The type needs to match your data. As with aggregation, expressions should only be used when they make sense. It simply will not know what to do if you try to multiply a name field by 80 hours, but it will allow you to create such an expression. The error won’t be flagged until you run the query and get a (sometimes unhelpful) error message. As much as any other area in query, you need to plan what you are trying to achieve before using an expression.
The ‘Length’ value determines the length of the field, and the ‘Decimals’ is used for numeric fields when you want a certain number of decimal places in your result. The decimals subtract from the overall length. A number defined as 4.2 will hold numbers from 99.99 to -99.99. You can find how values are stored in the database by looking at the ‘Fields’ tab. Although be warned, they are using a different notation, a 4.2 on the ‘Fields’ tab means 4 places to the left of the decimal and 2 to the right, or a 6.2 expression. Lastly, just because a field always uses numbers, it may be stored as a character string.
For our example, click the ‘Add Field’ link and find ANNUAL_RT in the job record. Using 8 and 2 for the length and decimals of a number field will be sufficient. Your expression should be like this:
Note that we did the math in the expression just as we would for a formula in Excel. For general usage, simple arithmetic will be the same. Expressions do not even need to involve fields that are already being returned, but do need to use fields in the records you have selected. Unfortunately, there is no way to determine the native type of a field without making it a returned field, so you can review it on the ‘Fields’ tab. Essentially, by creating an expression, you have created your very own field. As you can see from looking at the updated ‘Expressions’ tab, the value can be returned (click on ‘Use as Field’) or you can apply criteria to the value. By creating this expression, it will perform this equation on the annual rate for every row, and any criteria will be applied accordingly.
Tip: You can create a field with a blank. Type '' on the expression.
Back to Top
Renaming
Renaming queries is very easy. Simply select the query you want to rename and choose the ‘Rename Selected’ option. The screen looks like this:
Folders
Queries can be saved in folders for easy organization. After selecting the ‘Move to Folder’ option, you will be presented with the option of using a new folder or creating a new folder.
If I am now searching for queries, I have more than 300 and can only view the first 300 queries in the system. Changing my Folder View to just look at the ‘Test’ folder, I can easily find my queries.
Imagine if everyone filed their queries into folders appropriate if they, were a ‘Benefits’ or ‘Payroll’ query, or if campuses kept their own folders?
Add to Favorites
When you move queries to a list of favorites, you will be greeted with this list of queries as soon as you enter the query manager.
Deleting Queries
Don’t delete public queries. You can delete your own private queries.
Back to Top
Download data
There a several options for returning data from a query. You can have it placed directly into a spreadsheet by clicking on the ‘Download to Excel’ link. Any values are brought over as-is, so if you did any calculations or sorting, you get the values after the changes have been applied. The Excel option is available anywhere you run a query. In some cases, it might be worthwhile to get data that is better suited for easy manipulation in an external database.
The data can also be accessed in CSV (Comma Separated Values) format. The easiest way to get a CSV file is to run the query from either the Query Viewer or by using the ‘HTML’ option in the Query Manager when finding a query. CSV files are smaller and more universally compatible than Excel files.
Back to Top
UMS Reporting Tables/ Views
University of Maine System created its own tables or views to make reporting easier. In HR, there is a special record named UM_F_EMPL_VW. It combines many of the most commonly used fields into one table, and in many cases, even includes the translation fields separately. The ‘flat file’ originates because the data has been flattened, meaning there is no history – you always get the latest data.
There are disadvantages to using the flat file. The most obvious issue is that it offers no way to look at history. Also, it isn’t as friendly as other records – you may not get a list of options when creating an option, and there are little to no suggestions for bringing in other data as we did above with JOBDATA.
In many ways, however, using the flat file is the future of Query because of its ease of use and speed.
Back to Top
Environment
- PeopleSoft Human Resources (HRPRD)
- PeopleSoft Query