Calculation columns with formulas—ICRW

Calculation columns are useful in reports that require information based on data computations. To create a calculation column, you select a data column and create a formula that calculates the necessary information.

For example, you might calculate employee commission by multiplying the commission rate by the total number of OE sales invoices for an employee. When you run the report, the calculation results appear in a column you might have named Commission Amount.

Subscription Interactive Custom Report Writer

Platform Services

Regional availability

All regions
User type Interactive Custom Report Writer: Business, Project Manager
Permissions

Interactive Custom Report Writer: Run, List, View, Add, Edit, Delete

Platform Services: Run, List, View, Add, Edit, Delete

Create a calculation column

Interactive Custom Report Writer (ICRW) allows you to create calculation columns in your reports. A calculation column takes information from a column in your data source and performs operations on the data. A column formula specifies what the column values represent. The results of the calculation are shown as another column in your report.

For example, suppose you have a column that shows the revenue for each product. You might create a formula that takes the information in Revenue and multiplies it by 1.10. This calculation gives you a second column that shows the revenue by product if increased by 10%. You could then create columns that show revenue if it increased by 25%, 30%, or any other increase (or decrease) you choose.

Formulas follow the mathematical order of operations. These are:

  • The calculation too performs calculations inside parentheses first.

  • Next, it performs all multiplications and divisions, 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.

Use Column editor to create a formula from any of the columns you have included in your report. You can add multiple columns and operators, depending on the calculation.

  1. Open an ICRW report, go to the Refine tab, and select Click this icon to bring up a dialog where you can create a new calculation column for a report. . The Column editor appears. The following steps demonstrate how to create a simple calculation using two columns.
  2. Enter a Title for the folder and for the column. The folder title is needed for saving the column to the Saved area.
  3. In the Totals row dropdown, select the type of aggregation to be used for the row totals. Based on the option you select, valid selections become available in the Available area.
  4. From the Reporting areas list, expand the list to show the information you want to include in the calculation, for example Contacts > Contract line > Flat/Fixed amount.
    Select the item and select the Greater than sign (>)to move the item to the Calculation area.
  5. Use the selections at the bottom of the Calculation area to create the formula.
  6. Use the function, filter, column, variable, and other buttons for the basic math operators to create the formula: addition +, subtraction -, multiplication *, division / ), parenthesis ().

    Calculations are only valid for columns containing numbers. These columns appear with an orange icon (Vertical column with horizontal hash marks at regular intervals, similar to that on a ruler or other measuring device.) in the Reporting areas list.
    You can't use two operators in a row in a formula. For example, if you enter:

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

    Intacct doesn't allow you 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 can't use two columns in a row. There must be an operator between the columns to create a valid formula.
  7. Complete your calculation and select OK.

View a formula

After you create a calculated column, you can view the formula for the calculation.

  1. Open an ICRW report, go to the Define tab, and select Select the gear-shaped Settings icon to choose from a list of options on the dropdown menu..
  2. Select Edit formula.
  3. Make your changes, then select OK to save them.

Edit or remove a calculated column

If you no longer want a calculated column in a report, you can remove it or edit the formula.

Remove a calculated column

  1. Open an ICRW report and select Edit.
  2. From the Define tab, select Select the gear-shaped Settings icon to choose from a list of options on the dropdown menu. for the column, or select the down arrow beside the column title.
  3. Select Delete.
  4. Save your report.

Change the formula in a calculated column

  1. Open an ICRW report and select Edit.
  2. From the Define tab, select the down arrow beside the column title.
  3. Select Edit formula.
  4. Modify the formula as needed.
  5. Select OK to save the calculations.
  6. Save your report.

Field description

Column formula tab

Column formula options
Item Description
Folder title Heading name for the folder that will contain this column formula.
Column title Title heading that appears on the report for the column.
Custom Headings Place a check mark here to use the folder and column titles you enter above.
Aggregation Rule Select which aggregation rule to use for the total row. For example, show totals as a sum, average, or count
Selections Select all of the columns to include, based on the selected Reporting area. Select the Greater Than sign (>) to add the selection.
Column Formula Use the buttons at the bottom of the column formula area to create the formula.

Bins tab

Select Add bin to add a new bin to this formula. See Using bins for details.

Bins formula options
Item Description
Column Shows the column on which the bin is based
Operator Select the operator to set the value range.
Value Select or enter the value used by the operator.
Protect filter Select this option to protect the filter from accidental deletion or change.

Operation definitions

Operation definitions
Operator Description

Is equal to/is in

Displays all records in which the data in the selected column matches the entry in the Value field.

Is not equal to/is not in

Displays all records in which the data in the selected column is less than the entry in the Value field.

Is less than

Displays all records in which the data in the selected column is less than the entry in the Value field.

Is greater than

Displays all records in data in the selected column is greater than the entry in the Value field.

Is less than or equal to

Displays all records in which the data in the selected column is less than or equal to the entry in the Value field.

Is greater than or equal to

Displays all records in which the data in the selected column is greater than or equal to the entry in the Value field.

Is between

Displays all records in which the data in the selected column is between two values in the Value field.

Is null

Displays all records that have a null, or empty, field.

Is not null

Displays all records that do not have a null, or empty, field.

Is in top

Displays all records in which the data in the selected column is within in the top range of the entry in the Value field. For example, if you include 10 in the value field, only the top 10 records are shown.

Is in bottom

Displays all records in which the data in the selected column falls is in the bottom range of the entry in the Value field. For example, if you include 10 in the value field, only the bottom 10 records are shown.