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.

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:

  • Commission calculation: In a report containing an Opportunity Amount field, determine the salesman’s commission as a percentage of the amount.
  • Time-to-close calculation: In a report of Opportunities, with each row showing the creation date and the close date retrieved from the database, show the number of days between those two dates as a calculated value, so that you can measure the time to close for each Opportunity.

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.

  • If you create a calculated field whose variables are all drawn from the immediately-preceding block in the report, the field’s values will appear within that same block. For example, suppose you have created a three-step report showing you the opportunity records for each of the last three years, such as this one. You can then create a calculation step that uses the amount fields from those three steps, to calculate increases/decreases, commissions, and the like. Since the calculation uses only fields from the same block, its results will appear as part of the block.
  • In contrast, suppose you wish to compare unrelated values. For example, you might create a report with two steps, the first producing the total product sales (say, from the Product object) and the second producing the total inventory cost (from the Inventory object). You can then create a calculation step that computes the difference between total sales and inventory cost. In this situation, since the first two steps are unrelated, they will produce two separate blocks in the report. So the third calculation step will produce a separate block containing the calculation results.

Powered by BetterDocs