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 the complete native compact, tabular or outline report grid together with its location, rowItems, colItems and data-axis metadata before worksheet values and output ownership are committed together; it returns 1 on success or -1 with a diagnostic for unsupported state or a collision

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

Existing cell objects and formatting stay available, whilst shrinking or moving the report clears just the unchanged previously owned values; edited output, source overlap, merges, formulas, arrays and other reports are refused before mutation

Imported scalar output calls for explicit adoption with AAdoptExisting=True; subsequent updates rely on persisted individual-cell ownership, saving and reopening included

The operation reads the current cache records and neither refreshes source data nor recalculates formulas; Cache.RefreshOnLoad=False is fine because the worksheet now holds actual staged native results

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

Supported SUM calculated cache fields and cache-owned calculated items follow the context-sum contract set out in Native calculated Pivot fields and cache-owned items; custom member captions and unsupported layout combinations are refused explicitly, whilst supported generated raw layout keeps 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 living in lxPivotXml

Its writer is handed the whole report rectangle in row-major order, blank cells included, with zero-based offsets from the table anchor; XML and grid preflight finish before the first callback, yet caller-owned writes cannot be rolled back should a callback raise

The workbook facade provides a detached capture writer for transactional cell updates; the combined staged preview and native Variant grids are capped at 4194304 cells

The native preview path can ask for native grand-total orientation through TXLSPivotTable.MakePreview's optional AUseNativeGrandTotals parameter; existing single-writer and layout-writer calls keep their previous behaviour

Layout and repeated labels

Set Compact and Outline on the report and each row field before materialisation; tabular fields carry both flags as False, outline fields use Compact=False with Outline=True, and compact fields carry 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 does nothing for a field whose compact and outline flags are both enabled

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

Default tabular subtotals show up beneath their group; outline SubtotalTop selects the top or bottom position, whilst measures on rows use separate aggregate rows beneath the group and keep each measure's caption

Native cached output leaves absent row and column intersections blank and preserves a genuine numeric zero; ordinary Make and previews without the native option keep their previous empty-result behaviour

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

Imported repeated-label extensions accept namespace aliases, declarations on the extension element itself included; unknown unrelated XML stays preserved by the native materialisation path

Grouped members, axis order and custom subtotals

Native output supports complete numeric range groups, date groups and manual member groups held in the cache's existing typed grouping metadata; generated labels keep the exact imported GroupItemLabels, Unicode and localised date captions included

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 are refused before worksheet output changes

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

Native imported Year/Month and parent/manual-member hierarchies keep their explicit axis sequence, whilst cache-field indices, view-field indices and display calculation base identities stay unchanged; manual groups honour the 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, multiple selected functions, tabular or outline fields and measures on rows included

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

An imported custom subtotal selection rules out the implicit automatic subtotal even where defaultSubtotal is absent in native XML; applications must not pair xlpsDefault with custom functions, and such an ambiguous typed request is refused before mutation

Multiple column field levels with subtotals, unsupported compact data-axis combinations, collapsed members and custom captions sit outside this native output contract; calculated fields and items have their own narrower SUM and member-identity requirements, and raw preservation implies nothing about recalculating or materialising a feature

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

Native display calculations

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

Basic modes take in difference, percent of a base item, percent difference, running total, percent of row, column or grand total and index; extended modes take in 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 work from the zero-based BaseItem position, or from xlPivotBaseItemPrevious and xlPivotBaseItemNext for adjacent visible members in the effective display order

Parent row and parent column percentages go for 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, nonadditive averages included; cumulative percentages add up displayed member aggregates and use the original axis total for an innermost base field, whilst an outer base field uses the sum across its matching remaining members, so averages can exceed 100 per cent

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

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

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

Slicer cross-filter availability

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

This read-only query evaluates the current supported non-OLAP cache records, ignoring this Slicer's own selection but 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 all count as data under the verified native contract

The query uses a read lease and a detached record preview, preserves live handles and selection and honours FormulaArrayMemoryLimit; callback failure, reentry, invalid bindings or unsupported aggregate calculated members raise before any live changes are committed

Querying availability neither refreshes caches nor recalculates formulas, performs file I/O or updates serialised 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 is handed the detached preview and a zero-based record index once supported simple and aggregate filtering has run; EvaluationSourceTable keeps 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 stay active, and a non-nil replacement callback calls for 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 takes out unused members after complete validation and remapping of supported linked views and Slicers

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

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

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

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

Removing an active page or comparison member, a whole selected Slicer subset or a previously nonempty effective intersection is refused atomically; primitive member attributes that would be lost during typed rebuilding are likewise refused

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

Purge neither refreshes source data nor rewrites worksheet report cells, invokes record callbacks or applies MissingItemsLimit metadata automatically; refresh stays append-only unless this operation is explicitly requested

Advanced purge coordination

TXLSPivotCache._PrepareMissingItemPurge gives back an opaque TXLSPivotCachePurgePlan; applications would normally use the workbook facade so that every linked view and Slicer is brought in

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 gives the total removal count

Validate checks the original schema, records, field/item ownership identities and staged view selections; Commit validates once more and transfers the 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 the 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 that stage back so the plan can be reused; unsupported mutations after preparation are refused before commit