Add calculated columns—CRW

Wizard Step 3. Add calculated columns

You can create calculation columns in your report by adding a formula that performs calculations on a column. Calculation columns allow you to perform operations on other columns and show the results in your report.

For example, you might calculate the commission for an employee by multiplying their commission rate by the total number of sales invoices attributed to that employee. When you run the report, the calculated results appear in a column named Commission Amount.

The formulas created using the Custom Report Writer (CRW) follow the mathematical order of operations:

  • Calculations inside parentheses are performed first.
  • All multiplications and divisions are performed next, working from left to right.
  • Lastly, all additions and subtractions are performed, working from left to right.

When you create a formula, keep the order of operations in mind.

For information on terms that may differ in your location, see Terminology across locales.

Create a formula for a calculation

Subscription Customization Services or Platform Services
Regional availability

All regions
User type Business or Admin
Permissions Custom reports: List, View, Edit, Add, Delete

You can create a formula that uses columns of information from your data source. The Formula editor creates a formula using the information in the primary data source that you selected.

  1. Select Add.
    You can add multiple columns and operators, depending on the calculation you want to show in your report. The rest of these steps describe a simple calculation using two columns of information.
  2. From the Available report columns list, select a column containing data to be included in the calculation.
    For example, if you select AP bill detail total due, an expression for the Bill total appears in the Formula column.
  3. Use the four basic math operators (addition +, subtraction -, multiplication *, and division /) and parenthesis to create a formula.
    For example, select the subtraction operator in the Operators column.
    Calculations can only be applied to columns containing numbers.
  4. From the Available report columns list, select a column containing data to be included in the calculation.
    For example, if you select AP bill detail transaction amount, an expression for the Transaction amount appears after the - in the Formula column.
  5. In the dropdown list for Calculated column type, select the desired format for the formula results.
    The result might require a decimal format, or displayed as currency.
  6. Enter a Name for the calculated column and select Save.
  7. Select Next to continue to Wizard Step 4. Select the column order.
You cannot use two operators in a row in a formula. For example, if you enter:

{!APBILLITEM.TOTALDUE!}-*{!APBILLITEM.TRX_AMOUNT!}

You will not be allowed to save the formula because the subtraction operator is followed by the multiplication operator. Remove one of the operators before saving the formula.

Likewise, you cannot use two columns in a row. There must be an operator between the columns to create a valid formula.

Wizard steps reference

The steps for creating a CRW report can vary depending on the selections that you make in the Wizard.

The typical steps for creating a report with the Wizard are as follows:

  1. Select a primary data source.
  2. Add columns to the report.
  3. Add calculated columns.
  4. Specify the column order.
  5. Add column totals.
  6. Sort columns.
  7. Filter the report.
  8. Enable grouping.
  9. Group columns.
  10. Set report period and title.
  11. Define chart output.
  12. Include runtime prompts.
  13. Save and add the report to a menu or dashboard.

Do more with formulas and calculation columns

You can view the formula for a calculation column by selecting the column name in the Calculated column for this report field. The formula for the column is displayed to the right of the field.

  1. Select the calculated column name,
  2. Select Edit or Remove.
  3. Save the report.

To create reports with highly customizable calculation columns, you might consider using the Interactive Custom Report Writer (ICRW). For more information, see Work with calculation columns in interactive reports.

The Interactive Custom Report Writer (ICRW) is available by subscription. Once your company is subscribed, you can create a variety of interactive custom reports. For information on how to acquire an ICRW subscription, contact your Sage Intacct account representative.

To enable ICRW in a console environment, you must have ICRW actively running with the correct permissions set for the console and each entity.