Transactional Pivot cache refresh

Version 2.384.92 refreshes local rectangular, defined-name, and table Pivot sources without changing existing field objects, shared-item objects, or logical member indices; all PivotTable views sharing the cache continue to refer to the same members

Workbook refresh

Workbook.Recalculate;
if Workbook.RefreshPivotCache(Pivot.CacheId) <> 1 then
  raise Exception.Create('Pivot cache refresh failed');

TXLSXWorkbook.RefreshPivotCache returns 1 after a successful refresh and -1 when the cache, source, schema, or source values cannot be refreshed; it reads current cell values, so recalculate the workbook first when source formulas need evaluation

A rectangular source uses an existing local worksheet and a header row; SourceFirstRow, SourceFirstCol, SourceLastRow, and SourceLastCol use inclusive 1-based coordinates, and the first row supplies field names

Defined-name and table sources

When SourceIsNamedRange=True, SourceName identifies a defined name or a unique table name or display name; refresh and materialization resolve its current geometry on every call while preserving the source identity stored in the cache

SourceRangeSheet supplies the worksheet context for a local defined name; that scope takes precedence over a workbook name with the same spelling, and otherwise resolution uses the workbook scope without choosing an unrelated local name

A defined name must contain one explicitly worksheet-qualified absolute rectangle; quoted and Unicode worksheet names are supported, while relative references, unions, aliases, formulas, external references, and ambiguous name/table collisions reject before mutation

A table must retain a header row; records follow its current extent and exclude an actual totals row, while collision checks protect the entire table including its totals; a header-only table supplies zero records

Physical totals membership follows the stored totalsRowCount, whose absent value means zero; a totals UI hint alone does not remove a data row, and saving writes the actual membership explicitly

For an authored totals row, provide valid column totals metadata and worksheet content through ColumnTotalsRowLabels, ColumnTotalsRowFunctions, and ColumnTotalsRowFormulas; setting TotalsRowShown=True does not create totals formulas or coerce cell values

Worksheet moves retain local name scope, and worksheet renames update supported local references and Pivot source context while preserving unrelated external references and opaque imported cache XML; defined-name saving removes an optional leading formula equals sign without changing the in-memory formula

Headers match existing database fields by name without case sensitivity; reordering source columns preserves logical field indices, while duplicate, renamed, missing, or extra source fields are rejected; empty headers use the existing FieldN fallback

Empty source cells remain empty without being added to the worksheet cell collection; header-only sources produce zero records while retaining historical members

Stable members and shared views

Existing shared items keep their indices and object identities, and new typed values append to the domain; reordering, removing, and later reintroducing source members preserves hidden items, page selections, base-item references, and explicit group mappings that already identify those members

All views sharing one cache see the refreshed records; absent historical members remain in the cache domain but do not create aggregate rows by themselves, because TXLSPivotTable.Make builds results from current records

Historical members are retained regardless of MissingItemsLimit; this refresh path does not purge missing members, enforce a retained-member limit, or renumber selection indices

Numeric, date, Boolean, text, blank, and representable error values retain their types through refresh and XLSX saving; shared-item content flags and representable bounds are recomputed from the retained and newly appended domain

Saving completes refreshed view member lists before trailing subtotal items while preserving existing order, hidden flags, and imported layout extensions; page selections map between logical cache members and the positions stored in each view, so reordered member lists still select the same member after reopening

Calculated and grouped fields

Calculated fields do not consume source columns; their names, formulas, and shared-item domains remain available, while each refreshed record starts with an empty placeholder for those fields; Make evaluates supported calculated formulas from the current source values

XLSX cache records store database fields only; saving and reopening preserve source-column alignment even when calculated fields occur between database fields

Numeric and date grouping on a source field can accept new members; explicit discrete grouping accepts reordered existing members but rejects a new member that has no explicit mapping, preserving the entire previous cache

Version 2.384.94 reconstructs supported fixed derived grouped fields from validated base/parent links; automatic domain expansion, unmapped discrete members and member-property fields reject transactionally, while external and OLAP sources remain outside the local-source contract; see grouped-cache boundaries

Atomic updates and callback refresh

Source validation, reading, type conversion, record allocation, and member lookup finish in detached staging data; failure leaves live field and item handles, records, metadata, raw replay state, and all shared views intact

Success replaces record and lookup buffers, disables raw cache replay, updates RefreshedDate, and sets RefreshOnLoad=False; saving uses the refreshed typed cache rather than replaying its previous raw representation

Standalone or Classic-backed code can use the same staging implementation through TXLSPivotCache.RefreshFromSource; its reader receives 1-based source coordinates and returns a Variant, and validation or reader failures raise an exception

TlxPivotSourceCellValue = function(ARow, ACol: Integer): Variant of object;

procedure TXLSPivotCache.RefreshFromSource(AValueReader: TlxPivotSourceCellValue);
procedure TXLSPivotCache.RefreshFromSource(AValueReader: TlxPivotSourceCellValue;
  AFirstRow, AFirstCol, ALastRow, ALastCol: Integer);

The one-argument overload requires a concrete local rectangle in the cache; the explicit-bounds overload reads a caller-resolved local rectangle without changing the cache's named or table source identity, and callers remain responsible for resolving that identity correctly

Cache refresh updates cache data; applications can call Make with their own result writer or MaterializePivotTable for guarded worksheet output; typed non-OLAP Pivot Slicer selection is available for supported imported bindings, while complete native layout remains separate work