Create the cross-block formula

Now that we have our three report blocks, each with their own record counts, as part of one joined report, we can perform calculations using figures from the different blocks.

You may choose to use the Outline pane to remove columns that are unnecessary in order to make the report easier to read. For this example we will keep the Team Member column and remove the Employment Record # column for each block:

To calculate across the blocks in your report:

1. First, create a common grouping. Go to the Outline tab and add field to the Group Across Blocks field, such as Basis.

   

2. Next, select the down arrow beside the Leavers This Year block, and select Add Cross-Block Summary Formula.

   

3. Give the formula a Column Name of “Turnover Rate” and set the Formula Output Type to “Percent”.

4. The formula to use is as follows:

   

   If you created the report blocks in the same order shown in this worked example, you can paste the formula below. If you created the blocks in a different order, the formula will need to be adjusted.

   Note that B0 references the first block in the report, B1 the second, and B2 the third.

   Copy

   B2#RowCount / ( (B0#RowCount+B1#RowCount) / 2 )

   Alternatively, you can build the formula manually by selecting the fields individually and adding them to the report. Search for "Record Count", select the count for the block you want to add, and select Insert.

   

5. Select Apply when you have finished.

6. Select Save to save the report.

The turnover rate is calculated in a column to the right of the report.

To make the report easier to read, disable detail rows using the toggle beneath the report: