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
Ingredients
Apps | |
|---|
| Platform | Server |
|---|
| Level | |
|---|
| Estimated time | |
|---|
Macros | |
|---|
Suppliers | |
|---|
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>data:BusinessUnit1,exr>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>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>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>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>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".
