Using the Expression Builder

When you create an expression, it must be formatted correctly so Sage HRMS can understand it. You can type the expression in manually (if you know exactly what the field, relationship, and value should be)—or you can use the Expression Builder to help you build the expression. As you work with Sage HRMS, whenever you see the Expression Builder button, click it to open the Expression Builder window so you can create the expression.

To build an expression

1. To create an expression telling Sage HRMS which employees to select, click the Expression Builder button.

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.