Steps: Explore Reports Using Ad Hoc Analysis

Explore Cell
to view the cell details in a new web page and drill into transactions. See
Explore Cell Content
.
Example: Explore a cell to view the values from each contributing quarter that sums up the FY2022 time rollup.
Expand
and
Collapse
to display or hide row or column details in context for ad hoc analysis. See
Expand and Collapse Rows or Columns
.
Example: Expand an expense account to see which child account is causing a variance noted at the parent level. Collapse organization levels to see the rollup at the top or parent level of the hierarchy.

Before You Begin

New to ad hoc analysis? Review Example: Expand Report Elements for Ad Hoc Analysis.

Explore Cell Content

View cell details such as the contributing values and time, rollups, level, and account information.
View the Audit Trail report with a history of user-entered changes for the cell. Enable Audit Trail for your instance.
Drill into transactions to run a default Transaction Detail report for the cell. Enable Transactions for your instance. If your instance integrates with Workday, you can drill into Workday to view transactions.

Expand and Collapse Rows or Columns

Either child rows display indented below the parent or child columns display to the left of the parent. Depending on your user setting, you can select the direction of expansion using the Expand dialog.
All formatting
excluding
borders or merged cells copy from the parent to all expanded child rows or columns.
Existing Excel formulas from parent copy to the child rows or columns.
The parent and child rows or columns refresh with the latest data and element structure based on your model.

Child rows or columns remove.
The parent remains and refreshes with the latest data.

Expand a Single Parent on the Row

You can expand a parent element such as Revenue to see the child product and service revenue amounts. You can again expand the Product Revenue element to see the child product groups.

To expand a parent element on the row:

Select a column and apply the time element for which you want to see data.
Select a row.
From the Elements pane, select a single parent account element.
Right-click the account element and click
Apply and Expand
from the context menu.
The report refreshes and data for child revenue elements display for the parent.

Expand a Single Parent on the Column.

You can expand a parent time element such as FY 2020 to see the data for quarters. You can further expand a quarter such as Q1 to see monthly data for the quarter.

To expand a parent element on the column:

Select a row and apply an account element.
Select a column.
From the Elements pane, select a single parent time element (absolute or relative).
Right-click the time element and click
Apply and Expand
from the context menu.
The report refreshes and child time elements display for the parent.
To collapse the expanded parent element, right-click and select
OfficeConnect
>
Collapse
from the context menu.

Expand Multiple Parents on Rows or Columns

When you expand a row or column where you applied multiple parent elements, you have these options:

Expand by either parent element. The resulting expansion includes a row or column for each child element of the expanded parent. It also includes a row or column for the element that you didn't expand. Example: You apply Assets and Net Income to a row and then select to expand only Assets. The resulting expansion produces rows for the children of Assets and a row for Net Income.
Expand by multiple parent elements of the same type. The resulting expansion includes the children of all parents. Example: You expand a row with an Assets parent and a Liabilities parent. Both parents have the element type of Accounts. The resulting expansion produces a flat list of the children under Assets and the children under Liabilities.
Expand by multiple parent elements of different types. The resulting expansion includes the Cartesian product of the children of the expanded parents.
Example: You have a row where you applied the Operational Expenses account and the Engineering and Sales levels. You can:
Expand by account only to view how data rolls up from all the child operating expense accounts.
Expand by level only to view how data rolls up from all the child engineering levels or all child sales levels.
Expand by account and the Engineering level to view how data rolls up for all the child Operational Expense accounts for the Engineering levels.
Expand by account and both Sales and Engineering levels. This expansion lets you view how data rolls up for all the child Operational Expense accounts for the Sales and Engineering levels.

When you expand a row or column with a parent and a leaf-level child element, the resulting expansion includes:

A row or column for each child element of the expanded parent.
A row for the leaf-level child element.

To expand multiple parent elements:

Select a row or column that has multiple parent elements applied.
Right-click and select
OfficeConnect
Expand
from the context menu. The Expand dialog displays.
(Optional) Use the
Move
options to reorder the elements as you want to expand them on the grid. Example: If you order versions before accounts, versions expand first on the grid followed by accounts.
Select the elements to expand. Hold down Shift to select multiple elements.
(Optional) To change the default behavior for new expansions:
Select the direction of expansion.
Unselect
Exclude rows with no data
to keep any blank rows with no data.
Unselect
Apply the row/column currency to descendants
not to report with rollups that add up using the same currency.
Select
Exclude uncategorized elements
to remove these elements.
Select
Exclude elimination elements
to remove these elements.

Focus on Parts of an Expansion

Use these options to focus on specific rows or columns of an expansion. Select one or more rows or columns within an expansion. The selection doesn't need to be contiguous.

Keep Only
. Displays only the selection and remove other parts of the expansion.
Remove Only
. Removes the selection from the expansion.

Selecting either option causes the original expansion to break.

Example: You expand the Assets element to display all its children.

Current Assets
Fixed Assets
Long Term Assets
Other Assets

Next, you select to keep only Current Assets and Fixed Assets. The remaining 2 children remove from the expansion so you can focus on the two that you kept.

Conversely, you can select to remove only Current Assets and Fixed Assets from the expansion to display the remaining 2 children.

To focus on specific parts an expansion:

From an expansion, select the elements that you want to focus on.
Right-click and from the OfficeConnect context menu, select
Keep Only
or
Hide Only
.

Show all Descendants or only Leaf Levels in an Expansion

Depending on what level of detail you want to see in an expansion, use these options:

Expand All
. Displays all descendant parent and child elements of the element that you select.
Expand to Leaf Level
. Displays only the child elements at the leaf level of the element that you select. Excludes all rollup elements.

To display all descendants in an expansion:

Select any parent element that is part of an expansion.
Right-click and from the OfficeConnect context menu, select
Expand All.

To display only the leaf levels in an expansion:

Select any parent element that is part of an expansion.
Right-click and from the
OfficeConnect
context menu, select
Expand to Leaf Level
.

When multiple expandable elements apply to a row or column, selecting either option opens the Expand dialog. After you select the elements in the dialog:

Expand All
adds all descendant parent and child elements of the selected elements to the grid.
Expand to Leaf Level
adds only the leaf-level child elements of the selected elements to the grid.

Expand to a Specific Level

Depending on what hierarchy level you want to explore, you can expand a single parent element to display elements only at that specific level. Ancestors are excluded by default. You can override the default to include ancestors in the expansion.

Example:

The Liabilities and Equities account hierarchy includes these elements:

Liabilities (level 1)
Current Liabilities (level 2)
Long Term Liabilities (level 2)
Equity (level 1)
YTD Earnings (level 2)
Retained Earnings (level 2)

You select to expand the Liabilities and Equities account only at level 2. The default expansion displays these elements under Liabilities and Equities:

Current Liabilities
Long Term Liabilities
YTD Earnings
Retained Earnings

You uncheck the

Exclude ancestors

option and expand again. The expansion updates to add the parent Liabilities and Equity elements.

To expand only to a specific level:

In the report, select a row or column that has a parent element applied.
Right-click and from the OfficeConnect context menu, select
Expand to
. The Expand to dialog displays.
Optional. If multiple parent elements exist at the selected location, from the
Elements
drop-down, select which parent element to expand.
Select a hierarchy level to expand to.
To display ancestors, uncheck
Exclude ancestors
.
Click
OK
.

Identify an Expanded Element from the Review Tab

To identify an element that is part of an expansion from the

Review

tab:

Select a single row or column that is part of an expansion.
In the Reporting pane, select
Review
.
Notice the expansion [+] icon next to it.

Actions that Disable a Parent-Child Hierarchy

These actions disable a parent-child hierarchy:

You delete any parent within a hierarchy.
Child elements are no longer contiguous. You inserted rows between children or between the parent and children.
The metadata for the parent and children doesn’t match. Example: Parent is Current Assets but children are now Engineering.
The parent is an unknown element either because you can’t access it or you deleted it.
You group elements that are already part of an expansion.

Excluding Blank Rows During Expansion

When you expand elements, the

Exclude rows with no data

option inactivates when you apply:

The same element type on both rows and columns.
An element to a cell within a row.
Level and Currency elements on rows or columns and select Level Currency in workbook properties.
Any Time element (absolute, relative, component, or context) on both rows and columns.