- Creating a report formula
- Using a formula with report filtering
- Using your report formula with sorting and grouping
You can use a report formula to perform additional calculations on your reports. This can help you to analyze and compare data, or work as a temporary solution if you do not have access to add a formula field to a table.
Note: Report formulas are designed for temporary use. Formulas with complex logic or formulas that act on many fields, records, or tables will calculate more quickly when created in a field on the table.
Creating a report formula
-
In the Report Builder, under Report formulas, select Add a report formula.
For table reports, this section appears after you select Custom columns. For charts, this section is under Options.
-
Select the formula type from the drop-down.
-
Write the formula. Read about creating formulas.
-
If you want to create an additional report formula, select Add another report formula. You can create up to three report formulas. Note: You cannot reference one report formula in another.
-
Save the report.
Using a formula with report filtering
You can use a report formula to set special filtering, such as comparing values in one field to values in another field.
Example:
You can create a formula to select a check box when the word “infrastructure” appears in the Task Name, Rollup Category, or Description field. Select Formula - Checkbox from the dropdown and use a contains formula:
Contains([Task Name], "infrastructure")
or
Contains([Rollup Category], "infrastructure") or
Contains([Description], "infrastructure")
Or, to select a check box when the current user’s name appears in the Owner, Author, or Reviewer field, use:
[Owner]=User() or
[Author]=User() or
[Reviewer]=User()
To use your report formula with filtering, select Filter in the Initial filters section, and select or search for the formula name from the drop-down. Then, add your criteria.
Note: Report formulas do not work with dynamic filters.
Tip: For the Formula - Checkbox field, when you write the formula you do not need to use “true” and “false.” If the conditions are met, the result is true and check box is selected. If not, the check box is cleared.
Using a report formula with sorting and grouping
To use your report formula with sorting and grouping:
- In the Sorting & Grouping section, select Sort or group on other fields.
- Choose a sorting and/or grouping option, such as sort and group from low to high by.
- Select or search for the formula name from the drop-down list.