Salesforce Joined Report: Add Cross-Block Summary Formula

We shared how to add the same report type in Joined Report (in Classic) back in 2013. Despite the hate and love of joined reports, there is a feature that is not utilized by many users in the joined report, called Cross-Block Summary Formula.

Cross-Block Summary Formula is pretty similar to Summary Formula, with only different fields to be calculated from across blacks.

Not going to talk much about what is a summary formula is, but here is the sample on using Cross-Block Summary Formula. In this blog, I am going to calculate the percentage of Closed opportunities compare to All opportunities by the Opportunity Owner.

Add from Cross-Block Summary Formula, the field will be added to the selected block

Percentage number of closed opportunities compare to all.

formula B0#RowCount/B1#RowCount

My first block is filtered by closed opportunity, and the second block is all opportunity.

Percentage Amount of closed opportunities compare to all.

formula B0#AMOUNT:SUM/B1#AMOUNT:SUM

To move the fields to a new block on their own, add a new block and add a name field. To avoid duplication, you can remove the fields from created earlier.

Here is the complete report

Here is the result

Here are a few notes related to cross-block custom summary formulas:

• You can add a cross-block formula to any block in the report.
• In Lightning Experience, cross-block formulas are automatically added to the report when you create them. In Salesforce Classic, cross-block formulas aren't automatically added to the report when you create them. To add a cross-block formula in Salesforce Classic, drag it to a block.
• When you add cross-block formulas to a block, they appear to the right of standard ones in the order in which you add them to the block.
• The results of cross-block formulas are affected by the filter options applied to the blocks in the report. As a result, a cross-block formula can yield different results when you change filter options.
• Each joined report can have up to 10 cross-block custom summary formulas.
• Deleting a block that’s used in a cross-block formula also deletes the formula from both the Fields pane and any remaining blocks containing it.
• Each cross block formula must have a unique name. However, standard and cross-block custom summary formulas can have the same name.
• Cross-block formula names can't include brackets (“[“ or “]”).

Reference:

• Add a Summary Formula to a Joined Report
• Summary Formulas with Joined Reports