Custom summary formulas allow you to calculate values based on the numeric fields available in the report type. This means that you do not have to create custom formula fields for calculated results if they are only relevant in reports.
Formulas must be 3,900 characters or less. Up to five formulas can be created per report. Fields available for custom summary formulas are Number, Percent, and Currency. To add a new formula to a summary or matrix report, navigate to the Fields pane, where at the top, you will see the formulas folder icon. By double-clicking on the Add Formula option, you can define it and then click on OK. After you have defined a new formula on the report, it automatically gets added to the preview pane as a column for summary reports and as a summary field for matrix reports.
The following screenshot shows you the formula called Avg Expected Revenue, the top-left section of the Fields pane, and how it automatically appears in the preview pane as a column (on the far right) for the example summary report.
To define a formula field, follow these steps:
- Click on Add Formula in the Fields pane.
- Enter a column name for the formula. This will be displayed within the report.
- Optionally, enter a description.
- Select the data type from the Format picklist.
- Select the number of decimal places from the Decimal Places picklist.
- Set the option denoting where this formula is to be displayed.
- The formula calculation will be displayed in the report at the level that is selected.
- Build the formula by selecting one of the fields listed in the Summary Fields picklist, and then select the summary type:
Summary type
Description
SumThe sum of data in a field or grouping of fields
Largest ValueThe largest value of data in a field or grouping of fields
Smallest ValueThe smallest value of data in a field or grouping of fields
AverageThe average of data in a field or grouping of fields
- Click on Operators to add operators to the formula. Select the function category, choose the function you want to use in your formula, and click on Insert.
- Click on Check Syntax to check whether the formula contains any errors and then click on OK.
Tip
Hiding details when building new reports
Often when building new reports, you will not necessarily know just how many records are actually going to be returned. This can be the reason for the report in the first place. You might also be experimenting with the report format to see what data is being returned. In these cases, you should set the Hide Details option to prevent the detailed data being returned and show just the "skeleton" of the report—this shows you the number of rows that will be returned. Limiting rows on a tabular report allows you to use it as a source report for the dashboard table and chart components. However, if you change the report format, the Row Limit setting is automatically removed.