Adding Advanced Functions to a report is a sequence of small steps: start with the basic report, add the right groupings, then layer summary formulas on top. The walkthrough below builds a pipeline report showing percent of pipeline by stage with a running cumulative total.
- Build the base report and group by Stage
From the Reports tab, create a new report on Opportunities. Add the standard fields (Opportunity Name, Amount, Close Date, Stage). Group by Stage. Show the Amount field as a summary with the Sum aggregate. The result is a grouped report with subtotals per stage. Save the report and confirm the totals match what you expect before adding any formulas. Getting the base report right is half the work; the formulas are the easier half once the groupings and filters are correct.
- Add a percent-of-total summary formula
Open the report builder, click Add Summary Formula in the Fields panel, and define a new formula. Name it Percent of Pipeline. Set the format to Percent with 1 decimal place. Set the formula to AMOUNT:SUM / PARENTGROUPVAL(AMOUNT:SUM, GRAND_SUMMARY). Choose to display it at the Grand Summary level so it appears once per stage row. Save and run the report. Each stage now shows its percentage of total pipeline alongside the absolute amount.
- Add a running-total column via dashboard widget
Save the report, then open a dashboard. Drag the report onto the dashboard as a Lightning Table widget. In the widget options, find the Running Total toggle for the Amount column and turn it on. The dashboard now displays the cumulative pipeline as you read down the stages, which is exactly what an executive wants to see for a stage-funnel review. This is the example of how dashboard widgets expose Advanced Functions that the report itself cannot directly produce.
- Test edge cases and document the formulas
Run the report against different filter contexts to confirm the formulas behave correctly when the pipeline is empty, when only one stage has records, and when grand totals are zero. Test with non-admin profile users to confirm field-level security does not break the formula. Add a description to the report explaining what each summary formula computes and when to use it. The description appears in the Reports list view and helps future users understand whether the report is right for their question.
- PARENTGROUPVAL requires a grouping in the report. Calling it on an ungrouped report returns an error. Add a grouping or use the GRAND_SUMMARY parameter.
- Summary formulas cannot reference other summary formulas in the same report. Chain calculations into a single formula or use a custom formula field on the object.
- Cross-block formulas only work in Joined Reports. The same syntax in a standard report produces an error.
- Running totals are dashboard-widget features, not report features. Saving the report alone does not preserve the running total; the widget configuration on the dashboard does.
- SAQL functions in CRM Analytics are case-sensitive. Misspelling stddev as STDDEV or stdDev produces a function-not-found error.