Use formulas in columns—ICRW

Related information

Related topics

• Add and edit column formulas—ICRW
• Add, edit, and delete calculation columns—ICRW
• Save and reuse columns—ICRW

Interactive Custom Report Writer (ICRW) allows you to use formulas in calculation columns to show specific results.

A simple example might be to show the number of billable hours in a project compared to unbillable hours. For this formula, you could create two columns and add a formula to each, one where billable=true and one where billable=false.

Prerequisites

Explaining the creation of formulas in detail is beyond the scope of this topic. Before creating formulas, you should have a basic understanding of formulas and how to create them. The most commonly used formulas are similar to the ones created in software, such as Microsoft™ Excel.

Enter a formula directly

1. Open an ICRW report and go to the Define tab.
2. Find the column to which you want to include or edit a formula.
3. In the column heading, select (Option) and choose Edit formula.
4. Optionally, enable Custom headings and give the column a different title.
5. Select a Totals Row option. The default is None.
6. In the Calculation section, enter the formula.
7. Select OK.

A simple example of a formula might be to filter timesheet entries that are billable for a project. In this case, you could use the formula:

FILTER("Timesheet Entry"."QTY" 
USING ("Timesheet Entry"."
BILLABLE" = 'true'))

Where you filter the count of timesheets using the timesheet entry field ("Timesheet.Entry" and
where the BILLABLE flag is set to true (BILLABLE" = 'true').

Use the Formula dialog box

The Formula dialog provides multiple sections that work together in the creation of formulas. You can use functions, operators, expressions, and variables to create formulas.

1. Open an ICRW report, select Options () in a column heading, and choose Edit formula.
2. Optionally, in the Edit Column Formula dialog, select Custom headings and give the column a new title.
3. Select a Totals Row option to use when including a Totals row. By default, None is selected. This option sets the Column totals for attribute or measure columns. For more information, see the Totals row table.
4. Include columns from the reporting area by selecting the field and selecting > to add it to the Calculation area.
5. In the Calculation section, use the function, Filter, Column, Variable, and operator buttons to create your formula.
6. Select OK.

Totals row

If you’re including an aggregation for totals on a column, select the type of information you want to display for totals. For example, to show the minimum value of the rows in a column you would set the Totals row to Min.

Operators

1. Open an ICRW report and go to the Define tab.

2. Select a column, select the Options icon () in the heading. and select Edit formula.

3. Use the Calculation area of the dialog box to add operators to the formula.

The following buttons appear at the bottom of the area:

Function

1. Open an ICRW report and go to the Define tab.
2. Select the column, select the Options icon () in the heading, and select Edit formula.
3. Use the Calculation area of the dialog box to add functions to the formula.
4. Select Function () to display the Insert Function dialog box. Expand a function folder to see the functions available in the folder.
   When you select a function, the lower portion of the dialog box shows you the function's syntax and description. Where it's appropriate, Intacct also provides an example.
5. After selecting a function, select OK to add it to the formula.
   
   If you have already included something in the formula area, that text is automatically included in the selected function. If nothing is selected, the expression for the function is inserted.

Filter

You can use filters to limit data in columns to get results that answer a specific question. At minimum, filters use a column, an operator, and a value. Advanced filters can include variables, operators, and multiple columns. A simple example might be to limit a column to the customers who bought more than 50 items in a period.

1. Open an ICRW report and go to the Define tab.
2. Select the column, select (Options) in the heading, and select Edit formula.
   You use the Calculation area of the dialog box to add filters to the formula.
3. Select Filter to open the filter editor, then select a column in the selection pane to add it to the filter.
4. Create an expression for the filter.
5. Select other column names in the selection pane to define more expressions for a more complicated filter statement. To specify the relationship between expressions, use AND/OR.
6. When your filter is complete, select OK.

More about filtering

After you create the filter, you can view it in the Calculation section of the column formula dialog. It will look something like this:
FILTER(<expression> USING <filter_expression>)

• <expression> is an expression that contains at least one measure. For example, the expression "sales 1" is allowed if "sales" is a measure. The expression "productid" is not allowed if "productid" is a scalar attribute.
• <filter_expression>does not contain a measure, does not contain other expressions, and is True or False.

Select other column names in the selection pane to include more expressions. You can build up a complex filter statement using multiple expressions.

Column (expressions)

1. Open an ICRW report and find the column expression in which to include or edit a formula.
2. Select (Options) for the column and select Edit formula.
3. Select to select a column from the columns that are already part of the current report.
   When you select a column name from the menu, the column is added to the formula.

Variables

Add variables to a column formula

1. Open an ICRW report and go to the Define tab.
2. Select the column, select the Options icon () in the heading, and select Edit formula.
3. Use the Calculation area of the dialog box to add filters to the formula.
4. Select and select the type of variable you want to add. Select either Session or Presentation.
5. Enter the name of the variable. Based on the type of variable you add, you might need to include a variable value.
6. Select OK to save your variable.