The following article demonstrates some of the new and enhanced features in OfficeConnect. As these features have only recently been included in the latest release of OfficeConnect, users will need to make sure that they are using the latest version before following along.
Watch the video, or continue reading below
1. Grouping
Grouping elements allows your report to dynamically reflect changes made in the model. Using accounts as an example, when the grouping feature is applied, any changes to the account such as new additions, removals or reordering of accounts will be reflected in the report.
Updating a group
- Select update elements
- Select Refresh
For this to work, please ensure Include Update Groups in Refresh is selected (under workbook properties).
Create a new group using a consolidation.
The Apply Immediate Children as Group property will automatically bring across each child account of the parent (in the following example, Revenue) and any changes made to that account will be automatically reflected in the report.
- Highlight a row
- Right-click on a consolidation
- Select Apply Immediate Children as Group
Select Refresh (from Office Connect Ribbon
Create a new group using a custom selection.
The Apply selection as a Group property allows more customization in terms of the accounts included in the group. Additional accounts can be added to the groups created through the manage property, which is discussed in the next section.
- Highlight a row
- Ctrl-click a number of accounts
- Right-click on a selected account
- Select Apply Selection as a Group
- Select Refresh (from Office Connect Ribbon)
Manage Groups
1. Select Manage from the OfficeConnect ribbon
2. Select the Edit button
3. Make your changes to a group and select OK.
Create a group using attributes
- Highlight a row
- Navigate to attributes and select the attribute you want to group on
- Right-click the attribute and select Apply Immediate Children as a Group
- Navigate to Accounts and drag a selected account on-top of the current selection
- A pop-up will appear. Select Yes
4. Select Refresh (from OfficeConnect Ribbon)
2. Expand/Collapse
The expand and collapse feature is a useful way to analyse data for adhoc reports. It is useful to understand the detail behind a consolidation. However, it is advisable to use the Excel group/ungroup features (Data > Group) as there is data refresh lag.
Expand/Collapse an element
- Highlight the row
- Select Expand/Collapse
Updating the behaviour of Expand/Collapse
Navigate to Workbook Properties > Explore
3. Filters
There are two types of filters in OfficeConnect: Worksheet Filters and Workbook Filters. Worksheet filters are for a single sheet within a workbook, whereas Workbook filters are global filters for a whole workbook.
Accessing Worksheet Filters
Worksheet Filters can be accessed from either the OfficeConnect ribbon or the Task Pane:
Accessing Workbook Filters
Navigate to Workbook Properties > Filters
Enabling a filter
1. In the reporting pane, select the +
2. Right-click an element and Select all Descendants
3. Select OK
4. You can now filter on a specific level in the task pane
In the reporting pane, select the +
4. Troubleshooting
Sometimes you may be confused by the data shown in your report. To better understand the components that make up a cell, you can use the review section of the task pane.
1. Select a cell, or row or column
2. Navigate to the Review section of the task pane
Please note, if a cell has a specific element on it, then the worksheet filter will not apply to that cell.
5. Workbook Properties vs User Settings
Workbook Properties will take precedence over User Settings.
A simple test is to update the format and press refresh. The report will update to the format specified in Workbook Properties as opposed to the properties specified in User Settings.
Lets compare both properties below:
The output below is in Millions, demonstrating that the User Settings have been overridden by the Workbook Properties.
