Pivot layout for AI models

"Revenue by region and year, only the top five regions, as a share of the total." That sentence describes a pivot layout: two fields, one measure with an aggregate and a display mode, and a sort by value with a cut-off. A language model can write that layout if it knows exactly what this pivot can do. Three functions give it that:

Function	What it does
`getPivotAiSchema(controller, options?)`	A JSON Schema of the layout, limited to what the fields allow
`getPivotAiState(controller, options?)`	The current layout in that shape, for the model to start from
`applyPivotAiState(controller, answer, options?)`	Checks the model's answer and applies the valid part

There is no model inside the grid, and the grid sends nothing anywhere. You use your own model (any model that supports structured output, or any that can return JSON) and call it from your own code. Import the functions from @kanunilabs/pivotgrid-react-enterprise or @kanunilabs/pivotgrid-enterprise. They work only on an Enterprise grid's controller. A Community grid's controller makes them throw an error that names the feature.

Getting the controller

// React
const ref = useRef<PivotGridRef>(null);
<EnterprisePivotGrid ref={ref} licenseKey={key} data={rows} initialFields={fields} />;
const controller = ref.current!.getController();

// JavaScript
const grid = createEnterprisePivotGrid(host, { licenseKey: key, data: rows, initialFields: fields });
const controller = grid.controller;

A round trip

import { getPivotAiSchema, getPivotAiState, applyPivotAiState } from '@kanunilabs/pivotgrid-react-enterprise';

const schema = getPivotAiSchema(controller);
const current = getPivotAiState(controller);

// On your server: ask your own model, with the schema as its output format.
const answer = await askModel({
  instructions: 'You change the layout of a pivot table. Start from the current layout.',
  input: `Current layout: ${JSON.stringify(current)}\nRequest: ${userText}`,
  outputSchema: schema,
});

const { applied, dropped } = applyPivotAiState(controller, answer);

askModel stands for your own call to your model's API. answer can be the parsed object or the JSON text the API returns.

What the schema covers

Part	Properties	Notes
`fields`	`rows`, `columns`, `values`	Which fields go where. Each value has an aggregate (`sum`, `avg`, …) and a display mode (`absolute`, `percentOfTotal`, `runningTotal`, …)
`filter`	`filters`	Keep (`include`) or drop (`exclude`) listed values of a field. `[]` removes every filter
`sort`	`sort`	Per row or column field: by the field itself, or by a measure (`byValueField`), with an optional `top` cut-off
`totals`	`totals`	Grand total row and column, subtotals, and `tabular` or `tree` row headers

The schema only offers what the pivot accepts. A text field gets count and distinct but never sum. A summary-level calculated field can be a measure but not a row. A field you hid from the field chooser (visible: false) is not offered at all. Every object lists all of its properties as required and allows no others. That is the subset the strictest structured-output modes accept. The schema is draft 2020-12.

"Top five regions by revenue" is a sort with byValueField: 'revenue' and top: 5. The measure's grand total sets the order, which is what "biggest" usually means.

Telling the model more

const options = {
  exclude: ['totals'],                         // parts the model may not change
  fields: {
    revenue: { description: 'Net revenue in EUR' },
    city: { includeValues: true },             // list its values in the schema
    customer: { exclude: true },               // never mention it; it stays where it is
  },
  maxValues: 50,                               // per field, default 100
};
getPivotAiSchema(controller, options);
applyPivotAiState(controller, answer, options); // same options on the way back

Values are listed only when you ask. includeValues sends a field's distinct values to whatever model you use, so turn it on only where you are allowed to share them. Without the list a model has to guess the spelling. Either way, applyPivotAiState checks every filter value against the data, in the browser. A value that does not exist, such as "Izmit" instead of "Izmir", is reported in dropped and not applied. Without that check, the pivot would quietly filter down to nothing.

What `applyPivotAiState` does with an answer

Keeps the valid part and lists everything it left out in dropped, in words: values city: sum does not suit a string field, sort by region: top needs byValueField, kept every value.
Never throws on a bad answer. Text that is not JSON, or JSON that is not an object, returns applied: [] with the reason.
Places fields in one commit. A field the answer no longer uses goes back to the field chooser. A field in the filter area stays there, and so does a field you excluded.
Changes only the parts the answer mentions. If an answer has rows but no columns, the column fields stay where they are.

What it does not do

No model, prompt or chat box. Those belong to your application.
No row data. The model sees fields, and a field's values only when you allow it. It never sees rows, so it cannot answer questions about the numbers themselves.
Not the prefilter or calculated fields. It does not touch the row-level prefilter, and it does not create calculated fields. It can place existing ones.