Contact us Blog

Calculated fields in multi-step reports

Overview

This document describes calculated fields using formulas in multi-step reports. If you are not already familiar with multi-step reports, you can learn about them.

A calculation step provides calculations based on fields from other query steps in the report. Such a step includes calculated fields, each of which contains a formula for doing its calculation. So a calculation step can include multiple calculated fields. Note that the calculations are done when the report is run. The calculated fields in a calculation step are not part of your Salesforce database, but rather are dynamically computed by the multi-step report runner. You can use calculated fields for generating Excel or Word documents or email messages.

Here are a few examples of the kinds of calculations you can do:

You can add a calculation step anywhere in the report. Each field of such a step contains one formula — see screen shot below or watch the video. When you select "Calculations with formulas" as the data source in the Step info tab, the last three tabs (Filter terms, Range and sort, and Linkages) will be dimmed and unavailable, and the label on the second tab changes to "Calculated fields." You can then click the second tab to add the calculated fields and formulas.

When you create a calculation step, the second tab's label changes to "Calculated fields" and the remaining tabs are inaccessible (see screen shot above). In the second tab, you can add new calculated fields via the "Add field" button, or edit existing calculated fields via the "Edit" button within the field's panel. When editing a field, you can provide the name, data type and the formula for the field. When editing a field, you can click the "Insert field reference" dropdown at the top, and it will show all the query fields available from earlier steps. If you click one of them, the corresponding reference will be inserted into the formula box of the field you are editing.

A formula is simply an expression that uses as variables the field names - i.e., the names of the columns - of the non-calculated fields in earlier steps. It can use the full range of JavaScript operators, such as +, -, * and / operators for arithmetic, <, > for comparisons, and && and || for logical connectives.

For more information about JavaScript calculations in Apsona reports, see this page.

Calculations and report blocks

As described elsewhere, a multi-step report could be rendered in more than one block. So there are a few related aspects applicable to calculations.