QMetrix Workday Adaptive Planning (Adaptive Insights) User Group

OfficeConnect Enhancements in 2021

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

  1. Select update elements
  2. Select Refresh

OfficeConnect | qmx_admin

For this to work, please ensure Include Update Groups in Refresh is selected (under workbook properties).

OfficeConnect | qmx_admin

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.

  1. Highlight a row
  2. Right-click on a consolidation
  3. Select Apply Immediate Children as Group

Select Refresh (from Office Connect Ribbon

OfficeConnect | qmx_admin

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.

  1. Highlight a row
  2. Ctrl-click a number of accounts
  3. Right-click on a selected account
  4. Select Apply Selection as a Group
  5. Select Refresh (from Office Connect Ribbon)

OfficeConnect | qmx_admin

Manage Groups

1. Select Manage from the OfficeConnect ribbon

OfficeConnect | qmx_admin

2. Select the Edit button

OfficeConnect | qmx_admin

3. Make your changes to a group and select OK.

OfficeConnect | qmx_admin

Create a group using attributes

      1. Highlight a row
      2. Navigate to attributes and select the attribute you want to group on
      3. Right-click the attribute and select Apply Immediate Children as a Group
      4. Navigate to Accounts and drag a selected account on-top of the current selection
      5. A pop-up will appear. Select Yes

OfficeConnect | qmx_admin

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

  1. Highlight the row
  2. Select Expand/Collapse

OfficeConnect | qmx_admin

Updating the behaviour of Expand/Collapse

Navigate to Workbook Properties > Explore

OfficeConnect | qmx_admin

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:

OfficeConnect | qmx_admin

Accessing Workbook Filters

Navigate to Workbook Properties > Filters

OfficeConnect | qmx_admin

Enabling a filter

1. In the reporting pane, select the +

OfficeConnect | qmx_admin

2. Right-click an element and Select all Descendants

3. Select OK

OfficeConnect | qmx_admin

4. You can now filter on a specific level in the task pane

OfficeConnect | qmx_admin

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

OfficeConnect | qmx_admin

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:

OfficeConnect | qmx_admin

The output below is in Millions, demonstrating that the User Settings have been overridden by the Workbook Properties.

OfficeConnect | qmx_admin

Scroll to Top

This website uses cookies to ensure you get the best experience on our website. More Information

Skip to content