Report the sum of fields in child pages based on grouping and filters

Scenario

We have a parent page with a number of child pages that contain a Scaffolding report for users to enter initial data. See the table below.

Business Unit	Nature of Work	Team Size

Business Unit	Nature of Work	Team Size
Applications	Maintenance	20
Business Network	New feature development	32
Applications	Maintenance	22
CEM	New feature development	1
Business Network	New feature development	2
Content Services	Maintenance	21
Common Engineering	Maintenance	21
Applications	New feature development	6
Applications	New feature development	7

The parent page should show a sum of Team Size values across all child pages based on filters for fields within Business Unit and Nature of Work.

We are also trying to avoid displaying repeat fields in the report on the parent page. If there is more than one matching field, the report on the parent page should display the sum of all matching fields.

Result

Recipe

Apps	Reporting for Confluence Data Center, Scaffolding Forms & Templates Data Center
Level	Intermediate
Estimated time	30 minutes
Macros	Content Reporter, Expanding Reporter, Grouping Reporter, Grouping Stats, Report Column, Report Info, Report Table, Text Sort
Suppliers	Grouped Supplier, Page Supplier, Scaffold Data Supplier, Space Supplier, Stats Supplier, Text Supplier

Storage format

You can copy and paste this code into the Confluence Source Editor:

<ac:structured-macro ac:macro-id="f20138c6-ae0d-49d0-8949-9dd62ce057d8" ac:name="report-table" ac:schema-version="1">
  <ac:rich-text-body>
    <ac:structured-macro ac:macro-id="f3075452-054d-45f1-86a4-abaf25c97859" ac:name="grouping-reporter" ac:schema-version="1">
      <ac:parameter ac:name="as">Business1,NoW1</ac:parameter>
      <ac:parameter ac:name="">exr&gt;data:BusinessUnit1,exr&gt;data:NoW1</ac:parameter>
      <ac:rich-text-body>
        <p class="auto-cursor-target">
          <ac:structured-macro ac:macro-id="de082f70-90d9-4d77-be2d-e21273a50a4b" ac:name="grouping-stats" ac:schema-version="1">
            <ac:parameter ac:name="as">TeamSize1</ac:parameter>
            <ac:parameter ac:name="">exr&gt;data:TeamSize1</ac:parameter>
          </ac:structured-macro>
        </p>
        <ac:structured-macro ac:macro-id="3b244216-c1e2-4a64-b82e-1e0f10207034" ac:name="expanding-reporter" ac:schema-version="1">
          <ac:parameter ac:name="as">exr</ac:parameter>
          <ac:parameter ac:name="">data:AtulTable</ac:parameter>
          <ac:rich-text-body>
            <ac:structured-macro ac:macro-id="b164c2c1-b0d8-4ee3-9762-c28c316ddd2e" ac:name="content-reporter" ac:schema-version="1">
              <ac:parameter ac:name="types">page</ac:parameter>
              <ac:parameter ac:name="scope">Parent&gt;descendents</ac:parameter>
              <ac:rich-text-body>
                <p>
                  <br />
                </p>
              </ac:rich-text-body>
            </ac:structured-macro>
            <p class="auto-cursor-target">
              <ac:structured-macro ac:macro-id="7bc03f32-20bf-42e5-954a-3d68ccdeee47" ac:name="text-sort" ac:schema-version="1">
                <ac:parameter ac:name="">data:BusinessUnit1</ac:parameter>
              </ac:structured-macro>
            </p>
          </ac:rich-text-body>
        </ac:structured-macro>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="68652179-a0db-4712-8c66-76b92baa6255" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Business Unit</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="71c2a367-1c8c-4f7c-af84-c7753bb7ab96" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">grouped:Business1</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="8eaf6df6-0625-4005-9217-83b634a8beeb" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Nature of Work</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="4e7b2bc6-0652-4ba7-86db-904f9fce5d5b" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">grouped:NoW1</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="bd77b7d5-b9b3-4ea7-a1d7-99b2f70f5885" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="summaryFormat">$#,##0.00</ac:parameter>
      <ac:parameter ac:name="summaryType">sum</ac:parameter>
      <ac:parameter ac:name="summaryValue">grouped:TeamSize1&gt;sum</ac:parameter>
      <ac:parameter ac:name="title">Team Size</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="8b13411f-5ac1-4938-b1cc-c0ebd4ccf3b1" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="format">$#,##0.00</ac:parameter>
            <ac:parameter ac:name="">grouped:TeamSize1&gt;sum</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
  </ac:rich-text-body>
</ac:structured-macro>

Macro structure

You can recreate the example in the editor view:

Steps

Create a Report Table macro.
Within the Report Table macro, create a Grouping Reporter macro with the following parameters:
- Key: exr>data:BusinessUnit1,exr>data:NoW1
  (BusinessUnit1 and NoW1 contain the fields that can be used in the filter.)
- As: Business1,NoW1
Within the Grouping Reporter macro, create a Grouping Stats macro with the following parameters:
1. Key: exr>data:TeamSize1
2. As: TeamSize1
Under the Grouping Stats macro, create an Expanding Reporter macro with the following parameters:
- Key: data:AtulTable
- As: exr
- Match All: selected
Within the Expanding Reporter macro, create a Content Reporter macro with the following parameters:
- Spaces: @self
- Types: page
- Scope: Parent>descendants
- Match All Criteria: selected
Under the Expanding Reporter macro, create a Text Sort macro with the following parameters:
- Key: data:BusinessUnit1
- Order: ascending
- Mode: natural
- Locale: @user
Within the Report Table macro, under the Grouping Reporter macro, create a Report Column macro with Title: Business Unit.
Within the Business Unit Report Column macro, create a Report Info macro with Key: grouped:Business1.
Under the Business Unit Report Column macro, create another Report Column macro with Title: Nature of Work.
Within the Nature of Work Report Column macro, create a Report Info macro with Key: grouped:NoW1.
Under the Nature of Work Report Column macro, create another Report Column macro with the following parameters:
- Title: Team Size
- Summary Type: sum
- Summary Value: grouped:TeamSize1>sum
- Summary Format: $#,##0.00
- Columns Spanned: 1
- Rows Spanned: 1
Within the Nature of Work Report Column macro, create a Report Info macro with the following parameters:
- Key: grouped:TeamSize1>sum
- Format: $#,##0.00