How to Create Pivot Tables from a Source Range with Multi-Field Support in OfficeCLI

Use officecli add --type pivottable with --prop source=Sheet!A1:D100 to define your data range, then specify multiple row fields with --prop rows=Region,Product and value aggregations with --prop values=Sales:sum,Quantity:avg.

Creating pivot tables from a source range in OfficeCLI involves defining a rectangular data region and mapping fields to the four pivot axes: rows, columns, values, and filters. The CLI internally splits this into a workbook-level PivotTableCacheDefinitionPart (data cache) and a worksheet-level PivotTablePart (rendered table), both managed by the PivotTableHelper class in [src/officecli/Core/PivotTableHelper.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/PivotTableHelper.cs#L904). This guide covers multi-field configurations, aggregation functions, and placement options based on the actual OfficeCLI source implementation.

Defining the Source Range

Every pivot table requires a source property that points to a rectangular range containing headers and data. The helper validates this reference through PivotTableHelper.ResolvePivotSourceSpec (around line 1669) before creating or reusing a cache definition.

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500

The source can be:

• Sheet-qualified range: Sheet1!A1:D100
• Named range: SalesData (if defined in the workbook)

If you omit the anchor property (pos), the pivot auto-places immediately after the source range via PivotTableHelper.CreatePivotTable (line 1675).

Configuring Multi-Field Rows, Columns, and Filters

OfficeCLI supports comma-separated field lists for multi-field axis configurations. The parser in PivotTableHelper.BuildPivotTableDefinition (around line 1120) tokenizes these into individual PivotField references.

Multi-Field Row Hierarchy

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Region,Product,Rep

Fields appear left-to-right in the pivot, creating a nested hierarchy: Region → Product → Rep.

Multi-Field Columns

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop cols=Year,Quarter

Filter Fields

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop filters=Status,Priority

Filter fields populate the pivot's filter area without appearing in rows or columns.

Setting Value Aggregations with Multi-Field Support

The values property accepts Field:agg tuples separated by commas. Valid aggregations per the schema include sum, avg, count, max, min, product, stddev, stddevp, var, and varp.

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Region,Product \
  --prop cols=Year \
  --prop values=Revenue:sum,Units:count,Margin:avg

Each tuple generates a separate data field in the pivot cache. The parser handles type coercion and aggregation function mapping within BuildPivotTableDefinition.

Top-N Row Limits and Style Options

Limiting Row Keys

The topN property restricts displayed row keys to the highest N values by the first value field:

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Product \
  --prop values=Revenue:sum \
  --prop topN=10

This executes in PivotTableHelper.CreatePivotTable (around line 1089) by setting page field filters on the row axis.

Layout and Style Flags

Property	Values	Effect
style	compact, outline, tabular	Pivot table format
subtotals	true, false	Show/hide subtotal rows/columns
grandtotals	true, false	Show/hide grand total row/column
blankrows	true, false	Insert blank rows after items

officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Region,Product \
  --prop values=Sales:sum \
  --prop style=compact,outline \
  --prop subtotals=false \
  --prop grandtotals=false

Style processing occurs in BuildPivotTableDefinition (around line 1205).

Complete Multi-Field Configuration Example

officecli add sales.xlsx /Dashboard --type pivottable \
  --prop source=RawData!A1:F1000 \
  --prop rows=Territory,Account_Manager \
  --prop cols=Quarter,Month \
  --prop values=Deal_Size:sum,Win_Rate:avg,Opportunities:count \
  --prop filters=Stage,Priority \
  --prop topN=50 \
  --prop style=compact \
  --prop subtotals=true \
  --prop grandtotals=true \
  --prop pos=H5

This creates a pivot table anchored at cell H5 with:

• Two-level row hierarchy (Territory → Account_Manager)
• Two-level column hierarchy (Quarter → Month)
• Three aggregated metrics with different functions
• Two active filters
• Top-50 row limit
• Compact styling with subtotals and grand totals enabled

Verifying and Inspecting Pivot Tables

After creation, query the pivot structure using get with JSON formatting. The CLI exposes read-only child nodes (pivotfield, pivotrow, pivotcolumn, pivotdata) for inspection.

officecli get sales.xlsx /Dashboard/pivottable[1] --format json

These structural nodes cannot be directly added, set, or removed—they reflect the internal Open XML representation per the schema in [schemas/help/xlsx/pivottable.json](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/pivottable.json).

Attaching Slicers for Interactive Filtering

Once a pivot table exists, bind slicers using the slicer handler in [src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs#L42):

# Create slicer filtered by Region

officecli add sales.xlsx /Dashboard --type slicer \
  --prop pivotTable=/Dashboard/pivottable[1] \
  --prop field=Region

# Additional slicer for Product

officecli add sales.xlsx /Dashboard --type slicer \
  --prop pivotTable=/Dashboard/pivottable[1] \
  --prop field=Product

PowerShell Equivalent Syntax

officecli add sales.xlsx "/Dashboard" --type pivottable `
  --prop source=RawData!A1:F1000 `
  --prop rows=Territory,Account_Manager `
  --prop cols=Quarter,Month `
  --prop values=Deal_Size:sum,Win_Rate:avg,Opportunities:count `
  --prop filters=Stage,Priority `
  --prop topN=50 `
  --prop style=compact `
  --prop subtotals=true `
  --prop grandtotals=true `
  --prop pos=H5

Summary

• source (alias src) defines the rectangular data range for cache generation
• Multi-field axes use comma-separated lists: rows=A,B,C creates nested hierarchies
• Value aggregations specify Field:function tuples: Revenue:sum,Units:avg
• topN limits displayed row keys; pos overrides auto-placement
• Style flags (style, subtotals, grandtotals) control visual presentation
• Slicers attach to existing pivots via separate --type slicer commands
• Core implementation resides in PivotTableHelper.cs; schema documentation in pivottable.json

Frequently Asked Questions

What aggregation functions does OfficeCLI support for pivot values?

OfficeCLI supports standard Excel aggregations: sum, avg, count, max, min, product, stddev, stddevp, var, and varp. Specify these in Field:agg format within the values property. The parser in BuildPivotTableDefinition validates and maps these to Open XML aggregation constants.

Can I create a pivot table without specifying row or column fields?

Yes. A pivot table with only values and optionally filters produces a single-cell summary (grand totals only). However, at minimum you must specify source and at least one field mapped to any axis (rows, columns, values, or filters) for a valid pivot cache.

How does OfficeCLI handle overlapping source and pivot placement?

If you omit the pos property, PivotTableHelper.CreatePivotTable automatically places the pivot starting one column after the source range's right edge. For explicit placement, provide pos=CellReference—the helper validates no overlap exists before insertion.

Why can't I modify pivot field properties directly through child nodes?

The pivotfield, pivotrow, pivotcolumn, and pivotdata nodes exposed via get are read-only structural reflections of the Open XML parts. Per the schema in pivottable.json, these support only get and delete operations at the parent pivot level. Modify pivot structure by recreating with updated --prop values or using Excel's object model.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}