Configuring a unit conversion rule

A common use case for account style rules is to create a rule to convert a quantity of consumption from one unit to another. For example, you might need an account style that enables your organization to capture water data in multiple units of measure, such as liters, gallons, cubic meters, cubic feet, and convert the result to a common unit of measure such as liters.

1. Configure an account style that has the following fields:
   • A field for the quantity to be converted, which is labeled Quantity and set as a mandatory field.
   • A drop-down list field of supported units, which is labeled Unit and set as a mandatory field.
     Note: The most flexible way to configure a unit conversion drop-down list is to give each unit an integer value with 4 decimal places, for example, 1.0000, 2.0000, and then use an SQL CASE statement in the rule to multiply the quantity by a specified value if the integer value is selected in the drop-down list. Compared to storing values directly in the drop-down values, using more than four decimal places makes the conversions more accurate, and also any adjustments to conversions do not impact previously loaded data.

     It is recommended that you have a blank or Select a value entry at the top of the drop-down list so that the user must actively select the unit of measure that the data is being captured in.

   • A field to store the result of the calculation, which is normally the primary measure field of the account style. The field needs to be set to hidden because it is populated by the rule and therefore must not accept direct data input.

   It is good practice to map the quantity and unit field to C_21 and C_22. Most data types have a primary measure that is mapped to a field from C_1 to C_20. By avoiding the C_1 to C_20 fields when you configure the quantity and unit fields, the account style remains flexible for any future changes. It can also be easily copied or modified for use with another data type.
2. Write an assignment rule to convert the quantity captured to liters based on the unit that is selected in the unit list:
   1. On the Rules tab, select the field that you want to populate with the rule.
      If the selected field is not hidden, a warning message is displayed that reminds you to set the field to hidden after you have configured the rule.
   2. To write the rule, click Edit expression.
   3. When you start typing, the editor suggests SQL commands, and, if you select a command, the editor automatically generates the required command structure.
   4. Complete the highlighted areas to complete the expression, as outlined in the following example:

      C_14=ROUND(CASE
      WHEN C_22 = 1 THEN C_21 * 1
      WHEN C_22 = 2 THEN C_21 * 3.785411784
      WHEN C_22 = 3 THEN C_21 * 1000
      ELSE 0 END,4)

      CASE statement

      The CASE statement evaluates the value of C_22 and returns different results based on its value.

      WHEN Clauses

      The WHEN clauses work like a conditional expression, similar to an if-else statement in programming:

      • If C_22 equals 1 (liters), the expression returns the value of C_21 unchanged:

        WHEN C_22 = 1 THEN C_21 * 1

      • If C_22 equals 2, the expression converts C_21 from gallons to liters:

        WHEN C_22 = 2 THEN C_21 * 3.785411784

      • If C_22 equals 3, the expression converts C_21 from cubic meters to liters.

        WHEN C_22 = 3 THEN C_21 * 1000

      • If C_22 is not 1, 2, or 3, the expression returns 0, which is a default value for any other cases.

        ELSE 0

      ROUND(..., 4)

      The ROUND function rounds the result of the CASE statement to four decimal places., For example, if the result of the CASE statement is 3.785411784, the final value of C_14 is 3.7854.

3. To confirm that the statement is valid click Evaluate in the editor.
4. If no errors are returned, click Apply.
5. To save the new account style, return to the Fields tab and set the field that is populated by the calculated result to hidden.
   The warning messages disappear and the Save button is enabled.