Pivot Table

Pivot tables build a cube-style structure directly in Excel, connected to your data model. Instead of extracting raw data first, you query the data model directly and explore the results using Excel's native pivot tools. You can summarize values, apply filters, rearrange dimensions, and drill into totals interactively without writing formulas.

The pivot is built from the fields and dimensions you select in the dialog. Once inserted, you arrange them using Excel's standard pivot pane, placing fields into Rows, Columns, Values, and Filters. You can also narrow the data before it loads using dimension filters or a Reporting Tree node, so only the relevant records are included from the start.

If the formula references a Reporting Tree node, it is applied as a filter on the results automatically.

When to create a pivot table

• Summarize data across dimensions such as account, product, or region without loading raw data into Excel.
• Compare values over time for month-over-month or year-over-year analysis.
• Drill into totals to view the records behind a high-level summary.
• Explore data by rearranging dimensions and applying filters on the fly.

Create a pivot table

1. In the Add-ins tab, select Pivot Table.
2. Configure the Data Model and Environment.
3. Under Dimension Settings, add filters to narrow the dataset as needed.
4. Under Columns, select the fields to include and arrange them using the arrow buttons.
5. Choose the output location for the pivot table.
6. Select Ok to insert the pivot table.
7. Use the pivot table pane to add Values, Filters, Rows, and Columns.
8. To filter using a Reporting Tree node, select the reference cell from the Reporting Tree in the Reporting Tree node field.

Pivot table properties

Property	What it does
Data Model	The data model to query.
Environment	The environment for the pivot.
Dimension Settings	Adds filters to narrow the data.
Columns	The available fields you can add to the pivot.
Selected Columns	The fields to include, arranged using the arrow buttons.
Output Location	Where to insert the pivot table: New Worksheet to insert into a new sheet Existing Worksheet to insert at a specified starting cell.

Apply dimension filters

Dimension filters narrow the pivot to specific values, such as a particular company, period, or product line, before the data loads into Excel.

1. Select a Dimension to filter results for the chosen field.
2. Choose a filter type:
   • List to include or exclude specific values. Wildcards are supported.
   • From-To to define a numeric or string-based range.
   • Select All to include all values in the dimension (default).
3. Define filter values using a prompt, a cell reference, or manual entry.
4. Select Ok to apply the filter. The complete formula appears in the Excel formula bar.

Use of wildcards for filtering

Wildcards apply only to text values, not numbers. You can use only one filtering method at a time: wildcard, list, or range. Combining methods, such as 4???;[4000:4999], is not supported.

Supported wildcards

• ? matches exactly one character
• * matches one or more characters
• ! excludes characters or ranges
• ; defines a list of values
• [ ] defines a numeric or text range

Filtering Examples

Example	What it matches
4000	Matches exactly 4000.
4000;5000;6000	Matches 4000, 5000, or 6000.
4*	Matches any value starting with 4.
4???	Matches values starting with 4 with exactly four characters.
[4000:4999]	Matches values between 4000 and 4999.
!4000	Excludes 4000.
!4*	Excludes values starting with 4.
!4???	Excludes values starting with 4 with exactly four characters.
![4000:4999]	Excludes values between 4000 and 4999.
4???-???-10	Matches values starting with 4, followed by 3 characters, a dash, 3 characters, another dash, and ending with 10.
!1000;2000;7000	Excludes 1000, 2000, and 7000.

Refresh a pivot table

The Pivot Table Refresh button in the Add-ins tab updates all pivot tables in your Excel file with the latest data from the data model. Pivot tables do not refresh automatically, so a manual refresh is always required after:

• Change filters or Reporting Tree nodes to make sure the pivot reflects the updated selections.
• Update parameters to reload the latest values from the data model.
• Notice that pivot tables have not updated after a change was made.