# How to Create Pivot Tables from Source Ranges with showDataAs and Date Grouping in OfficeCLI

> Learn to create Excel pivot tables from source ranges using OfficeCLI. Configure layout, aggregation, date grouping, and percentages with the showDataAs property for powerful data analysis.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-08-01

---

**OfficeCLI generates Excel pivot tables via a single `officecli add` command that references a source range and configures layout, aggregation, date grouping, and percentage calculations through the `showDataAs` property.**

OfficeCLI is an open-source command-line tool for automating Microsoft Office documents. The project's xlsx skill schema defines how pivot tables are constructed from raw worksheet data, supporting advanced features like temporal grouping and relative value calculations.

## Specifying Source Ranges and Pivot Layout

The pivot creation process begins with the **`source`** property, which accepts an absolute worksheet range (e.g., `Sheet1!A1:J51`). According to the schema defined in [SKILL.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/skills/officecli-xlsx/SKILL.md) (lines 260-270), the CLI parses this range to extract column headers and establish the pivot cache.

Field placement is controlled through three key properties:

- **`rows`** – Fields placed on the row axis.
- **`cols`** – Fields placed on the column axis.
- **`values`** – Data fields with aggregation specifications.

## Implementing showDataAs for Percentage Calculations

To display values as percentages rather than raw aggregates, OfficeCLI implements the **showDataAs** transformation through the `values` property syntax: `field:aggregation:showDataAs`.

When a third token is present (such as `percent_of_row`, `percent_of_col`, `percent_of_total`, or `running_total`), the CLI injects a `<showDataAs>` element into the pivot definition. This modifies how Excel renders the calculated values relative to row, column, or grand totals.

## Enabling Date Grouping via Field Suffixes

Date grouping requires no separate configuration flag. Instead, append temporal suffixes directly to date field names in the `rows` or `cols` properties:

- `:year` for annual grouping.
- `:quarter` for quarterly rollup.
- `:month` for monthly aggregation.

When OfficeCLI detects these suffixes, it automatically generates `<fieldGroup>` elements in the underlying OOXML, enabling Excel's native date hierarchy without manual pivot table manipulation.

## Practical CLI Implementation

The following command creates a pivot table that groups dates by year and quarter, displays sales as a percentage of each row, and filters by region:

```bash
officecli add pivot-demo.xlsx "/Sales by Date" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop 'rows=Date:year,Date:quarter' \
  --prop 'values=Sales:sum:percent_of_row' \
  --prop filters=Region \
  --prop layout=outline \
  --prop grandtotals=both \
  --prop style=PivotStyleMedium7

```

This example appears in the repository's documentation at lines 66-74 of [pivot-tables.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/examples/excel/pivot-tables.md).

## Programmatic Batch Generation

For automated workflows, reference the Python implementation in [pivot-tables.py](https://github.com/iOfficeAI/OfficeCLI/blob/main/examples/excel/pivot-tables.py). This script iterates over configuration dictionaries and executes `officecli add` via `subprocess`, demonstrating how to generate multiple pivot tables programmatically from the same source data.

## Key Source Files

- **[SKILL.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/skills/officecli-xlsx/SKILL.md)** – Defines the `pivottable` element schema, including `showDataAs` enumerations and date grouping specifications (lines 260-270).
- **[pivot-tables.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/examples/excel/pivot-tables.md)** – Contains 19 practical examples, including date grouping demonstrations (lines 66-74).
- **[pivot-tables.py](https://github.com/iOfficeAI/OfficeCLI/blob/main/examples/excel/pivot-tables.py)** – Python driver showing programmatic CLI invocation.
- **[README.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/README.md)** – High-level capability overview linking to the full pivot table documentation.

## Summary

- **Source ranges** are specified via the `source` property using absolute worksheet references (e.g., `Sheet1!A1:J51`).
- **showDataAs** calculations use the syntax `field:agg:showDataAs` (e.g., `Sales:sum:percent_of_row`) to generate `<showDataAs>` XML elements.
- **Date grouping** is triggered by appending `:year`, `:quarter`, or `:month` to field names in `rows` or `cols`, creating `<fieldGroup>` hierarchies.
- **Implementation** occurs through the `officecli add --type pivottable` command, which renders OOXML pivot cache and definition files.

## Frequently Asked Questions

### How do I configure date grouping by month instead of year?

Append `:month` to your date field name in the `rows` or `cols` property (e.g., `--prop 'rows=Date:month'`). OfficeCLI automatically generates the appropriate `<fieldGroup>` XML structure for monthly aggregation without requiring additional flags.

### What values are supported for the showDataAs parameter?

OfficeCLI supports `percent_of_row`, `percent_of_col`, `percent_of_total`, and `running_total` as valid showDataAs tokens. These map directly to Excel's native pivot table calculation types and are defined in the xlsx skill schema.

### Can I create multiple pivot tables from the same source range?

Yes. The `source` property can reference the same worksheet range across multiple `officecli add` commands. Each invocation generates an independent pivot table definition while sharing the underlying pivot cache for efficiency.

### Where is the pivot table XML generated in the OfficeCLI source?

The XML generation logic resides within the xlsx skill implementation. The CLI constructs `<dataField>` elements for values, `<fieldGroup>` elements for date hierarchies, and `<showDataAs>` elements for percentage calculations, then packages these into the workbook's `xl/pivotTables/` and `xl/pivotCache/` directories according to the OOXML specification.