Create element groups to dynamically update your reports with latest changes to your reporting structure.
Create an element group by:
- Applying elements as a group from the Elements pane to the report. See Create and Apply Element Groups from the Elements Pane.
- Grouping elements from rows or columns of an existing report. See Create Element Groups from Report Rows or Columns.
Before You Begin
Create and Apply Element Groups from the Elements Pane
When you apply an element group to a row, each element in the group is a new row in the report. If you apply to a column, each element in the group is a new column.
Apply Direct Children as a Group
To create and apply all direct children of a parent element as a group:
- Select the row or column where you want to apply the group.
- In the task pane, select a parent element such as Assets.
- Right-click the parent element and select Apply Immediate Children as a Group.
All direct children of the parent element are applied to the grid. These elements now have a group status.
Apply Selection as a Group
To create and apply selected siblings of the same parent element as a group:
- Select the row or column where you want to apply the group.
- In the task pane, select siblings. Use the Shift key or Ctrl key to make contiguous and non-contiguous selections. For example, select Current Assets, Fixed Assets, and Other Assets and skip Long Term Assets.
- Right-click the selection and select Apply Selection as a Group.
The selected siblings only of the Assets element are applied as a group to the grid.
Apply Secondary Element to a Group
To apply a secondary element that is not related to the primary elements already in a group:
- Select a row or column of the existing group. For example, if you have levels as a group on the columns, you can select Sales -North.
- In the task pane, select the secondary element to apply to the existing group. For example, select FY 2020 to apply this time element across all the levels.
- In the task pane, right-click the secondary element and select Apply to Selection. A confirmation message appears.
- Select Yes to apply the secondary element across all elements of the existing group. In this case, apply to all levels in the existing group.
Create Element Groups From Report Rows or Columns
You can also create groups of elements that are already applied to report rows or columns.
- Select a single or multiple contiguous rows or columns with child elements of the same parent. For example, select Subsidiary Diff and Accounts Payable to group these two elements.
- Right-click and select OfficeConnect > Group.
You can group elements that are part of an expansion. See Expand and Collapse Rows or Columns in the topic Explore Reports Using Ad Hoc Analysis. The expansion indicator in the Review pane is replaced with the group indicator for the element.
Update Element Groups
You can add, remove, or reorder elements in your model that are part of a group. Make these changes in Modeling and use OfficeConnect Update Groups option to reflect the changes in reports. You can also refresh a report to update all element groups with the latest element structure. To exclude element groups from Refresh, edit your workbook property for Groups. See Workbook Properties.
- Refreshes only element groups for the entire workbook. It does not refresh the other reporting data.
- Adds, removes, or reorders rows or columns based on the latest element structure reflected in the Elements pane. A new row or column copies the formatting from the last row or column in the group. Borders and merged cells aren't copied.
- Does not add back elements that you excluded from the group before. For example, if you excluded Long Term Assets from the Assets group, updating the group does not add it back.
To refresh the report rows and columns associated only with element groups:
- Review the task pane to verify you're seeing the latest element structure. Or, from the Connection group, select Update Elements to get the latest changes for your model.
- From Groups, select Update Groups.
- Optional. To update the whole report with the latest data, select Refresh.
Considerations for Reordering Rows or Columns
You can reorder your rows or columns containing element groups by inserting new rows or columns. The following exceptions apply:
- You can’t insert a column because Excel throws an exception indicating that a column containing data or formatting will go out of the bounds of the worksheet.
- You can’t insert a row because Excel throws an exception indicating that a row containing data or formatting will go out of the bounds of the worksheet.
- The row or column ranges contain one or more merged cells.
Manage Element Groups
Use Manage Groups to view and navigate to existing element groups in a report. You can also edit a group or undo the group association.
Groups list first by worksheet (left to right) and then by location (rows followed by columns). You can see the following details about the groups:
- Name of group (from parent) and the number of included children of total.
- Worksheet name where the group appears.
- Last date and time Update Groups ran for this group. Update Groups updates this date even if there is no change to the group.
- Last date and time the group structure changed. The group had members added, deleted, or reordered.
For a new group, the Last Updated and Last Changed dates show the date and time when you created the group.
Best Practice: Over time, changes in your model can cause elements in a group to become unknown to you. Manage Groups reflects latest element groups based on when Update Groups last ran. Before reviewing or editing any groups, run Update Elements and then Update Groups.
To manage an element group:
- From Groups, select Manage Groups.
- Manage your element groups:
- To break an element group, select the group, right-click, and then select Break Group. The groups members remain on the report but the group association removes.
- To navigate to an element group in the report, double-click the group. The element group gets highlighted in the report.
- To edit an element group, click the pencil button next to it. You can select and add members excluded from the group. You can also remove members from the group.
When you edit an element group, the changes reflect in the Manage Groups dialog and in the report. The Last Updated date changes even if you made no updates to the group members. The Last Changed date changes only if you update the group members in some way.
Identify an Element Group from the Review Tab
To identify an element group from the Review tab:
- Select a single a row or column.
- Select Review.
- If an element group applies to the row or column, you see the group icon next to it.
Breaking an Element Group: Best Practices
The following actions can cause an element group to break. The elements continue to appear in the report as individuals. Their group status removes and they no longer update dynamically.
- Inserting rows or columns that separate the grouped elements.
- Adding a secondary element that does not apply to all primary elements in the group.
- Replacing a single element from an element group.
- Cutting and pasting or deleting elements from a group.