HotXLS sets Excel pivot table "Show Values As" modes from Delphi through two properties on TXLSPivotDataField: ShowDataAs for the classic modes such as running total, difference and percent of row, and ExtendedShowDataAs for rank, percent of parent and running total percent. TXLSXWorkbook.MaterializeNativePivotTable then writes the computed grid the way Excel would. The two properties are mutually exclusive, and the extended ones depend on an extension URI that Excel matches case-sensitively
That last detail is the reason this post exists. A pivot table with a rank column looks like a small thing to generate, and for the first open it usually is. The trouble starts when a user clicks Refresh in Excel and the rank column turns back into a plain sum without any warning. Getting Show Values As right means getting two XML vocabularies right, plus a handful of numeric rules Excel never documents in the UI: how ties rank, what a cumulative percentage of an average means, and which empty cell becomes blank versus an error
Why are there two properties for one Excel menu?
Excel shows one "Show Values As" menu, but the file stores it in two different places, so HotXLS exposes two properties. The original modes from Excel 2007 live in the showDataAs attribute of the dataField element inside pivotTableDefinition, using the ECMA-376 ST_ShowDataAs enumeration. The modes Excel 2010 added, rank and the parent and cumulative percentages, did not fit that enumeration, so they went into an x14:dataField element with a pivotShowAs attribute, wrapped in an extLst extension block on the same data field
TXLSPivotShowDataAs mirrors the first group: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal and xlpsdaIndex. TXLSPivotExtendedShowDataAs covers the second: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending and xlpesdaRankDescending, with xlpesdaNone as the neutral value. A data field uses one group or the other, never both. If you leave ShowDataAs at a non-normal value and also set an extended mode, MaterializeNativePivotTable returns -1 and leaves the existing output exactly as it was, cells and pivot bounds included
BaseField and BaseItem carry the comparison target. BaseField is a zero-based index into the pivot table's field list, not into the source columns' header names, and it drives comparisons, running totals, percent of a selected parent and ranks. BaseItem is a zero-based item position inside that field, or one of the constants xlPivotBaseItemPrevious and xlPivotBaseItemNext for "compared with the neighbor". Parent row and parent column percentages ignore BaseField entirely; they pick the innermost field on the matching axis
Building a ranked pivot in Delphi
The shortest working path is to build the pivot, set the display mode on the data field, and materialize it before saving. The example below ranks regional amounts per category in descending order, so the biggest amount in each row is rank 1
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Measure: TXLSPivotDataField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Category';
Data.Cells[1, 2].Value := 'Region';
Data.Cells[1, 3].Value := 'Amount';
// ... fill rows 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // pivot field 0
Pivot.AddColumnField('Region'); // pivot field 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // basic mode stays off
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rank across Region
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');
Book.SaveAs('region-rank.xlsx');
finally
Book.Free;
end;
end;Two habits pay off here. First, set ShowDataAs := xlpsdaNormal explicitly when you choose an extended mode, even on a fresh field, because the same code often ends up reused against an imported pivot whose data field already carries a basic mode. Second, check the return value. A rejected materialization is atomic, which is good, but it also means a missed check ships a file with the previous grid in it
BaseField survives saving even if you never call MaterializeNativePivotTable. HotXLS writes the baseField attribute for percent of parent, running total percent and both rank modes as soon as the extended mode is set, so a workbook saved straight after configuration still tells Excel which field to rank across when it refreshes
What goes wrong with the extension URI?
The extension block that carries pivotShowAs is identified by the URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, and Excel compares that string case-sensitively during refresh. Note the lowercase e in 4e99. GUIDs are normally case-insensitive and many XML writers uppercase them, which is exactly the failure mode: a file with 4E99 opens in Excel with every cached cell correct, because Excel just paints the stored values. After RefreshTable, though, Excel no longer recognizes the extension, drops the extended mode, and recomputes the column as an ordinary sum. No repair prompt, no error, just a rank column that quietly became totals
HotXLS writes the literal URI as Excel expects it, so this only bites you if something downstream rewrites the package, for example a post-processing step that normalizes GUID casing or rebuilds extLst elements. The test that catches it is cheap: open the generated file in Excel, refresh the pivot, and confirm the extended column still shows ranks or percentages rather than raw aggregates. A test that only reads the cached cells will pass on a broken file every time
How does Excel rank ties and order running totals?
Excel's pivot rank is a dense rank, and HotXLS reproduces it. With descending amounts of 30, 30 and 20, the result is 1, 1 and 2, not the competition-style 1, 1 and 3 you get from RANK.EQ on a worksheet. A tie counts as one distinct preceding value. If a report needs competition ranking, compute it in the source data or in a worksheet formula next to the pivot, because no pivot display mode produces it
Running totals, running total percentages and previous or next comparisons follow the order members actually appear in, not the order of the field's item list. That distinction matters once a caller changes the sort after the cache was built
// Rows sorted descending: the running total accumulates in display order
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % difference from...
Measure.BaseField := 0; // ...the Category field
Measure.BaseItem := xlPivotBaseItemPrevious; // ...previous visible member
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');After SortType changes to xlpfstDescending, "previous" means the member displayed above, and the running total starts from the top of the sorted list. An implementation that walks the stored item list instead produces numbers that look plausible and disagree with Excel the moment anyone refreshes. Edge members need care as well: at the first member there is no previous neighbor, and only the percent-of mode compares the edge member with itself; the difference modes leave that cell to Excel's own edge behavior rather than inventing a zero
Why can a cumulative percentage exceed 100 percent?
A running total percentage over averages can legitimately go past 100 percent, because Excel accumulates the displayed member aggregates and divides by an aggregate that is not their sum. For sums the two are the same thing, so the last member lands on exactly 100 percent. For averages, adding up three category averages and dividing by the overall average is not a fraction of anything, and the final row can land well above 100 percent. HotXLS matches Excel here on purpose rather than clamping the value, because a clamped number would disagree with the refreshed file
The divisor also depends on where the base field sits. When the base field is the innermost field on its axis, the divisor is the original aggregate for that axis position. When the base field is an outer field in a nested layout, the divisor is the sum across the matching members of the remaining axis. Both positions were checked independently against Excel output, because a rule that works for one nesting level and silently fails on the other is the classic pivot bug
Simple percentages behave differently from the cumulative one. Percent of row, column and grand total divide by the original aggregate at the relevant scope, including for nonadditive aggregations such as averages, so a percent of total on averages is the cell average divided by the overall average, not a share of a summed pool
Blanks, #NULL! and #DIV/0! in comparison modes
Percent-of-base comparisons distinguish three empty situations, and each produces a different cell. When the numerator intersection has no source rows, the cell shows #NULL!. When the base item has no data at that intersection, the cell is blank. When the base item exists and its aggregate is zero, the cell shows #DIV/0!. Code that reads the materialized grid should test with VarIsEmpty and VarIsError rather than assuming every value cell holds a number
The simple percentages are more forgiving: an absent numerator counts as zero there. Parent row and parent column percentages add one more case, since a pivot with no field on the corresponding axis has no parent to divide by, so those results are blank. Grand totals follow each mode's own semantics too, which includes blank totals along the axis a comparison or running total walks; a total row full of blanks under a running total is correct, not a materialization failure
Switching modes on an imported pivot
Changing the display mode of a pivot that came from an existing workbook works the same way, with one extra flag. Pass True as AAdoptExisting so MaterializeNativePivotTable may take over the cells Excel already wrote for that pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // clear the basic mode
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // no BaseField needed
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');When the mode changes, HotXLS removes obsolete showDataAs, pivotShowAs and baseItem attributes from the staged definition, so the saved file never carries a stale basic mode next to a new extended one. The same staging step keeps the compact and outline flags on data and inactive fields in step with the report layout, which matters because Excel derives refreshed data captions from those flags and not only from the row and column fields. Invalid enum values are rejected as well: casting a byte of 255 into either enum can sign-extend to -1 in Delphi, so HotXLS checks both the lower and the upper bound and returns -1 without touching the grid
If you also add calculated fields or calculated items to the same report, read building and refreshing pivot tables with calculated fields in HotXLS first, and for the field-level XML rules that keep Excel from repairing the file, see XLSX pivot field schema validity in Delphi
FAQ
Can a HotXLS data field use ShowDataAs and ExtendedShowDataAs together?
No. TXLSPivotDataField.ShowDataAs and ExtendedShowDataAs are mutually exclusive; with both set, MaterializeNativePivotTable returns -1 and leaves the previous output unchanged
Why did my pivot rank turn into a sum after refresh in Excel?
The extension URI was probably rewritten with different casing. Excel matches {E15A36E0-9728-4e99-A89B-3F7291B0FE68} case-sensitively and falls back to the plain aggregation after RefreshTable when it does not match
Does the HotXLS pivot rank skip numbers after a tie?
No. HotXLS follows Excel's dense pivot rank, so 30, 30 and 20 rank as 1, 1 and 2
Show Values As support, native pivot materialization and the rest of the XLSX and XLS feature set ship in the HotXLS Delphi spreadsheet component for Delphi and C++Builder