Native calculated Pivot fields and cache-owned items

Calculate from context sums

For a local calculated cache field, native preview first sums every referenced database field in the requested leaf, subtotal or grand-total context and then evaluates the formula; dependencies can remain absent from the displayed data fields

For example, two records with Amount/Units values of 10/1 and 30/9 produce a native calculated field result of 40/10 = 4, and a grand total evaluates its own dependency sums rather than adding displayed ratios

Calculated items evaluate member references in each complete leaf context, including the other row and column axes; subtotals and grand totals include the resulting member contributions, so a ratio item can contribute a sum of its leaf ratios

A manually hidden member remains available when another calculated item references it, while its own contribution stays outside displayed totals; for A=40 and B=60, hiding A still gives Combined=A+B a value of 100 and includes Combined in the visible grand total

When calculated fields and items coexist, item contributions first update their referenced database-field sums, then the calculated field evaluates those updated context sums

Calculated items can belong to several ungrouped fields; the retained cache definition order determines which field formula writes an intersection last, so moving a definition can change both that intersection and the displayed totals

An unresolved calculated-member dependency evaluates when it is referenced, including a dependency defined later in that order; a member already calculated in an earlier step retains its current intersection value when another formula references it

Native field sums ignore Boolean and text source values, retain numeric and date values, and propagate typed source errors; lazy IF and IFERROR can handle an error without evaluating an unused branch

Cache ownership and binding

Public declaration	Contract
`TXLSPivotCacheCalculatedItem`	A cache-owned native calculated-member definition; obtain it through the owning cache and keep the cache alive while using the borrowed object
`TXLSPivotCacheCalculatedItem.FieldIndex: Integer`	Read-only zero-based cache-field identity
`TXLSPivotCacheCalculatedItem.ItemIndex: Integer`	Read-only zero-based shared-member identity within FieldIndex
`TXLSPivotCacheCalculatedItem.Formula: WideString`	Read/write native member formula; setting it changes the model, while preview or explicit binding validates syntax and dependency cycles before calculating
`TXLSPivotCacheCalculatedItem.Supported: Boolean`	Read-only availability of the parsed area identity, rather than a guarantee that every formula or view combination can be evaluated
`TXLSPivotCacheCalculatedItem.RawXml: WideString`	Read-only preserved XML for an unsupported calculated-item definition; known typed definitions use an empty string
`TXLSPivotCache.AddCalculatedItem(AFieldIndex, AItemIndex: Integer; const AFormula: WideString): TXLSPivotCacheCalculatedItem`	Creates a supported typed definition for an existing member, rejecting invalid identities, an empty formula or duplicate target identity; the returned object belongs to the cache
`TXLSPivotCache.FindCalculatedItem(AFieldIndex, AItemIndex: Integer): TXLSPivotCacheCalculatedItem`	Returns the borrowed matching definition or nil
`TXLSPivotCache.CalculatedItemCount: Integer`	Read-only number of retained supported or unsupported definitions
`TXLSPivotCache.CalculatedItems[Index: Integer]: TXLSPivotCacheCalculatedItem`	Read-only zero-based borrowed access to the definition list
`TXLSPivotCache.ClearCalculatedItems`	Frees the owned definitions and invalidates borrowed handles; it does not remove shared members or establish cache query completeness
`TXLSPivotCache.MoveCalculatedItem(AIndex, ANewIndex: Integer)`	Moves an existing definition between zero-based positions in the native solve order, preserving its borrowed object identity; invalid positions raise EArgumentOutOfRangeException without moving definitions
`TXLSPivotCache._AddUnsupportedCalculatedItem(const ARawXml: WideString)`	Parser-oriented preservation hook that retains unknown XML and marks the cache query model incomplete
`TXLSPivotCache.Assign(Source: TXLSPivotCache)`	Deep-copies definitions, fields, shared members, record indices and date-system context; source handles remain independent, and assigning the same cache to itself is safe
`TXLSPivotTable.BindCalculatedItemsToCache`	Validates and binds view-authored calculated items on a detached cache, then appends the new shared members and definitions to the live cache; failures leave member identities and definitions unchanged, while existing field and member handles remain valid

A view-authored item created with TXLSPivotField.AddCalculatedItem needs explicit BindCalculatedItemsToCache before native materialization or typed native reconstruction; importing a supported native cache definition already supplies that ownership

Cache binding affects every view sharing that cache, so call it as an authoring operation and retain the borrowed handles only until the corresponding list is cleared, assigned or destroyed

Member filters and display transformations

Caption and value comparisons on a calculated-item field select displayed members after the member formulas run; filtered source members remain available to referenced formulas, and visible subtotals and grand totals exclude those members' direct contributions

A value comparison can select a calculated measure; each candidate member evaluates that measure from its context dependency sums, rather than adding source-record ratios or treating the formula as a cache record column

Calculated-item results also support the modeled ShowDataAs transformations, including percentages, index, differences, running totals, percentages of running totals and ranks; hidden members stay outside visible running and rank sequences, while formula references retain their original values

Public declaration	Contract
`TXLSPivotFilter.DataFieldIsMeasureIndex: Boolean`	Selects the identity domain of DataField; False retains the existing zero-based cache-field identity, while True identifies a zero-based displayed data-measure position and is set when native iMeasureFld is imported
`TXLSPivotFilter.SourceOrder: Integer`	Read/write zero-based global order retained from the native filters element, defaulting to -1 when absent; local calculation and typed writing use nonnegative EvalOrder first, then SourceOrder, then stable field/list order

Typed native writing converts a cache-field identity to its displayed measure position; a cache field used by multiple data measures needs an explicit measure position, because its aggregation and display identity would otherwise be ambiguous

Excel can save active filters with evalOrder=-1 while preserving their evaluation order through the filters element sequence; importing, cloning and typed reconstruction retain that sequence rather than treating those filters as inactive

Changing a filter definition or its retained source position on an imported preserved native view requires typed reconstruction; ordinary native adoption validates preserved filter identity and rejects unsupported edits before writing report cells

Preview, queries and date systems

function TXLSPivotTable.MakePreview(AWriter: TlxPivotResultWriter;
  ALayoutWriter: TlxPivotLayoutWriter;
  AUseNativeGrandTotals: Boolean = False;
  ABudget: TlxPivotQueryBudget = nil;
  ADateSystem: TXLSPivotDateSystem = xlpdsCache): Integer;

function TXLSPivotTable.GetDataValue(const ADataField: WideString;
  const AConstraints: TXLSPivotDataConstraints;
  ABudget: TlxPivotQueryBudget; out AValue: Variant;
  ADateSystem: TXLSPivotDateSystem = xlpdsCache): Boolean;

MakePreview with AUseNativeGrandTotals=True uses a detached table and cache, including exact native sums and calculated-item identities; ordinary Make and default MakePreview retain their existing record-based calculation behavior

GetDataValue uses native context sums when the cache has calculated fields or items, and GETPIVOTDATA shares that query path; neither operation refreshes source data, recalculates inspected worksheet formulas or writes report cells

Public declaration	Contract
`TXLSPivotCache.Date1904: Boolean`	Read/write source date-system context for standalone native calculations; it defaults to False and is bound during Classic and XLSX import and worksheet cache creation, and during XLSX cache copying
`TXLSPivotDateSystem`	Typed date-system selection for detached native previews and queries
`xlpdsCache`	Use the cache's retained Date1904 context without changing it
`xlpds1900`	Project the 1900 date system into the detached calculation
`xlpds1904`	Project the 1904 date system into the detached calculation
`TryXLSXMakeNativePivotLayout(...; ADateSystem: TXLSPivotDateSystem = xlpdsCache): Boolean`	The lower-level native layout helper accepts the same detached date-system selection after its ResultXml and Reason output parameters
`TXLSGetDate1904 = function: Boolean of object`	Calculator metadata callback that reads the current owning workbook date system
`TXLSCalculator.OnGetDate1904: TXLSGetDate1904`	Optional borrowed callback used by GETPIVOTDATA; nil uses cache context, while workbook evaluators and their read-only views supply the current workbook setting

Standalone callers must supply the correct cache Date1904 context or an explicit date-system selection; workbook-owned native materialization and formula queries project the current workbook setting without modifying the live cache

Date source values remain absolute TDateTime values in the cache, while native numeric formulas use Excel date serials; the 1904 system subtracts 1462 days per date value, and the 1900 conversion handles the representable dates before March 1900

Saved native output ownership records its date-system context and compares styled date values against their serialized numeric payload; saving, reopening or changing the workbook date base preserves legitimate ownership, while changed date, Boolean, error and text payloads still reject overwrite

Budgets and unsupported input

The optional TlxPivotQueryBudget callback receives work increments and a current memory estimate, including the proposed detached snapshot and formula storage; False aborts with EXLSPivotQueryBudget and callback exceptions propagate

Record widths and database-member identities are checked before cloning; formula compilation and evaluation charge work, and the calculation retains bounded syntax depth, formula text, calculated-member domains and accumulator storage

Calculated-item planning limits the combined member domains, caption storage and field plans before allocation; native member evaluation and late aggregate-filter scans also reject proposed work above their fixed limits before entering those scans, and budget cancellation leaves live member masks unchanged

The formula parser supports numeric constants, field names, quoted names, field-qualified member names, arithmetic and comparisons, percent, and ABS, SQRT, POWER, ROUND, MIN, MAX, SUM, IF, IFERROR, AND, OR and PI; unknown functions, cell references, arrays and unbound names reject rather than supplying guessed values

Native calculated fields require SUM data measures and cannot be row, column or page axes; calculated items require retained string captions in ungrouped cache fields, automatic or SUM subtotals, SUM measures and the supported single-member area identity

Unknown formula functions, nonzero area fieldPosition, unknown or malformed area metadata, ambiguous filter measure identities and mutually active basic and extended display transformations reject explicitly; these restrictions do not turn an unsupported definition into an ordinary item

Late aggregate filtering of calculated members supports modeled value comparisons; legacy count, percentage and sum filter variants and unmodeled Top n metadata require a supported native selection model and reject rather than substituting source-record comparisons

Imported raw parts retain unsupported future metadata for ordinary roundtrip preservation; local calculation, typed reconstruction and semantic feature extraction require an available model and do not refresh source data to manufacture missing cache records

Semantic Pivot cache definitions include calculated-item identity and formula, date-system context and detached bounded payloads; unavailable native extensions remain an explicit semantic error