Adding Calculations to Reports

You can use calculations to manipulate and extend the data displayed in your Financial Report Builder reports and statements. You can apply custom or predefined formulas to your lenses and measure columns, depending on your reporting requirements. For example, you might want to sum the value of all displayed columns in your report.

Tutorial: Working with Calculations in Financial Report Builder

The following calculation types are available in the Report Component Configuration:

Calculation Types
Tab	Calculation Type
Lenses Configuration	Custom. Enter your own string value to create a formula. Calculations applied in this tab appear in rows at the bottom of your report, and are typically used to display the following: Totals or subtotals Profit calculations and percentages, such as gross profit, net profit and EBITDA
Measure Columns	Custom, or Predefined. Enter your own string value to create a formula or select a predefined calculation to apply to your data. Calculations applied in this tab appear in columns at the right side of your report.

Manually Entering a Formula for a Lens

You can manually enter a string to apply a formula to your selected lens.

For example, a Trial Balance report has the Financial Transactions dataset from Financial Analytics selected as the data source.

The selected lens for this report is Transactional Trial Balance. This lens contains General Ledger Accounts and their opening and net values for periods 002 and 003, 2021. This lens is assigned the alias TB in our Lens Configuration tab.

You might want to add a row at the bottom of our report to show the sum of rows contained in our selected lens. To do this, click Add Calculation and give this section the name Total. Enter the string for our required formula: sum(TB). A row named "Total" summing all the rows in the lens is now displayed in the report.

Adding Manual or Predefined Formulas to Measure Columns

Manual Formulas

You can manually enter a string or apply a predefined formula to your measure columns.

For example, we might want to add a measure column displaying the closing balance for a selected period in our Trial Balance report.

To do this, we add a measure column with the name "Closing". We can then manually enter a formula or select a formula from the list of predefined options, by clicking Add icon. . We can manually enter the string sum(measures()) or select the predefined option Total sum(measures()).

Measure columns, like rows, support aliases in formulas.

For example, to sum the value of aliases A and B in a report, enter the following calculation in your new measure column: sum(A+B).

Predefined Formulas

When you create a measure column in Financial Report Builder, the app automatically suggests a range of predefined formulas, depending on the values you specify in your initial lens configuration and in any existing measure columns.

Formula Syntax

The formula symbols and syntax used in columns and rows in Financial Report Builder are similar to those used by Excel.

Functions and Operators in Financial Report Builder

Refer to the table below for sample basic operators and functions available for use in Financial Report Builder. For example, A*B sums the value of the columns represented by the aliases A and B. The syntax used in each example varies according to whether it is used in a lens or measure column.

Many of these operators and functions are also used in Analytics SAQL functions. For more information, see the Salesforce Help.

Financial Report Builder Functions and Operators
Operator	Measure Column	Lens	Description
`&'%'`	`A&'%'`	`SUM(A)&'%'`	Appends rows with a percentage symbol, for example when displaying Gross Profit or EBITDA as a percentage.
`*`	`AB` `A100`	`SUM(A) * SUM(B)` `SUM(A)*100`	Multiplication applied by row/column.
`/`	`A/B` `A/2`	`SUM(A)/SUM(B)` `SUM(A)/2`	Division applied by row/column.
`-`	`A-B` `A-100`	`SUM(A)-SUM(B)` `SUM(A)-100`	Subtraction applied by row/column.
`+`	`A+B` `A+100`	`SUM(A)+SUM(B)` `SUM(A)+100`	Addition applied by row/column.
`()`	`(B-A)/(A*100)`	`(SUM(B)-(SUM(A))/(SUM(A)*100`)	Parentheses for grouping operations.
`abs()`	`abs(measures())`		Returns the absolute number of n as a numeric value, for example, to remove the minus sign from a negative number, making it positive.
`sum()`	`sum(measures())`		Returns the value of a numeric field
`average()`	`average(measures())`		Returns the average of the values of a measure field.
`max()`	`max(measures())`		Returns the maximum value of a measure field.
`median()`	`median(measures())`		Returns the median value of a measure field.