Reporting Tree

The Reporting Tree, used with the Excel Add‑in, lets you filter reports dynamically by selecting nodes in a hierarchy—such as regions, departments, or business units. It allows you to generate multiple variations of the same report without manually editing formulas or filters.

To use the Reporting Tree, your Excel workbook must already include at least one Data Extraction or Formula.

note

Only one Reporting Tree can be used per Excel workbook.

When to use reporting trees

• Filtering reports by hierarchy: Filter formulas, data extractions, and pivot tables based on the selected node.
• Consolidating data across groups: Combine results for regions, departments, or other entities in a single Excel workbook.
• Applying filters dynamically: Control filters through the tree without modifying formulas.
• Duplicating reports per entity: Create separate reports for multiple nodes.

Select a reporting tree

1. In the Add‑ins tab, select Reporting Tree.
2. Log in if prompted.
3. In the dialog, select the reporting tree and select Ok.

Define the reference cell

1. In your sheet, select the cell that will store the Reporting Tree node.
2. In the Add‑ins tab, select Reporting Tree Node Selector.
3. In the dialog, choose a node and select Ok. You can use wildcards in the reference cell to match multiple nodes.

Use of wildcards for filtering

Use wildcards to filter text values. Only one filtering method is supported at a time—you cannot combine patterns (for example, 4?0* is not valid).

• ? replaces one character
• * replaces multiple characters

Wildcard example	Description
4000	Match the value 4000.
4*	Match any value starting with 4.
4???	Match values starting with 4 and containing exactly four characters.
4???-???-10	Match values starting with 4, followed by -, any three characters, another -, any three characters, and ending with 10 (total length: 11 characters).

How Reporting Tree interacts with Add‑in

Once configured, the Reporting Tree node dynamically filters your reports. You can switch between nodes at any time without modifying formulas, extractions, or pivot layouts.

Feature behavior

Feature	How it works with the Reporting Tree
Formulas	Apply the selected Reporting Tree node automatically. When the node changes, formulas update to show data for that region, department, or business unit.
Formula Drill Down	Apply the selected node to drill‑down results, ensuring you see only the detailed records relevant to that part of the hierarchy.
Data Extraction	Use the selected node as a dynamic filter. Each time you change the node, the extracted data updates accordingly. Refresh the data manually using the Data Extraction Refresh button.
Pivot Table	Show only the data for the selected node, allowing you to reuse the same pivot layout across multiple entities. Refresh pivot tables manually using the Pivot Table Refresh button.
Open View Data Entry	Do not apply the Reporting Tree node automatically. However, if the underlying formula includes the node, the view or data entry session reflects it.

Reporting Tree Duplicator

Use the Reporting Tree Duplicator the Reporting Tree Duplicator to create a copy of your sheet for each Distributable node in the Reporting Tree. This lets you generate multiple versions of a report—one per business unit, region, or department—without manually duplicating or editing each sheet.

Prerequisites

Before using the duplicator, ensure the following:

• A Reporting Tree is configured.
• A Reference Cell for the Reporting Tree node is set in the sheet.

Duplicate reports

1. Select a refernce cell in the sheet.
2. In the Add‑ins tab, select Reporting Tree Duplicator.
3. In the Reporting Tree node cell field, confirm the reference cell. If the field is empty, select the cell manually.
4. Choose a duplication method.

Duplication method

Method	How it works
Duplicate by tab	Create a separate sheet tab for each Distributable node. Use the node label from the tree as the tab name. Update the Description and Node ID automatically on each sheet. Refresh all formulas that use the Reporting Tree node. Note: Generating many tabs may take time because formulas refresh for each sheet.
Duplicate by row	Duplicate the row containing the Reporting Tree node for each node in the tree. Adjust formulas automatically for each duplicated row. Include all nodes by default.

Clear the reporting tree cache

If updates to the Reporting Tree structure or nodes on SEI aren’t appearing in Excel, clear the cache to reload the latest data.

1. In Excel, open the Add‑ins tab.
2. Select Clear Cache.
3. In the dialog, select Cache Data Model Data.
4. Check the Reporting Tree option.
5. Click Ok to refresh the cache.