Reporting the sum of fields in children pages based on grouping and filters

Scenario

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

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 across all children 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 Server & Data Center, Scaffolding Forms & Templates
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:

CODE

<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.
Inside the Report Table macro, create a Grouping Reporter macro.
For the Key parameter, type in "exr>data:BusinessUnit1,exr>data:NoW1".
(BusinessUnit1 and NoW1 contain the fields that can be used in the filter.)
For the As parameter, type in "Business1,NoW1".
Inside the Grouping Reporter macro, create a Grouping Stats macro.
For the Key parameter, type in "exr>data:TeamSize1".
For the As parameter, type in "TeamSize1".
Below the Grouping Stats macro, create an Expanding Reporter macro.
For the Key parameter, type in "data:AtulTable".
For the As parameter, type in "exr" and check Match All.
Inside the Expanding Reporter macro, create a Content Reporter macro.
For the Spaces parameter, enter "@self".
For the Types parameter, set to "page".
For the Scope parameter, type in "Parent>descendents" and check Match All Criteria.
Below the Expanding Reporter macro, create a Text Sort macro.
For the Key parameter, type in "data:BusinessUnit1"
Set the Order parameter to "ascending".
Set the Mode parameter to "natural".
For the Locale parameter, enter "@user".
Inside the Report Table macro and below the Grouping Reporter macro, create a Report Column macro.
Set the Title to "Business Unit".
Inside the Report Column macro, create a Report Info macro.
For the Key parameter, type in "grouped:Business1".
Below the Report Column macro, create another Report Column macro.
Set the Title to "Nature of Work".
Inside the Report Column macro, create a Report Info macro.
For the Key parameter, type in "grouped:NoW1".
Below that Report Column macro, create another Report Column macro.
Set the Title to "Team Size".
Set the Summary Type to "sum"
In Summary Value, input "grouped:TeamSize1>sum".
In Summary Format input "$#,##0.00".
Both Columns Spanned and Rows Spanned should be set to "1".
Inside the Report Column macro, create a Report Info macro.
Set the Key parameter to "grouped:TeamSize1>sum".
Set the Format parameter to "$#,##0.00".