Reporting the sum of fields in children pages based on 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 UnitNature of WorkTeam Size
ApplicationsMaintenance20
Business NetworkNew feature development32
ApplicationsMaintenance22
CEMNew feature development1
Business NetworkNew feature development2
Content ServicesMaintenance21
Common EngineeringMaintenance21
ApplicationsNew feature development6
ApplicationsNew feature development7

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

Storage format

Macro structure

You can recreate the example in the editor view:

Steps

  1. Create a Report Table macro.

  2. 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".

  3. Inside the Grouping Reporter macro, create a Grouping Stats macro.
    For the Key parameter, type in "exr>data:TeamSize1".
  4. For the As parameter, type in "TeamSize1".

  5. 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.

  6. 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>descendentsand check Match All Criteria.

  7. 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".

  8. Inside the Report Table macro and below the Grouping Reporter macro, create a Report Column macro.
    Set the Title to "Business Unit".

  9. Inside the Report Column macro, create a Report Info macro.
    For the Key parameter, type in "grouped:Business1".

  10. Below the Report Column macro, create another Report Column macro.
    Set the Title to "Nature of Work".

  11. Inside the Report Column macro, create a Report Info macro.
    For the Key parameter, type in "grouped:NoW1".

  12. 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".

  13. 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".