Transactional Pivot cache refresh

Version 2.384.92 refreshes local rectangular, defined-name and table Pivot sources without altering existing field objects, shared-item objects or logical member indices; every PivotTable view sharing the cache carries on referring 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 whenever source formulas need evaluation

A rectangular source uses an existing local worksheet plus a header row; SourceFirstRow, SourceFirstCol, SourceLastRow and SourceLastCol take inclusive 1-based coordinates, and the first row provides the 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 materialisation work out its current geometry on every call whilst preserving the source identity held in the cache

SourceRangeSheet provides the worksheet context for a local defined name; that scope takes precedence over a workbook name of the same spelling, and otherwise resolution uses the workbook scope without settling on an unrelated local name

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

A table must hold on to its header row; records follow its current extent and exclude an actual totals row, whilst collision checks protect the whole table, totals included; a header-only table supplies zero records

Physical totals membership follows the stored totalsRowCount, where an absent value means zero; a totals UI hint on its own does not remove a data row, and saving writes out the actual membership explicitly

For an authored totals row, supply valid column totals metadata and worksheet content via ColumnTotalsRowLabels, ColumnTotalsRowFunctions and ColumnTotalsRowFormulas; setting TotalsRowShown=True creates no totals formulas and coerces no cell values

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

Headers match existing database fields by name, ignoring case; reordering source columns preserves the logical field indices, whilst duplicate, renamed, missing or extra source fields are rejected; empty headers fall back to the existing FieldN scheme

Empty source cells stay empty and are never added to the worksheet cell collection; header-only sources yield zero records whilst keeping historical members

Stable members and shared views

Existing shared items keep their indices and object identities, and new typed values join 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

Every view sharing one cache sees the refreshed records; absent historical members remain in the cache domain but do not create aggregate rows on their own, because TXLSPivotTable.Make builds results from the current records

Historical members are kept no matter what MissingItemsLimit says; this refresh path neither purges missing members nor enforces a retained-member limit, and it never renumbers selection indices

Numeric, date, Boolean, text, blank and representable error values keep 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 the refreshed view member lists ahead of trailing subtotal items whilst 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 pick the same member after reopening

Calculated and grouped fields

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

XLSX cache records store database fields alone; saving and reopening preserve source-column alignment even where calculated fields sit between database fields

Numeric and date grouping on a source field can take new members; explicit discrete grouping accepts reordered existing members but refuses a new member lacking an explicit mapping, preserving the whole of the 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 are refused transactionally, whilst external and OLAP sources stay outside the local-source contract; see grouped-cache boundaries

Atomic updates and callback refresh

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

Success swaps in new record and lookup buffers, disables raw cache replay, updates RefreshedDate and sets RefreshOnLoad=False; saving then works from the refreshed typed cache rather than replaying its earlier raw representation

Standalone or Classic-backed code can reach the same staging implementation through TXLSPivotCache.RefreshFromSource; its reader is given 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 needs a concrete local rectangle already 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 stay responsible for resolving that identity correctly

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