Native calculated Pivot fields and cache-owned items

Calculate from context sums

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

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

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

A manually hidden member stays available when another calculated item references it, whilst its own contribution sits outside the displayed totals; with A=40 and B=60, hiding A still leaves Combined=A+B at 100 and keeps Combined in the visible grand total

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

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

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

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

Cache ownership and binding

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

Cache binding touches every view sharing that cache, so treat it as an authoring operation and hold 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 pick the displayed members after the member formulas have run; filtered source members stay available to referenced formulas, and visible subtotals and grand totals leave out those members' direct contributions

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

Calculated-item results also support the modelled ShowDataAs transformations, taking in percentages, index, differences, running totals, percentages of running totals and ranks; hidden members stay outside the visible running and rank sequences, whilst formula references keep their original values

Typed native writing converts a cache-field identity into its displayed measure position; a cache field feeding several data measures needs an explicit measure position, otherwise its aggregation and display identity would be ambiguous

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

Changing a filter definition, or its retained source position, on an imported preserved native view calls for typed reconstruction; ordinary native adoption validates the preserved filter identity and refuses 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, exact native sums and calculated-item identities included; ordinary Make and default MakePreview keep their existing record-based calculation behaviour

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

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

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

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

Budgets and unsupported input

The optional TlxPivotQueryBudget callback receives work increments and a running memory estimate, covering 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 keeps bounded syntax depth, formula text, calculated-member domains and accumulator storage

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

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

Native calculated fields call for SUM data measures and cannot serve as row, column or page axes; calculated items call for 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 are refused explicitly; these restrictions do not turn an unsupported definition into an ordinary item

Late aggregate filtering of calculated members supports modelled value comparisons; the legacy count, percentage and sum filter variants and unmodelled Top n metadata call for a supported native selection model and are refused rather than substituting source-record comparisons

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

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