Native Pivot output, cross-filter queries and missing members

Native cached worksheet output

function TXLSXWorkbook.MaterializeNativePivotTable(
  ATable: TXLSPivotTable): Integer;
function TXLSXWorkbook.MaterializeNativePivotTable(
  ATable: TXLSPivotTable; AAdoptExisting: Boolean): Integer;

The operation stages both the complete native compact, tabular or outline report grid and its location, rowItems, colItems and data-axis metadata before committing worksheet values and output ownership together; it returns 1 on success or -1 with a diagnostic on unsupported state or a collision

Supported nonempty local views retain current filters, exact cache-member identities, ordinary hierarchies, supported grouped domains, default or custom subtotals and either supported data axis; the native grid uses original aggregate values rather than recombining displayed averages or other nonadditive results

Existing cell objects and formatting remain available, while shrinking or moving the report clears only unchanged previously owned values; edited output, source overlap, merges, formulas, arrays and other reports reject before mutation

Imported scalar output requires explicit adoption with AAdoptExisting=True; subsequent updates use persisted individual-cell ownership, including after saving and reopening

The operation reads current cache records and does not refresh source data or recalculate formulas; Cache.RefreshOnLoad=False is valid because the worksheet now contains actual staged native results

Caller record-filter callbacks participate in the detached preview but have no OOXML representation; Excel's explicit native refresh cannot reproduce arbitrary callback restrictions, while supported persisted manual and Slicer filters retain their wire representation

Supported SUM calculated cache fields and cache-owned calculated items use the context-sum contract described in Native calculated Pivot fields and cache-owned items; custom member captions and unsupported layout combinations reject explicitly, while supported generated raw layout retains unrelated XML and current member masks

TryXLSXMakeNativePivotLayout(const Xml: WideString; Table: TXLSPivotTable; AWriter: TlxPivotResultWriter; out ResultXml, Reason: WideString): Boolean is the lower-level staged layout helper in lxPivotXml

Its writer receives the entire report rectangle in row-major order, including blank cells, with zero-based offsets from the table anchor; XML and grid preflight finish before the first callback, but caller-owned writes cannot be rolled back if a callback raises

The workbook facade supplies a detached capture writer for transactional cell updates; the combined staged preview and native Variant grids are bounded to 4194304 cells

The native preview path can request native grand-total orientation through TXLSPivotTable.MakePreview's optional AUseNativeGrandTotals parameter; existing single-writer and layout-writer calls preserve their previous behavior

Layout and repeated labels

Set Compact and Outline on the report and each row field before materialization; tabular fields use both flags as False, outline fields use Compact=False and Outline=True, and compact fields use both flags as True

property TXLSPivotField.RepeatLabels: Boolean;

RepeatLabels defaults to False, copies independently with the field, and reads or writes the native x14:pivotField fillDownLabels extension; it repeats ancestor labels in tabular and outline output and has no effect on a field whose compact and outline flags are both enabled

Each separate row field receives its own physical column, while adjacent compact outline fields share a label column; multiple measures on rows follow CompactData, and multiple measures on columns retain independent data captions and grand totals

Default tabular subtotals appear below their group; outline SubtotalTop selects the top or bottom location, while measures on rows use separate aggregate rows below the group and retain each measure's caption

Native cached output leaves absent row and column intersections blank and preserves an actual numeric zero; ordinary Make and previews without the native option retain their previous empty-result behavior

Inactive and measure fields inherit the report layout in staged native metadata so Excel's explicit refresh retains the appropriate field and data captions; the operation preserves the live field settings and existing ownership checks

Imported repeated-label extensions accept namespace aliases, including declarations on the extension element itself; unknown unrelated XML remains preserved by the native materialization path

Grouped members, axis order and custom subtotals

Native output supports complete numeric range groups, date groups and manual member groups represented by the cache's existing typed grouping metadata; generated labels retain the exact imported GroupItemLabels, including Unicode and localized date captions

Grouping is evaluated on a detached cache, preserving the live cache's records, shared-item handles and group identities; invalid bounds, zero intervals, incomplete domains or unsupported group-item value types reject before worksheet output changes

The existing zero-based TXLSPivotField.Position determines the stable native row or column axis order; equal positions retain authored field order, so newly created fields with the default position retain their previous ordering

Native imported Year/Month and parent/manual-member hierarchies retain their explicit axis sequence, while cache-field indices, view-field indices and display calculation base identities remain unchanged; manual groups honor explicit Pivot item order rather than sorting group labels or cache indices

Custom row subtotals support xlpsSum, xlpsCount, xlpsAverage, xlpsMax, xlpsMin, xlpsProduct, xlpsCountNumbers, xlpsStdDev, xlpsStdDevP, xlpsVar and xlpsVarP, including multiple selected functions, tabular or outline fields and measures on rows

Each function aggregates the original source records at its own subtotal scope; its staged row metadata identifies the selected function independently, preserving Excel's explicit refresh behavior for nonadditive calculations and empty or error results

An imported custom subtotal selection excludes the implicit automatic subtotal even when defaultSubtotal is absent in native XML; applications must not combine xlpsDefault with custom functions, and such an ambiguous typed request rejects before mutation

Multiple column field levels with subtotals, unsupported compact data-axis combinations, collapsed members and custom captions remain outside this native output contract; calculated fields and items have their own narrower SUM and member-identity requirements, and raw preservation does not imply support for recalculating or materializing a feature

Saving, reopening and rematerializing retain supported grouping, axis order and subtotal identities; imported native views and independently copied typed caches have also been verified against Excel on both initial open and explicit refresh

Native display calculations

Set each ordinary data field's ShowDataAs or ExtendedShowDataAs before calling MaterializeNativePivotTable; basic and extended display modes are mutually exclusive

Basic modes include difference, percent of a base item, percent difference, running total, percent of row, column or grand total and index; extended modes include parent row, parent column and selected parent percentages, cumulative percentage and ascending or descending dense ranks

BaseField is the zero-based Pivot field index for comparisons, running totals, selected parent percentages and ranks; comparisons use the zero-based BaseItem position, or xlPivotBaseItemPrevious and xlPivotBaseItemNext for adjacent visible members in the effective display order

Parent row and parent column percentages choose the corresponding innermost axis field without using BaseField; a missing corresponding axis leaves those results blank

Simple percentages divide by the original aggregate at the relevant scope, including nonadditive averages; cumulative percentages sum displayed member aggregates and use the original axis total for an innermost base field, while an outer base field uses the sum across its matching remaining members, so averages can exceed 100 percent

Native ranks count distinct preceding aggregate values, and comparisons preserve Excel's blank and error behavior for absent intersections, missing bases, zero divisors and previous or next boundaries; simple percentages treat an absent numerator as zero

Grand totals use the selected mode's native semantics, including blank totals along a comparison or running axis; changing a display mode retains the report's transactional ownership, collision and budget checks

Saving and reopening retains the chosen basic or extended mode and base identities, and staged native metadata supports an explicit Excel refresh; ordinary Make and previews without the native option retain their existing calculation contract

Slicer cross-filter availability

property TXLSXSlicerCache.ItemHasData[Index: Integer]: Boolean;

This read-only query evaluates current supported non-OLAP cache records while ignoring this Slicer's own selection and retaining other Slicer selections, manual filters, caller predicates and applicable aggregate filters

Availability is the union across connected views; historical members without matching records have no data, blank measures have no data, and nonempty text, Boolean, zero, error and date measures count as data under the verified native contract

The query uses a read lease and detached record preview, preserves live handles and selection, and honors FormulaArrayMemoryLimit; callback failure, reentry, invalid bindings or unsupported aggregate calculated members raise without committing live changes

Querying availability does not refresh caches, recalculate formulas, perform file I/O or update serialized native no-data flags; applications should not infer native UI state from this read-only operation

TlxPivotRecordWriter = procedure(ATable: TXLSPivotTable;
  ARecordIndex: Integer) of object;
function TXLSPivotTable.MakeRecordPreview(AWriter: TlxPivotRecordWriter;
  AFilterOwner: TObject = nil; AFilter: TlxPivotRecordFilter = nil): Integer;

The advanced record callback receives the detached preview and a zero-based record index after supported simple and aggregate filtering; EvaluationSourceTable retains the original view identity and the return value is the number of emitted records

A replacement filter substitutes only the supplied owner's registered callback; other owners and the external predicate remain active, and a non-nil replacement callback requires a non-nil owner

Explicit historical-member purge

Removed := Workbook.PurgePivotCacheMissingItems(
  Pivot.CacheId, 0);
if Removed < 0 then
  raise Exception.Create('The cache member purge was rejected');

TXLSXWorkbook.PurgePivotCacheMissingItems(ACacheId: Integer; ARetainMissingItemsPerField: Integer = 0): Integer removes unused members after complete validation and remapping of supported linked views and Slicers

It returns the total removed shared-item count across all fields, zero for a successful no-op, or -1 with diagnostic 1402; current records retain every referenced member, plus at most the requested number of unused members per field chosen by greatest original shared-item index

Retained members keep their original relative order and object handles while logical indices become compact; linked field/item handles, manual hidden and detail flags, active page or comparison members and Slicer selections retain their identities

Domain flags and bounds are recomputed from the retained domain; supported imported compressed row/column and page positions are remapped explicitly, while grand-total dummy references and data pseudo-field ordinals remain unchanged

Ordinary complete local worksheet caches are supported; grouped, calculated, member-property, OLAP, external, incomplete or ambiguous caches and unsupported opaque identity metadata reject before mutation

Removing an active page or comparison member, an entire selected Slicer subset or a previously nonempty effective intersection rejects atomically; primitive member attributes that would be lost during typed rebuilding also reject

The known materialization ownership extension remains unchanged because its coordinates and typed value tokens contain no cache-member indices; unrelated opaque extensions are not assumed safe

Purge does not refresh source data, rewrite worksheet report cells, invoke record callbacks or apply MissingItemsLimit metadata automatically; refresh remains append-only unless this operation is explicitly requested

Advanced purge coordination

TXLSPivotCache._PrepareMissingItemPurge returns an opaque TXLSPivotCachePurgePlan; applications should normally use the workbook facade so every linked view and Slicer is included

MapItem and RemainingItemCount inspect cache remapping, StagePivotField registers a linked field and returns its stage index, and MapViewItemPosition inspects that field's wire position mapping; removed members map to -1 and RemovedCount reports the total removal count

Validate checks original schema, records, field/item ownership identities and staged view selections; Commit validates again and transfers prepared ownership without allocation or callbacks after the first live exchange

The plan owns detached snapshots, keeps its mutable staged cache private, and never owns original cache or view objects; keep those originals alive until the plan's Destroy releases staging, and commit each plan only once

Rejected field staging rolls back that stage so the plan can be reused; unsupported mutations after preparation reject before commit