Data Extraction

The Data Extraction feature provides real‑time access to external data sources—such as ERP systems—allowing you to build live, refreshable reports directly in Excel. You can select fields to extract, apply filters, configure auto‑refresh, and create dynamic lists that stay synchronized with your source data.

When to use the the data extraction

• Generating live tables in Excel: Insert real‑time data from external systems directly into your sheet.
• Filtering and sorting data subsets: Apply conditions to retrieve only the data you need.
• Building dynamic lists: Keep reports automatically updated as new records appear in the source system.
• Auto-refreshing data on file open: Refresh your report automatically whenever the Excel workbook is opened.

Create a data extraction

1. In the Add‑ins tab, select Data Extraction.
2. In the dialog, configure the Data Model and Environment.
3. Under Dimension Settings, add filters to narrow the data as needed.
4. Under Columns, select the fields to extract and use the arrow buttons to adjust their order.
5. Configure any additional settings.
6. Select Ok to generate the extraction table.
7. To filter values using a Reporting Tree node, select the reference cell from the Reporting Tree in the Reporting Tree node field.

note

When you edit the Reporting Tree node, all associated formulas refresh automatically to reflect the updated value.

Data extraction properties

Property	Description
Data Model	Select the data model to extract from.
Environment	Choose the environment for the extraction.
Dimension Settings	Adds filters to narrow the data.
Columns	Review the available fields you can extract.
Selected Columns	Arrange the fields to extract using the arrow buttons.

Additional data extraction settings

Setting	Description
Refresh Style	Choose how the sheet updates during refresh: Insert and Delete Cells – (Default) Insert or delete rows to match the result set while preserving surrounding content. Override Cells – Replace all data in the output range, including custom formatting or values. Override Cells first time, Insert and Delete Cells after – Override the range initially, then use insert/delete behavior on later refreshes. Insert Entire Rows – Add new rows as needed without deleting existing ones.
Order By	Sort the extracted records in ascending (ASC) or descending (DESC) order.
Top X	Limit the number of records returned in the extraction.
Distinct Records	Return only unique values. Make sure the Order By field is included in Selected Columns to enable this option.
Show Headers	Add a header row above the extracted data.
Refresh on Workbook Open	Refresh the extraction automatically when the Excel workbook is opened.
Output Location	Choose where to place the extracted data: New Worksheet – Insert data into a new sheet. Existing Worksheet – Insert data at a specified starting cell.

Apply dimension filters

Use dimension filters when your extraction needs to reflect only certain values—such as a specific company, period, or product line. By applying filters, you can tailor the returned data to match the structure of your report, reduce unnecessary rows, and make your Excel workbook easier to maintain as your data changes.

1. Select a dimension to filter results for the specified field and operation.
2. Choose one of the following filter types:
   • List – Include or exclude specific values. Wildcards are supported.
   • From–To – Define a numeric or string-based range.
   • Select All – Include all values in the dimension (default).
3. Define filter values using a prompt, a cell reference, or manual entry.
4. Select Ok to insert the formula. The full formula appears in the Excel formula bar.

Use of wildcards for filtering

• ? replaces one character
• * replaces multiple characters

Wildcard example	Description
4000	Match exactly 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 three characters, a dash, three characters, another dash, and ending with 10

Refresh data extractions

Use the Data Extraction Refresh button in the Add-ins tab to update all data extractions in the current sheet or Excel workbook. This ensures that your tables reflect the latest values from the source system.

Use this feature after modifying filters, changing settings, or updating parameters so your extracted data stays accurate and up to date.