HRMS Query Builder Sirena Hardy HRMS Trainer

HRMS Query Builder Sirena Hardy HRMS Trainer

Learning Objectives What is Query Builder? Useful Terms Accessing Query Builder Building Your Query Running Your Query Creating New Local Report 2

What is Query Builder? The Query Builder (QB) module in HRMS, provides users the ability to develop their own “queries” of HRMS data. The results of these queries can be exported to other software such as Excel for further manipulation and reporting or the queries can be saved as Local Reports to be executed as needed. (Note that only HRMS Users with “System Administrator” or “Finance Officer” roles can access the QB.) 3

Useful Terms Query: Request for specific data from data tables. Field/Column : Individual data element (For example: First Name) Table: Group of related data fields (For example: Employee Table – has the employee’s ssn, name, date of birth, etc) Row: A set of Fields in a Table that are relevant to a specific individual or item. (Multiples rows in a table) 4

Useful Terms Report Group: Several “Tables” connected together. For example. “Employee License Personnel” Report Group. This Report Group allows you to get information from the HRMS Employee Table and Licensure Personnel table at the same time. 5

Useful Terms Report Filter: Data value(s) used to reduce the amount of data returned. For example, “where employee last name ‘Johnson’. The only data returned is for employees with the last name “Johnson” Sort By: Order in which the data is returned. For example, sorting by Site, Employee Last Name, then Employee First Name. SQL: Structured Query Language is the standard language used to communicate with relational database management systems. SQL statements are used to perform tasks such as update data in a database or retrieve data from a database. 6

Accessing Query Builder 7

Building Your Query The first section to display in the Query Builder (QB) is the “Report Group and Tables” section. The first step in the query creation is to select the Report Group or Individual Table which contains the desired data. Continuously rebuild SQL query – Always leave this option checked. When checked, the query you are creating will always be ready to execute since the SQL query being created is continuously updated 8

Building Your Query Select a Report Group: You can either select “Show all tables” or select one of the “Report Groups” “Show all tables” was selected in this example. All available tables displayed in the right column. 9

Building Your Query Select a Report Group: You can either select “Show all tables” or select one of the “Report Groups” The Assignments – Basic report group will be selected for this example. Report Groups combine related tables together. Most of the time you will want to select a “Report Group” for your query. Note that when you hold your mouse over a Report Group, a listing of tables included in that Report Group is displayed. 10

Building Your Query The tables will display on the right after you select the Report Group Now you can select one of the tables in the “Select a Table” window to start building your query Note that if you are in the middle of creating a query and decide to change the Report Group, all work done for this query is lost. Selections will all be reset. 11

Building Your Query In this example, the “Employee” table was selected in the “Select a Table” window After selecting a table, the “Build Your Query” heading appears along with 3 sections; “Current Action”, “Pick a Field” and “Selected Fields”. Now you can start building your query. 12

Building Your Query Current Action - the default is “Select Fields to Include” At any time, you can select another table within the same report group. The fields for that table will populate and you can then pick fields from 13 that table to include in your query. Pick a Field - Scroll through and click on the fields you want displayed in the query. The table name appears beside the selected field name.

Building Your Query Selected field options. Order Asc – If you want to the query results to be sorted by one of the “Selected Fields,” highlight the field in the “Selected Fields” window and click on either “Order Asc” or “Order Desc”. As you do this, the “Order By Fields” window appears and the “Current Action” changes to “Order By”. (This causes the “Pick a Field” window to disappear.) Note that you can select several fields for this sort. Other selected field options include Move Up or Move Down (changes the 14 column order); Delete (remove one field) and Delete All (clears all fields).

Building Your Query Current Action (Filter) - One of the most useful functions in the Query Build is the “Filter Option”. When you need HRMS/Payroll data, you may only want results that meet a certain criteria (e.g., Classified staff only; Licensed staff only; or a list of all staff separated between the dates of 7/1/2014 and 8/30/2015. To reduce the amount of data returned by the types of criteria, you “FILTER” the data. Once you select the “Current Action” of “Filter On”, the “Filter Condition” 15 and “Where Clause” windows appear. The “Pick a Field” window remains since you are going to be “Filtering” on data fields.

Building Your Query First, decide the data field on which you need to filter and make sure you have the correct “table” selected. This table must appear in the “Pick a Field” window. For this example, we are going to only include “Licensed” staff. An employee is not labeled as classified, licensed, etc, but their position indicates their category. Select the Position Table in the “Select a Table” window. Now the field list for the Position table appears in the “Pick 16 a Field” window.

Building Your Query The data field on which we want to filter is the “Category Code”. We only want staff in “Licensed” positions. 17

Building Your Query Note that some data fields in the “Pick a Field” window are highlighted in yellow. This means that when you “right click” on the row, a code list with descriptions appears so you know the values to filter on. If you right click on the “Category” field the following screen displays: This lets you know that you will need to test for the value L, for Licensed staff. Click on the “Close” button to close this window. 18

Building Your Query The default “Filter Condition” is “Equal”. REMEMBER TO ALWAYS SELECT THE APPROPRIATE FILTER CONDITION BEFORE SELECTING THE DATA FIELD TO FILTER ON. In this example, “Equal” is what we want, because we want all positions where the Category Code is Equal to ‘L’. 19 (Note that this is “case sensitive”)

Building Your Query Now left click on “Category” since that is the field on which we want to filter. The following “Script Prompt” appears. Enter L (Note that this is “case sensitive”) and click OK. Then the following window appears. Click OK. (There are a few instances where you would not click OK. But for this example, click OK. 20

Building Your Query Note the text now in the “Where Clause” window to the right. “WHERE POS CATEGORY CD ‘L’. The “Where Clause” window contains the filter conditions that you have created. If you want to remove all your filter conditions, click the “Clear” button and the where clause will be removed. You will not need to click on the “Build Query” button, unless you have unchecked “Continuously Rebuild SQL Query” at the top of the screen. 21

Running Your Query Now you can “Run Your Query”. Here you can see the actual SQL query, set the maximum number of rows to return, and select Output options. Build Query – Only needed if you unchecked “Continuously Rebuild SQL Query”. New Local Report – to create a local web report Submit – Submits your query and results will be displayed. Click Submit to display results When first working on a query, you may want to limit the number of rows returned by the query to save time. You could set to 50 and the results would be limited to 50 rows of data. Max Rows set to 0 (the query default) returns all rows of data. 22

Running Your Query If the results are NOT what you want, close the result window (X in the upper right), and continue to work on the query until you get the desired results. If these are the results that you want and you do not want to save, but want to print, right click on the results and select print. If these are the results you want and you do not want to save the query but want to export to Microsoft Excel, follow these steps. Press Ctrl-A (Selects all the Data); Press Ctrl-C (Copies the data to clipboard); Open Excel and click on one of cells in a blank spreadsheet; and Press Ctrl-V (Pastes the results into Excel) If these are the results you want and you want to save the query, close the results and click on “New Local Report” 23

New Local Report 24

New Local Report 25

New Local Report Complet e all the required fields with red asterisk * Click Submit to create your local report 26

New Local Report Now your new local report displays in your list of local web reports 27

New Local Report All HRMS reporting can be accessed from the HRMS Home Page including the new local web report 28

Summary Provided a brief description of the HRMS Query Builder tool Reviewed the process to build a query based on report groups and tables Covered how to run your query and the various output methods Displayed the process for converting query into a new local web report Useful terms were provided to assist with Query Builder functionality 29

Filter Conditions EQUAL ( ) – Used when you want the selected data field equal to some value. For example: WHERE POS CATEGORY CD ‘L’. When prompted for the Script Prompt you would enter L. NOT EQUAL ( ) – Used when you want the selected data field not equal to some value. For example. If you wanted a list of all Licensed staff that do not have career status, WHERE EMP TENURE STS CD ‘C’. When prompted for the Script Prompt you would enter C. LESS THAN ( ) – Used when you want the selected data field less than some value. For example. If you wanted to list all staff last hired before 7/1/2009, WHERE EMP LASTHIRE DTE ‘2009-07-01’. When prompted for the Script Prompt you would enter 2009-07-01. LESS THAN/EQUAL TO ( ) – Used when you want the selected data field less or equal to some value. For example. If you wanted to list all staff last hired on or before 6/30/2009, WHERE EMP LASTHIRE DTE ‘200906-30’. When prompted for the Script Prompt you would 30

Filter Conditions GREATER THAN ( ) – Used when you want the selected data field greater than some value. For example. If you wanted to list all staff terminated after 6/30/2009, WHERE EMP EMPL END DTE ‘2009-06-30’. When prompted for the Script Prompt you would enter 2009-06-30. GREATER THAN /EQUAL TO ( ) – Used when you want the selected data field greater than or equal to some value. For example. If you wanted to list all staff terminated on or after 7/1/2009, WHERE EMP LASTHIRE DTE ‘2009-07-01’. When prompted for the Script Prompt you would enter 2009-07-01. LIKE – Used when you want the selected data field to “contain” a string of characters. The % sign is used to represent a “wild card” for characters. For example. If you wanted all staff whose first name starts with ‘A’, WHERE EMP FIRST NM LIKE ‘A%’. The % is a wildcard and represents any character, but the first character must be ‘A’. When prompted for the Script Prompt you would enter A%. Another example. If you wanted all staff whose first name “contains” an ‘A’, WHERE EMP FIRST NM LIKE ‘%A%’. The % is a wildcard and represents any character 31

Filter Conditions IN – Used when you want the selected data field to be equal to one of a list of values. This is one case where you WOULD NOT click on OK when prompted to add QUOTES. For example, if you wanted a list of current staff at schools 304, 308 and 312, WHERE POS SITE CD IN (‘304’,’308’,’312’). When prompted for the Script Prompt you would enter (‘304’,’308’,’312’). NOT IN - Used when you want the selected data field to NOT be equal to one of a list of values. This is one case where you WOULD NOT click on OK when prompted to add QUOTES. For example, if you wanted a list of current staff at schools EXCEPT for 304, 308 and 312, WHERE POS SITE CD NOT IN (‘304’,’308’,’312’). When prompted for the Script Prompt you would enter (‘304’,’308’,’312’). BETWEEN - Used when you want the selected data field to fall between two values. This is one case where you WOULD NOT click on OK when prompted to add QUOTES. For example, if you wanted a list of current staff terminated between 2009-07-01 and 2010-06-30, WHERE EMP EMPL END DTE BETWEEN ‘2009-07-01’ and ‘2010-0630’. When prompted for the Script Prompt you would enter 32

Filter Conditions IS NULL – Used to test if the selected data field contains nothing. This is mainly used to help clean up missing data. For example. If you wanted to list all staff that for some reason have NULL Employment status data, WHERE EMP STATUS CD IS NULL. IS NOT NULL – Used to test if the selected data field contains a value. For example. If you wanted to list all staff with a NBPTS expiration date, WHERE NBPTS EXPIRE DATE IS NOT NULL. Tips For dates, always use the format YYYY-MM-DD. Example: 2010-05-01 When using IN, NOT IN, or BETWEEN, do not ADD QUOTES. Enter in the entire list of values in quotes, separated by commas and surrounded by parentheses for the Script prompt. For example: (‘304’,’308’,’312’) See IN description. 33

HRMS Support Contact Information Email Support: [email protected] NCID users can submit a help ticket: ServiceNow Client Portal 34