Adding Calculations to Reports

You can use calculations to manipulate and extend the data displayed in your 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.

You can apply calculations to your data in the following locations in Report Builder:

• The Lens Configuration tab.
• The Measure Columns tab, using the Formula Builder.

Lens Configuration Tab

You can add custom formulas to perform calculations on a lens. To do this, perform the following steps:

1. From your report, click | Lens Configuration.
2. Click | Add Calculation. 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:
   1. Totals or subtotals
   2. Profit calculations and percentages, such as gross profit, net profit and EBITDA.

Formula Builder

The options displayed in the Formula Builder window enable you to apply predefined formulas to your measure columns or create your own formulas.

To apply a predefined formula or create your own, perform the following steps:

1. Click the row in the Measure Calculation Columns table that you want to perform a calculation on.
2. In the Formula Builder window, specify the calculation you want to apply using the following options:

Calculation Type

The following predefined types of calculation are available:

• Total. Displays the total value of your selected measure.
• Measure. Enables you to create a formula based on the measure columns displayed in the Measure Columns tab.
• Rolling Total. Displays a rolling total for your selected measure.
• Percentage. Displays the value of your selected measure as a percentage.

Measure

Select the measure you want to perform the predefined calculation type on.

Value

Select a single value in your selected measure.

Note:

This option is disabled if you are not pivoting the data in your report or have activated soft dates and selected a period format that aggregates more than one time period, such as Current Year (Periods).

An example of how Calculation Type, Measure, and Value operate together is shown below.

In this example, if you select your Calculation Type as Measure, and your Measure as Sum of Dual Value, you would then be presented with the option of selecting Merlin Technologies Ltd., Merlin Technologies Pty., or Merlin Technologies, Inc., under Value.

3. Click Apply to apply your selected formula. Alternatively, you can write your own formula in the Measure Calculation field.
4. Click Save.

Manually Entering Formulas

You can manually enter a string to apply a formula in the Lens Configuration tab or Measure Columns tab.

Example One

A Trial Balance report uses the Financial Transactions dataset from Financial Analytics as its 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 your 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 labelled "Total" summing all the rows in the lens is now displayed in the report.

Example Two

You might want to add a measure column displaying the closing balance for a selected period in your Trial Balance report.

To do this, you add a measure column with the name "Closing". You can then manually enter a formula or select a formula from the list of predefined options, by right-clicking your required row to open the Formula Builder. You can manually enter the string sum(measures()) in the Measure Calculation field.

Note:

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).

Formula Syntax

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

Refer to the table below for sample basic operators and functions available for use in 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.

Report Builder Functions and Operators

Operator	Measure Column	Lens	Description
^		SUM(A)^2	Indicates an exponent used to raise the power of a number.
&'%'	A&'%'	SUM(A)&'%'	Appends rows with a percentage symbol, for example when displaying Gross Profit or EBITDA as a percentage.
*	A*B A*100	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.
count()		count(measures())	Returns the sum of all non-blank values in a row.
countblank()		countblank(measures())	Returns the sum of all blank values in a row.
max()	max(measures())		Returns the maximum value of a measure field.
median()	median(measures())		Returns the median value of a measure field.
min()	min(measures())		Returns the minimum value of a measure field.
mode()	mode(measures())		Returns the modal value of a measure field.