Account style rules

Edit online

In the account style rules tab you can configure assignment, variance, and threshold rules, and also custom validations.

Assignment rules

Assignment rules are used to populate a field with a calculated value. Common use cases include summing fields together, using a quantity and unit drop-down list to convert a captured value from one unit of measure to the primary unit of measure, prorating a value based on an annual figure, and more. You can write assignment rules in the expression builder by using SQL commands. If you add an assignment rule, you need to populate the following rule properties:
Rule name
The Rule name is a short description of what the rule does, for example, convert to kWh.
Account style field
The Account style field shows the available fields that are configured on the Fields tab. If you select a field to populate with a calculated value, you must set the field to be hidden on the Fields tab. Hiding the calculated field prevents it from being displayed in the data capture form because if a value is captured in the field, the rule would overwrite it.
Expression
In the Expression field, click Edit expression to display the expression editor. You can write a rule by using an SQL command and the available account style fields in the C_1 to C_40 columns. To validate the rule, click Evaluate. If the rule is valid, a green checkmark is displayed. To save the rule, click Apply.
Validation
If the rule is not valid, any errors are displayed and you cannot save the rule until you resolve the errors.
Description
The description is an optional area where you can describe the rule in more detail or capture any reference notes.
Adopt the following important guidelines to avoid errors in data loading or rule evaluation:
  • Enclose multiplication formulas in an SQL ROUND function to avoid generating rule results in scientific notation. IBM® ESG Suite record fields can store values up to 4 decimal places. If your rule results in a small number that is less than 4 decimal places, it might be turned into an exponential value if the data is loaded through a connector. An exponential value would cause a data loading error where the system tries to load a text value, for example, 1.23E-6, into a numeric field. Rounding the result of a calculation to 4 decimal places or fewer eliminates the risk of the data loading error.
  • Unless the fields in a calculation have been set to mandatory, include logic to handle a blank value. You can use the SQL COALESCE command, for example C_1 = COALESCE(C_2, 0) + COALESCE(C_3, 0).. In the previous example, if C_2 or C_3 are left blank, the formula defaults the fields to zero and the result is still calculated.
  • Avoid rules where a divide by zero error might occur. For example, a rule such as C_5 = C_4/C_3 might cause an error if C_3 is zero. Instead, rewrite the rule as C_5 = C_4 / NULLIF(C_3, 0). Then, if C_3 is zero, the function returns null and the resulting formula is C_4/ NULL, which returns NULL for C_5 instead of an error.

Threshold rules

Threshold rules are used to validate data against a fixed value, and they either prevent data from being loaded, or they raise an alert. Threshold rules are useful for guarding against obvious data entry issues where a fixed threshold can be easily determined, for example, fields where negative numbers are always considered an error.

If you add a threshold rule, you need to populate the following rule properties:
Rule name
The Rule name is a short description of what the rule is checking, for example, invalid floor space.
Account style field
The Account style field shows the numeric fields that are configured on the Fields tab. Text fields and drop-down lists are not supported for threshold rules.
Apply to record types
The Apply to record types setting normally has the default value of Standard selected. Select All to also apply the rule to adjustments and reversals. The setting is applicable only if Include record type is selected in the Advanced Settings section of the Details tab.
Set rule thresholds per selection
The Set rule thresholds per value enables you to specify threshold values in the rule conditions more intuitively against common measures such as week or quarter, without the need to calculate and enter a daily rate.

Threshold rules are checked against each record and based on equivalent daily rates for both the record and the rule. For example, if the Set rule thresholds per selection is Year, and the rule condition is set to raise an alert for values greater than 1000, the yearly threshold value is converted to an equivalent daily rate of 1000/365 days per year, which is approximately 2.7397 units/day.

Therefore, if the value of a record that is entered for January is 93, the value is converted to an equivalent daily rate of 93/31 days in January, which is 3 units/day and triggers the alert for the January record. The total consumption for the whole year might be less than 1000 units, but the January record still results in an alert because the equivalent daily rate is higher than the rule condition.

The Set rule thresholds per setting is relevant only for accumulative type fields where data sums over time, and is not visible for fields that are linked to instantaneous measures.

If the selected field is not linked to a measure, then the Set rule thresholds per setting has an extra Record option in the list that should be selected if the field contains instantaneous type data.

The following table summarizes the available settings for different scenarios:
Table 1. Account style fields and associated threshold rule options
Account style field Set rule thresholds per Calculation to convert rule condition threshold value to equivalent daily rate
Field with instantaneous measure Not applicable, entered records are compared directly against the rule condition values Not applicable
Field with accumulative measure Day Not applicable
Week Divide by 7
Month Multiply by 12 and divide by 365
Quarter Multiply by 4 and divide by 365
Year Divide by 365
Field with no measure Record Not applicable, treat like an instantaneous value and entered records are compared directly against the rule condition values
Day Not applicable
Week Divide by 7
Month Multiply by 12 and divide by 365
Quarter Multiply by 4 and divide by 365
Year Divide by 365
Conditions
You can configure multiple conditions for a threshold rule and each condition operates independently. For example, consider that you create the following two conditions:
  • High alert when a value is greater than 100
  • Medium alert when a value is greater than 50
If a value of 120 is loaded, it triggers both the High and Medium alert conditions. To make the rule conditions mutually exclusive, use the between operator to configure the rule:
  • High alert when a value is greater than 100
  • Medium alert when a value is between 50 and 100
In the condition expression, the Alert level value specifies the action to take if the rule condition is met. The available options and resulting actions are shown in the following table:
Table 2. Threshold rule alert severity and resulting action
Alert severity Action
Error Prevent the record from being saved
High Raise an alert with high severity
Medium Raise an alert with medium severity
Low Raise an alert with low severity
Debug Raise an alert with debug severity, which is automatically acknowledged and visible only to system administrators
Select an operator from the If value entered is list, for example, Greater than, and so on.

For Threshold value, enter the value that corresponds to the Set thresholds per setting. The equivalent daily rate is shown below the value for reference. For example, if you select Year for the Set thresholds per setting, and you set the threshold value to 1000, the equivalent daily rate is shown as 2.7397 per day.

Custom validation rules

Most validations can be handled by one of the following common use cases:
  • A mandatory flag in the field settings of an account style
  • Threshold greater than or less than rules
  • Variance rules
For validations that fall outside the previous use cases, you can use the expression builder to configure custom validation rules.

Variance rules

Variance rules are used to raise alerts when values vary from historical data by a certain percentage. For example, a variance rule can raise an alert if electricity consumption is more than 50% higher than the same period the previous year.

If you add a variance rule, you need to populate the following rule properties:
Rule name
The Rule name is a short description of what the rule is checking, for example, invalid floor space.
Account style field
The Account style field shows any numeric fields whose values are normalized to monthly data. Numeric fields that are not normalized to monthly data, text fields, and drop-down lists are not supported for threshold rules.
Apply to record types
The Apply to record types setting normally has the default value of Standard selected. Select All to also apply the rule to adjustments and reversals. The setting is applicable only if Include record type is selected in the Advanced Settings section of the Details tab.
Compare field value to selection
The Compare field value to value enables you to select the reference period average to compare against:
  • Historical average, which is the historical average across the full account history.
  • Previous [n] months average, which enables you to select between 1 and 12 months.
  • Previous period average, which is the average of the months spanning the previous period. For example, if the record entered spans October to December, the previous period months are July to September.
  • Same period previous year, which is the average of the same months the previous year. For example, if the record entered spans January to February 2025, the same period previous year months are January to February 2024.
Conditions
You can configure multiple conditions for a variance rule and each condition operates independently. For example, consider that you create the following two conditions:
  • High alert when the variance is greater than 100%
  • Medium alert when the variance is greater than 50%
If the variance is 120%, it triggers both the High and Medium alert conditions. To make the rule conditions mutually exclusive, use the between operator to configure the rule:
  • High alert when the variance is greater than 100%
  • Medium alert when the variance is between 50% and 100%
The Alert level value specifies the action to take if the rule condition is met. The available options and resulting actions are shown in the following table:
Table 3. Variance rule alert severity and resulting action
Alert severity Action
High Raise an alert with high severity
Medium Raise an alert with medium severity
Low Raise an alert with low severity
Debug Raise an alert with debug severity, which is automatically acknowledged and visible only to system administrators
Unlike threshold rules, it is not possible to set an alert severity of Error for variance rules because the likelihood of variance rules triggering false positive errors is higher.
You can select an operator from the If variance is list, for example, Greater than, and so on. For Variance value %, enter the percentage variance value above which the rule condition triggers an alert.
Note: Variance rules trigger alerts for values that are above or below the reference value. For example, if the historical average for an account is 100 units/day and a condition is created with a 20% variance value, then an alert is raised if a record is entered whose value is either higher than 120 units/day or below 80 units/day.