Deciding whether to use Web Reports or OfficeConnect to build your reports in Workday Adaptive Planning / Adaptive Insights can be tricky. Both are great reporting tools – but how can you determine which is the right tool for you?
Unfortunately or fortunately, there is no right answer, as it very much comes down to an analysis of your reporting needs and personal preference.
Are you looking for a platform to quickly pull together data for quick analysis with minimal formatting requirements? If yes, web reporting may be your answer – get tips on that here.
Or, are your requirements for a more customised approach, with complex calculations and specific formatting requirements? If your answer is yes, you should consider using OfficeConnect.
OfficeConnect is widely appreciated for its flexibility in reporting. As it is an add-on for Excel, it allows users to build a report utilising OfficeConnect properties and features, as well as make use of familiar Excel functionality.
If you are interested in building reports in OfficeConnect in Workday Adaptive Planning, the following tips will help set you up to deliver an insightful and professional report and to make the most out of the technology.
Watch the video or read our explanation below.
Dynamically update rows and columns
This feature was released in the 2020R1 Product Release. Previously, when a new child account was added to the model, all reports would need to be manually adjusted and the new child accounts dragged into the report. This process can be quite time consuming and can often lead to reports missing vital information.
The Dynamically Update Rows and Columns feature makes this process seamless, by initially setting the reports up to allow for these changes from the very start.
Follow the steps below to set up your OfficeConnect reports to allow for this feature. There are two ways to you can do this: Apply Immediate Children as Group or Update Selection as Group.
- Highlight a row in the worksheet. Right-click on the parent account and from the drop-down options, select Apply Immediate Children as Group.
In the above example, this will add all the child accounts of 4000 Revenue into the worksheet. - Alternatively, you can highlight the child accounts of 4000 Revenue and select Apply Selection as Group. Use this option when you want to intentionally exclude an existing child account from the report.
Once a new child account has been added to the model, there are a few necessary steps to bring this account into the report.
- From the OfficeConnect toolbar, select Update Elements. This will update the account elements in the reporting pane.
- From the toolbar, select the Elements property, and Update Groups. This will update the account groups added to the report in the initial set up step.
Important notes to consider
- This feature only applies to OfficeConnect reports at this stage.
- This feature only applies to new OfficeConnect reports. Any existing reports that you would like to reflect this behavior will need to have the accounts re-added into the report.
Using relative dates
Relative dates can be used to enhance Workday Adaptive Planning / Adaptive Insights OfficeConnect reports by eliminating the need to manually update reports month-by-month.
When enabled, the date is relative to the reporting date which by default is the current date. However, the reporting date can also be changed through Workbook Properties.
Labels
Labels are a great way to add context to reports. Commonly, labels are used to display time periods and versions. However, they can also be used to display filters or context elements.
The image below shows the label for a filtered element. In the below scenario, location is a custom dimension.
A common issue that users run into when adding labels to an OfficeConnect report is that they can interfere with the report design. For example, labels cannot be added to merged cells.
Instead of compromising the readability of the report, consider adding the label to a hidden cell. This hidden cell can then be referenced in the report design.
Linked Cells
The Linked Cells feature highlights all the Workday Adaptive Planning / Adaptive Insights elements in the report.
This feature is extremely useful in distinguishing between accounts and metrics that are driven from Workday Adaptive Planning and calculations made using the Excel functions.
The default colours of these highlighted elements can be changed in User Settings.
It is best practice to turn off Linked Cells before making any changes to the report.
Refresh worksheets or workbooks
Optionally, decide whether to refresh the current worksheet, a selected number of worksheets or the entire workbook.
By default, when selecting the green play icon to refresh the report, only the current worksheet will be refreshed. However, the drop-down options let you control which sheets to refresh.
Suppression
Enabling the Hide Zero and Blanks property will hide those accounts with no data.
The image below is an example of when this property is disabled. Notice how accounts 4300 Maintenance Revenue and Intercompany revenue have no data.
Compare this to the below image where the property has been enabled. Notice that these accounts are no longer displayed.
Copy/Paste
When it comes to using the Copy and Paste functions in OfficeConnect, there are two options.
1. Standard copy and paste
Whilst the standard copy and paste functions will still work, they do not copy across the Adaptive elements. This will mean that all the OfficeConnect functions are disabled.
See the image below that indicates the standard copy and paste functions were applied.
2. Workday Adaptive Planning copy and paste
Workday Adaptive Planning also provides a copy and paste function. This will copy across the Adaptive elements and therefore allows you to interact with the OfficeConnect functions on the toolbar.
To copy and paste an entire worksheet using the Adaptive function, right-click on the sheet and select Move or Copy Report.
This feature can also be used to copy rows and columns within a worksheet.
To copy a column, right-click the column. From the OfficeConnect property, select Copy Elements.
Filters
Filters can be applied in OfficeConnect to either the entire Workbook, or the Worksheet.
Workbook filters
- To apply a Workbook filter, Navigate to Workbook Properties. Select the Filters tab and use the plus sign to choose a filter.
- In the below image, locations Australia and United States have been set as possible filters, with the Australia filter currently enabled. This allows the user to optionally filter on both Australia and United States at the same time.
- When a workbook filter is enabled, it will filter every worksheet in the report. This means that each worksheet in the example above will be filtered for Australia.
Worksheet filters
- Worksheet filters on the other hand will only filter the specific worksheet they are applied too.
- These can be set from the reporting pane on the left of the screen, or from the Worksheet filters property on the toolbar.
Repeating Reports
Repeating reports allows you to duplicate a worksheet multiple times based on a repeating element.
- From the toolbar, select the Repeating Reports. Select the element type and use the check box to select each element you expect to have its own sheet.
- In the below example, Location has been used to repeat.
Considerations
- Users should consider repeating reports as separate reports to maintain. When a change needs to be made to the source report, it will need to be made for each repeater as well.
Looking for more Workday Adaptive Planning / Adaptive Insights best practice tips?
