> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reducto.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Pivot tables

## `PivotTableCollection`

A range, a table or an address such as "Data!A1:E13"; a bare address is on `sheet`.

Import from `@reductoai/xlsx/officejs`.

### PivotTableCollection properties

| Name | Type | Description |
| - | - | - |
| `count` | `number` | read-only |
| `items` | `ExcelPivotTable[]` | read-only |

### `PivotTableCollection.add()`

```ts theme={null}
add(
  name: string,
  source: string | ExcelRange | ExcelTable,
  destination: string | ExcelRange,
): ExcelPivotTable
```

An empty pivot table, as Excel's Insert PivotTable makes before any field is placed.
`source` is a range, a table or an address such as "Data!A1:E13"; `destination` its top-left cell.

### `PivotTableCollection.getCount()`

```ts theme={null}
getCount(): ClientResult<number>
```

### `PivotTableCollection.getItem()`

```ts theme={null}
getItem(name: string): ExcelPivotTable
```

By name, case-insensitive.

### `PivotTableCollection.getItemOrNullObject()`

```ts theme={null}
getItemOrNullObject(name: string): Nullable<ExcelPivotTable>
```

### `PivotTableCollection.refreshAll()`

```ts theme={null}
refreshAll(): void
```

Refreshes every pivot table in the collection, as `ExcelPivotTable.refresh`.

## `ExcelPivotTable`

`Excel.PivotTable`, read from its part: fields, layout and the ranges it covers.

Import from `@reductoai/xlsx/officejs`.

### ExcelPivotTable properties

| Name | Type | Description |
| - | - | - |
| `allowMultipleFiltersPerField` | `boolean` | |
| `calculatedFields` | `CalculatedFieldCollection` | read-only Extension (VBA `CalculatedFields`): fields that are a formula of other fields. |
| `columnHierarchies` | `RowColumnPivotHierarchyCollection` | read-only |
| `dataHierarchies` | `DataPivotHierarchyCollection` | read-only |
| `enableDataValueEditing` | `boolean` | |
| `filterHierarchies` | `FilterPivotHierarchyCollection` | read-only |
| `hierarchies` | `PivotHierarchyCollection` | read-only Every source field, to place with an axis collection's `add`. |
| `id` | `string` | read-only |
| `layout` | `PivotLayout` | read-only |
| `missingItemsLimit` | `number` | Number of items to retain per field: -1 Automatic, 0 None; VBA's `PivotCache.MissingItemsLimit`. |
| `name` | `string` | |
| `refreshOnOpen` | `boolean` | The cache's `refreshOnLoad`; an edit that leaves the cache stale turns it on again. |
| `rowHierarchies` | `RowColumnPivotHierarchyCollection` | read-only |
| `useCustomSortLists` | `boolean` | |
| `worksheet` | `ExcelWorksheet` | read-only |

### `ExcelPivotTable.changeDataSource()`

```ts theme={null}
changeDataSource(source: string | ExcelRange | ExcelTable): void
```

Extension (VBA `SourceData`): Change Data Source. Fields keep their place by name; a table on
a shared cache gets a cache of its own.

### `ExcelPivotTable.clearAllFilters()`

```ts theme={null}
clearAllFilters(): void
```

Extension (VBA `ClearAllFilters`): every field shows all its items again.

### `ExcelPivotTable.clearTable()`

```ts theme={null}
clearTable(): void
```

Extension (VBA `ClearTable`): removes every field, filter and format; the table stays.

### `ExcelPivotTable.delete()`

```ts theme={null}
delete(): void
```

Removes the pivot table and its cells.

### `ExcelPivotTable.getDataSourceString()`

```ts theme={null}
getDataSourceString(): ClientResult<string>
```

The source as Excel writes it: `Sheet1!A1:D13`, or the name or table it reads.

### `ExcelPivotTable.getDataSourceType()`

```ts theme={null}
getDataSourceType(): ClientResult<"Unknown" | "LocalRange" | "LocalTable">
```

### `ExcelPivotTable.move()`

```ts theme={null}
move(destination: string | ExcelRange): void
```

Extension (VBA `Location`): Move PivotTable. The table's first cell goes to `destination`, lower
when its filters need the rows above; cells there are overwritten.

### `ExcelPivotTable.refresh()`

```ts theme={null}
refresh(): void
```

Rebuilds the cells from the source as Excel's Refresh does. Throws for a layout we cannot
rebuild yet (grouping, dates, sorted fields, values shown on rows) or a table that would
grow over other cells.

### `ExcelPivotTable.set()`

```ts theme={null}
set(
  properties: ExcelPivotTable | UpdateData<ExcelPivotTable>,
  options?: UpdateOptions,
): void
```

Sets several properties at once; a navigation member takes a nested batch.

## `PivotHierarchy`

A source field, or one placed on an axis (`RowColumnPivotHierarchy`, `FilterPivotHierarchy`).

Import from `@reductoai/xlsx/officejs`.

### PivotHierarchy properties

| Name | Type | Description |
| - | - | - |
| `name` | `string` | read-only |
| `fields` | `PivotFieldCollection` | read-only `RowColumnPivotHierarchy.fields`: the one field of a source column. |
| `id` | `string` | read-only |
| `position` | `number` | Place on its axis, from 0. |

### `PivotHierarchy.set()`

```ts theme={null}
set(
  properties: PivotHierarchy | UpdateData<PivotHierarchy>,
  options?: UpdateOptions,
): void
```

Sets several properties at once; a navigation member takes a nested batch.

## `RowColumnPivotHierarchy`

A field on the row or column axis.

Import from `@reductoai/xlsx/officejs`. Extends [`PivotHierarchy`](https://docs.reducto.ai/office/xlsx/api/pivots#pivothierarchy).

### RowColumnPivotHierarchy properties

| Name | Type | Description |
| - | - | - |
| `name` | `string` | read-only |
| `fields` | `PivotFieldCollection` | read-only `RowColumnPivotHierarchy.fields`: the one field of a source column. |
| `id` | `string` | read-only |
| `position` | `number` | Place on its axis, from 0. |

### `RowColumnPivotHierarchy.set()`

```ts theme={null}
set(
  properties: PivotHierarchy | UpdateData<PivotHierarchy>,
  options?: UpdateOptions,
): void
```

Sets several properties at once; a navigation member takes a nested batch.

### `RowColumnPivotHierarchy.setToDefault()`

```ts theme={null}
setToDefault(): void
```

## `DataPivotHierarchy`

`Excel.DataPivotHierarchy`: a value field with its caption, source field and summary.

Import from `@reductoai/xlsx/officejs`.

### DataPivotHierarchy properties

| Name | Type | Description |
| - | - | - |
| `field` | `PivotHierarchy` | read-only Name of the source field the values come from. |
| `id` | `string` | read-only |
| `name` | `string` | |
| `numberFormat` | `string` | The format code of the value cells, as `Range.numberFormat` gives it. |
| `position` | `number` | |
| `showAs` | `ShowAsRule` | How the values show: as they are, or measured against other cells of the pivot. |
| `summarizeBy` | `string` | A caption Excel made ("Sum of Amount") follows the new function; one set by hand stays. |

### `DataPivotHierarchy.set()`

```ts theme={null}
set(
  properties: DataPivotHierarchy | UpdateData<DataPivotHierarchy>,
  options?: UpdateOptions,
): void
```

Sets several properties at once; a navigation member takes a nested batch.

### `DataPivotHierarchy.setToDefault()`

```ts theme={null}
setToDefault(): void
```

The summary Excel picks for the field, its caption and the General format.

## `PivotField`

`Excel.PivotField`: sorts and filters the items of a row, column or filter field.

Import from `@reductoai/xlsx/officejs`.

### PivotField properties

| Name | Type | Description |
| - | - | - |
| `name` | `string` | read-only |
| `caption` | `string` | Extension (VBA `Caption`): the label the field shows; an empty one goes back to the source name. |
| `id` | `string` | read-only |
| `items` | `PivotItemCollection` | read-only |
| `layoutBlankLine` | `boolean` | Extension (VBA `LayoutBlankLine`): an empty line after each item's group. |
| `layoutCompactRow` | `boolean` | Extension (VBA `LayoutCompactRow`): the next field's labels share this field's column. |
| `layoutForm` | `"Outline" \| "Tabular"` | Extension (VBA `LayoutForm`): item labels in outline or tabular form. |
| `layoutSubtotalLocation` | `"Top" \| "Bottom"` | Extension (VBA `LayoutSubtotalLocation`): where the group's subtotal goes in outline form. |
| `repeatLabels` | `boolean` | Extension (VBA `RepeatLabels`): the item's label on every line of its group. |
| `showAllItems` | `boolean` | Lists the field's items that have no data too. |
| `showDetail` | `boolean` | Extension (VBA `PivotField.ShowDetail`): expands or collapses every item of the field. |
| `subtotals` | `FieldSubtotals` | Only `automatic` is supported; a custom function throws. |

### `PivotField.applyFilter()`

```ts theme={null}
applyFilter(filter: PivotFilters): void
```

Manual, label and value filters; a date filter throws.

### `PivotField.clearAllFilters()`

```ts theme={null}
clearAllFilters(): void
```

### `PivotField.clearFilter()`

```ts theme={null}
clearFilter(filterType: PivotFilterType): void
```

### `PivotField.getFilters()`

```ts theme={null}
getFilters(): PivotFilters
```

The manual, label and value filters; date filters are not read.

### `PivotField.getGrouping()`

```ts theme={null}
getGrouping(): PivotGrouping | null
```

Extension: the field's grouping as Excel's Grouping window shows it; null for text.

### `PivotField.group()`

```ts theme={null}
group(options: PivotGroupOptions): void
```

Extension (VBA `Range.Group`): numbers in steps of `by` from `start` to `end`, or dates by
`periods`. A date field gives way to one new field per period, named like "Months (Date)".
With `items`, those labels become one group ("Group1") in a new field outside, like "Region2".

### `PivotField.isFiltered()`

```ts theme={null}
isFiltered(filterType?: PivotFilterKind): ClientResult<boolean>
```

### `PivotField.set()`

```ts theme={null}
set(properties: PivotField | UpdateData<PivotField>, options?: UpdateOptions): void
```

Sets several properties at once; a navigation member takes a nested batch.

### `PivotField.sortByLabels()`

```ts theme={null}
sortByLabels(sortBy: SortBy): void
```

### `PivotField.sortByValues()`

```ts theme={null}
sortByValues(
  sortBy: SortBy,
  valuesHierarchy: DataPivotHierarchy,
  pivotItemScope?: (string | { name: string })[],
): void
```

Orders the items by `valuesHierarchy` on the grand total line, or on the line of the other
axis whose items `pivotItemScope` names, outermost first.

### `PivotField.ungroup()`

```ts theme={null}
ungroup(items?: string[]): void
```

Extension (VBA `Range.Ungroup`): on a date's period field, every period goes and the date comes
back. `items`: only those groups of an item-group field split up.

## `PivotLayout`

`Excel.PivotLayout`.

Import from `@reductoai/xlsx/officejs`.

### PivotLayout properties

| Name | Type | Description |
| - | - | - |
| `altTextDescription` | `string` | |
| `altTextTitle` | `string` | |
| `autoFormat` | `boolean` | |
| `displayErrorString` | `boolean` | VBA `DisplayErrorString` (an extension): show `errorString` in place of each error value. |
| `emptyCellText` | `string` | What a data cell no record reaches shows while `fillEmptyCells` is on. |
| `enableFieldList` | `boolean` | |
| `errorString` | `string` | VBA `ErrorString` (an extension): the text shown in a value cell that holds an error. |
| `fillEmptyCells` | `boolean` | |
| `layoutType` | `PivotLayoutType` | |
| `preserveFormatting` | `boolean` | |
| `showColumnGrandTotals` | `boolean` | The Grand Total row, which totals each column; the part calls it `rowGrandTotals`. |
| `showDrillIndicators` | `boolean` | Extension (VBA `ShowDrillIndicators`): the +/- buttons on item labels. |
| `showFieldHeaders` | `boolean` | Field Headers: the row and column captions and the filter buttons on them. |
| `showRowGrandTotals` | `boolean` | The Grand Total column, which totals each row; the part calls it `colGrandTotals`. |
| `showTableStyleColumnHeaders` | `boolean` | Extension (VBA `ShowTableStyleColumnHeaders`): the style's header row look. |
| `showTableStyleColumnStripes` | `boolean` | Extension (VBA `ShowTableStyleColumnStripes`): banded columns. |
| `showTableStyleLastColumn` | `boolean` | Extension (VBA `ShowTableStyleLastColumn`): the style's last column look on the Grand Total column. |
| `showTableStyleRowHeaders` | `boolean` | Extension (VBA `ShowTableStyleRowHeaders`): the style's first column look on the row labels. |
| `showTableStyleRowStripes` | `boolean` | Extension (VBA `ShowTableStyleRowStripes`): banded rows. |
| `subtotalLocation` | `"AtTop" \| "AtBottom" \| "Off"` | |
| `valuesOnRows` | `boolean` | Extension (VBA `DataPivotField.Orientation`): the Values field, which shows with two or more value fields, is a row field rather than a column field. |
| `valuesPosition` | `number` | Extension (VBA `DataPivotField.Position`): the Values field's place on its axis, from 0. |

### `PivotLayout.displayBlankLineAfterEachItem()`

```ts theme={null}
displayBlankLineAfterEachItem(display: boolean): void
```

Ends each row item's group with an empty line.

### `PivotLayout.getAxisFieldName()`

```ts theme={null}
getAxisFieldName(cell: string | ExcelRange): string | null
```

Extension: the row or column field whose label `cell` shows, as Excel's Active Field reads it.

### `PivotLayout.getColumnLabelRange()`

```ts theme={null}
getColumnLabelRange(): ExcelRange
```

### `PivotLayout.getDataBodyRange()`

```ts theme={null}
getDataBodyRange(): ExcelRange
```

### `PivotLayout.getDataHierarchy()`

```ts theme={null}
getDataHierarchy(cell: string | ExcelRange): DataPivotHierarchy
```

The value field a body cell shows.

### `PivotLayout.getFilterAxisRange()`

```ts theme={null}
getFilterAxisRange(): ExcelRange
```

The report filter cells above the table: one row per filter field, caption and value.

### `PivotLayout.getPivotItems()`

```ts theme={null}
getPivotItems(axis: PivotAxis, cell: string | ExcelRange): ClientResult<PivotItem[]>
```

The items of the row or column fields that make up the cell's value; a grand total has none.

### `PivotLayout.getPivotStyle()`

```ts theme={null}
getPivotStyle(): ClientResult<string | null>
```

The pivot style's name, or null for None. Office.js `getPivotStyle` returns the style object;
read it with `workbook.pivotTableStyles.getItem(name)`.

### `PivotLayout.getRange()`

```ts theme={null}
getRange(): ExcelRange
```

### `PivotLayout.getRowLabelRange()`

```ts theme={null}
getRowLabelRange(): ExcelRange
```

### `PivotLayout.repeatAllItemLabels()`

```ts theme={null}
repeatAllItemLabels(repeatLabels: boolean): void
```

Repeats each row item's label on every line of its group, in outline and tabular form.

### `PivotLayout.set()`

```ts theme={null}
set(properties: PivotLayout | UpdateData<PivotLayout>, options?: UpdateOptions): void
```

Sets several properties at once; a navigation member takes a nested batch.

### `PivotLayout.setAutoSortOnCell()`

```ts theme={null}
setAutoSortOnCell(cell: string | ExcelRange, sortBy: "Ascending" | "Descending"): void
```

Sorts as Excel's ribbon does from `cell`: a label sorts its field, a value sorts by that column.

### `PivotLayout.setStyle()`

```ts theme={null}
setStyle(style: string | { name: string } | null): void
```

Office.js `setStyle`: a built-in or custom pivot style by name, or null for None.
