Defining your own selection criteria

Use the Criteria Table to select a previously-defined expression associated with a code in the Criteria Code Table.

Note: You can also click Add New Code to open the Expression Builder to define your own criteria on an as-needed basis. However, you can instead select the Expression Builder directly from the Custom Criteria list.

1. In the Custom Criteria list, select Criteria Table.

2. The Criteria Table page lists the criteria codes from your Criteria Code Table. Highlight the code you want to use.

   Note: If you did not define any criteria codes, only the Add New Code selection appears in the Criteria Table window.

3. Click OK.

   The expression appears for Custom Criteria on the process page.

Use the Criteria Builder to create selection criteria by selecting a field from the Personnel (HRPERSNL) database, a relational operator, and a value for comparison. The Criteria Builder automatically formats the expression for you.

1. In the Custom Criteria list, select Criteria Builder.

2. On the Criteria Builder page, build your expression:

   1. Make a selection in the Field list.
   2. In the Relationship list, select a relationship expression.
   3. For Value, type the value that you want to select for that field.

   Example: To limit the selection to employees whose Job Title is Production Manager, select Job Title for Field, select Equal To for Relationship, and type Production Managers for Value.

3. If you need to add another criterion to the expression:

   1. Click the And button. The first part of the expression appears in the window.
   2. Repeat step 2.

   Example: To also limit the selection to Production Managers who started their job on or after 01/01/17, select Job Start Date for Field, select Greater Than or Equal To for Relationship, and type 01/01/17 for Value.

   Note: If you want to delete all the criteria you defined and start over, click Reset. If you want to delete one criterion at a time (from the last to the first), click Undo.

4. When you finish defining the expression, click Done.

   The page now shows the complete expression.

5. To save the expression to your Criteria Table:

   1. Click the Save To Criteria Table button.
   2. In the Criteria Table window, type a description for your new expression.
   3. Click OK.

6. To save and select the expression, click OK to return to the process page.

   The expression then appears in the Custom Criteria list.

Use the Expression Builder to create selection criteria by selecting a field from one of the databases, a relational operator, and a value for comparison.

Tip: To help avoid data processing errors, wait until you are comfortable using expressions and are knowledgeable of the Sage HRMS databases before creating these expressions. For more information about database files and fields, see Data Dictionary.

1. In the Custom Criteria list, select Expression Builder.

2. In the Expression Builder window, build your expression:

   1. In the From table list, select the database table to use.

   2. In the Fields list (which lists the fields from the database table you selected in step 2a), double-click the field to use.

      The database table alias and field appear in the Expression panel above the buttons.

   3. Place your cursor after the data field you just added and then type a space.

   4. Click the Function Category drop-down and select the Function Category that you want to use in the expression.

      When you change the Function Category drop-down, the Function drop-down also updates and displays a list of functions in the selected category.

      Function Categories

      Function Category Descriptions

       

      Function Category	Description
      Logical	Use to evaluate conditions and return results based on whether the conditions are True or False.
      Date	Use with date values to extract part of the date, calculate durations such as tenure and format dates.
      Math	Math functions perform numeric calculations.
      String	Manipulate text values by removing spaces, change formatting...

   5. Double-click the Function to add it to the Expression panel.

   6. Place your cursor at the end of the expression, and then type a space and the comparison value.

      Note: Different types of values must be entered in different formats in SQL:

      • Character values must be enclosed with single quotation marks: 'value'
      • Date values must be enclosed with single quotation marks and in the format: YYYY-MM-DD
      • Logical values should be enter typically as: 1 for TRUE and 0 for FALSE.
      • Numeric values are entered directly: 235.12

   Example: To limit the selection to active employees, select PE - Employee Personnel from the From Table drop-down, double click on the Employee Status Code P_ACTIVE from the Fields list, change the Function Category field to Logical and then double click the equal sign = in the Function list, and to finish your expression, type a space and then enter 'A'. Your expression would look like this:
   PE.P_ACTIVE = 'A'

3. If you need to add another criterion to the expression:

   1. Type a space at the end of the expression in the Expression panel.
   2. Select Logical from the Function Category drop-down.
   3. In the Function list, select the operator, such as AND.

      The operator is added to the Expression area.

   4. Repeat step 2 to add the second criteria.

   Example: To also limit the selection to employees in the Sales department (organizational level 1), select PE - Employee Personnel from the From Table drop-down, double click on the Division Code P_LEVEL1 from the Fields list, then double click the equal sign = in the Function list and to finish your expression, type a space and then 'SALES'. Your expression would now look like this:
        PE.P_ACTIVE = 'A' AND PE.P_LEVEL1 = 'SALES'

4. When you finish entering the expression, click OK.

   Tip: You can click Verify to make sure you used the correct structure for your expression. For example, there is an equal sign, it will confirm that there is a value after the equal sign. When you save your expression and return to the report or process to use the expression, Sage HRMS checks your expression again. If it is an illogical expression, a message displays and indicates what is wrong; return to the Expression Builder and fix the error.

   The expression then appears in the Custom Criteria list.