Balance Sheet Case Study Task: Reverse the Sign of Calculated Columns Using a Nested Formula

In the Balance Sheet Case Study Task: Add Calculated Columns to the Layout task, you set up a calculated column to provide the difference between the current balance and the last month end balance, and you set up a calculated column to provide the difference between the current balance and the last year ending balance. In a subsequent task, you named the columns Change This Month and Change This Year.

When you set up the calculated columns, you applied a formula for subtraction. You now need to apply the reverse sign to those calculated columns, which you accomplish by applying a multiplication formula to the columns.

For the calculated columns to return accurate results, you need to apply the reverse sign (multiplication formula) to the column balances before you subtract balances to get the changes in the balances. You can accomplish this by modifying the existing subtraction formula to include a nested function for the multiplication.

Note: You can access an existing layout that was created for the One View Financial Statements feature only by using the Manage Layout option on the Work with Statement Definition form. If you exited the Layout Editor after you completed the last set of steps and need to re-access an existing layout, see Balance Sheet Case Study Task: Accessing an Existing Layout.

To create a nested formula to reverse the sign of calculated columns, complete the following steps for the Change This Month column:

1. Select the Change This Month column in the liabilities section.

2. Click the Define Custom Formula icon in the Formula section of the Column tab.

3. Select Subtraction form the Basic Match section.

4. In the Function: Subtraction section, select the Minuend option.

5. In the Parameter: Minuend section, select the Nested Function option, and then click the Edit button next to the Nested Function option.

   The system opens a second Function window.

6. In the new Function window, select Multiplication from the Basic Math list.

7. In the Function: Multiplication section, select the Multiplier option.

8. In the Parameter: Multiplier section, select the Constant Value option.

9. Enter -1 in the Constant Value field, and then click OK.

   The system closes the second Function window and returns you to the first Function window.

   The Minuend field in the original Function window now has the value multiplication(CurrentActual.'-1').

   

10. In the Function: Subtraction section, select Subtrahend.

11. In the Parameter: Subtrahend section, select the Nested Function option, and then click the Edit button next to the Nested Function option.

    The system opens a second Function window.

12. In the new Function window, select Multiplication from the Basic Math list.

13. In the Function: Multiplication section, select the Multiplicand option.

14. In the Parameter: Multiplicand section, select the Field option, and then select Last Month End from the list.

15. In the Function : Multiplication section, select the Multiplier option.

16. In the Parameter: Multiplier section, select the Constant Value option.

17. Enter -1 in the Constant Value field, and then click OK.

    The system closes the second Function window and returns you to the first Function window.

    The Subtrahend field in the original Function window now has the value multiplication(Last Month End.'-1')

    

18. Click OK on the Function window.

19. Click the Save icon to save your layout.

20. Repeat Steps 1-18 for the Change This Year column, substituting these values:

    • In Step 1, select the Change This Year column.

    • In Step 13, select Last Year End.

Caution: If you need to exit the BI Publisher Layout Editor, do not click the Sign Out icon or you will log out of the server. Instead, click the Close icon (the X in the upper right corner of the window) to exit the window.

After you complete the steps in this task, your statement layout should look like the following image: