OfficeConnect Excel Groups provide an easy method for keeping reports up to date with changes in element hierarchies. For example, if a new account is added to the Personnel Expenses roll-up parent and Personnel Expense children have been added as a group, the new account will appear on the report when the report’s groups are refreshed. In this article we will review:
Adding groups to Excel reports
Updating groups on Excel reports
When working with elements and groups, you have three choices when adding them to a sheet:
Apply to Selection
Apply Immediate Children as Group
Apply Selection as a Group
The options can be selected by right clicking on an element in the Elements tab of the Task Pane or from the Apply to Selection icon on the OfficeConnect ribbon.
Apply to Selection – inserts the selected member into the ROW or COLUMN selected. Steps:
Select the row or column you want the element added to.
Select the element or elements from the Elements tab you want added.
Right click the selected elements and click Apply to Selection.
Apply Immediate Children as Group – inserts the CHILDREN of the selected element PARENT. It is important to note that the parent total is NOT included in the group. If you want to include a total, best practice would be to add the parent as the total element rather than adding a total formula. When new elements are added to the hierarchy structures, they will be automatically added to the report and may cause formulas to become misaligned to report data. Steps:
Select the row or column you want the elements added to.
Select the parent element whose children you want added.
Right click the selected parent element and click Apply Immediate Children as Group.
Checking the review tab shown that the elements are part of a group.
Note - A feature of groups is that once created, they behave like a single unit. Any addition elements added to any single account in the group will be added to all of them (described in more detail below).
Apply Selection as a Group – inserts the specific elements selected as a group. This is similar to adding the individual elements separately BUT any additional elements added to any one account gets applied to all of them (described in more detail below).
Applying Additional Elements to a Group
When creating reports, it is often necessary to “nest” different elements into the same rows or columns. You can do this by dragging the elements to existing row or columns. In our example, let say you want rows 9 through 12 to apply only to Company A and rows 14 to 16 to apply to Company B. Since both account sets were created as groups, the level elements can be applied to the groups rather than the individual row elements. Steps:
Select a row or column from the group you want the element added to.
Select the element you want added from the Elements tab.
Right click the selected element and click Apply to Selection.
NOTE – Dragging a new element to an existing group will cause a warning to appear. Selecting Yes will apply the selection to the group, selecting no, will apply the selection to the individual row you dragged the element to and break up the group.
Tip – You can always see what elements are assigned to a row, column, or cell by selecting it and selecting the review tab
Groups can be easily updated to reflect any changes made in the model structures. When you first log into Adaptive through Excel, the most recent element structure is loaded, and the groups can be updated by clicking Update Groups.
If structural changes were made after initially logging into Adaptive, you will need to manually refresh the elements by clicking Update Elements.
TIP – Groups do not automatically refresh when refreshing sheet data. To be sure your report contains the most recent structural changes, click Update Groups AFTER logging into your report but BEFORE refreshing it.
The following video shows the above steps in action in OfficeConnect for Excel:
The user interface might not match what you see in this video.