fix(pivot-table): requery when totals and subtotals options change - #45306

SUMMARY

With non-additive metrics (for example AVG), the Pivot Table gets its totals and subtotals from rollup levels returned by the chart query (grouping_sets, see SIP-216). Which levels are requested is derived from the Show row summaries / Show rows subtotal / Show column summaries / Show columns subtotal options. Two problems followed from that:

1. The options did not requery. All four controls were renderTrigger, so toggling one re-rendered the stored result without a new query. The table drew Total rows/columns and the group collapse toggles, but the total cells were empty and no Subtotal lines appeared until a requery was forced. In that state, collapsing a group hid the whole group instead of collapsing it to its subtotal row.
2. Row subtotals depended on "Show column summaries". With metrics applied on rows, the renderer places the metric pseudo-dimension first in the row hierarchy, so the fully collapsed row level is drawn as each metric group's "Subtotal" line. buildGroupbyCombinations only requested that level for "Show column summaries", so with only "Show rows subtotal" the generated query was identical to the no-options query (same cache entry) and no subtotal rows were ever fetched. The mirror case is "Show columns subtotal" with metrics applied on columns, which was gated only on "Show row summaries".

ROOT CAUSE

• Requery: renderTrigger: true on the four controls dates from when every total was computed client-side from leaf rows (feat: Adds options to show subtotals in Pivot Table #24960). SIP-216 (feat(table/pivot-table): correct non-additive totals/subtotals via DB rollup [SIP-216] #41184) made non-additive totals database-computed and chosen per toggle, but left the flag on. feat(pivot-table): reintroduce show-values-as-fraction display option #42761 hit the same class of problem for showValuesAs by adding that control without renderTrigger; the four toggles were not revisited.
• Missing level: rowPrefixNeeded([]) / colPrefixNeeded([]) in buildGroupbyCombinations assumed the renderer's hierarchy equals groupbyRows / groupbyColumns. It does not once the metric dimension is prepended to the metric axis.

CHANGES

• controlPanel.tsx: remove renderTrigger from rowTotals, rowSubTotals, colTotals and colSubTotals, and also from metricsLayout ("Apply metrics on"), transposePivot and combineMetric. Which axis the metrics land on, which dimensions form rows and columns, and whether metrics are combined all decide which rollup level the totals/subtotals options need, so changing them with an option already checked had the same stale-level problem. Toggling any of them now marks the chart as needing an update, and Run fetches the levels it needs (same treatment as showValuesAs). Charts whose metrics are all additive, or that use a result aggregation, also need Run after a change now.
• utilities.ts: also request the collapsed metric-axis level for the metric axis's own subtotal option. It is skipped when metrics are combined (combineMetric), because no per-metric subtotal line exists there and a percent mode would otherwise carry the extra level through the combined-metrics filter and break rendering.
• form_data_query_context.py: _pivot_grouping_sets is the declared Python mirror of the frontend level builder (used when queries are rebuilt from form data, e.g. MCP chart tools and the export fallback); it now applies the same metric-axis subtotal rule so it matches the chart.
• Tests: updated the "row subtotals on, everything else off" pruning expectation and added tests for the control flags (all seven level-selecting controls), the requested levels with and without transposePivot, the generated query, the combined-metrics percent cases (levels unchanged, chart renders), and a parametrized Python test of _pivot_grouping_sets using the same territory/quarter cases as the plugin tests. Run against the previous code, the control, level, query and Python level tests fail.

BEFORE/AFTER SCREENSHOTS

Before	After

Before video: https://gist.githubusercontent.com/sadpandajoe/a7524beba653265a119d951b09036f81/raw/ae1a30051a0ee0c23225b0dff5296c2021fce145/before.mp4

After video: https://gist.githubusercontent.com/sadpandajoe/a7524beba653265a119d951b09036f81/raw/ae1a30051a0ee0c23225b0dff5296c2021fce145/after.mp4

Before: with column summaries checked, toggling "Transpose pivot" (or "Apply metrics on") re-rendered instantly from the stored result (⚡), leaving blank Total or Subtotal cells until a requery was forced; "Show rows subtotal" alone never showed Subtotal lines.

After: each of these controls marks the chart as needing an update; after Run the Total/Subtotal cells are filled in, "Show rows subtotal" alone shows a Subtotal line per row group, and collapsing a group leaves its subtotal row.

The recordings above were captured at an earlier commit of this branch (before combineMetric and the Python mirror were added); they cover the totals/subtotals options, "Apply metrics on" and "Transpose pivot". Browser evidence for the latest commits will be added once an environment is available.

The recordings walk through each case: rows subtotal alone, column summaries, all four options with a collapsed group, the metrics-on-columns mirror, switching "Apply metrics on", toggling "Transpose pivot", and the adjacent checks ("Show values as", combined metrics with a percent display mode, an additive-only chart, and the Median result aggregation).

TESTING INSTRUCTIONS

1. Create a Pivot Table on a dataset with a numeric column, for example Vehicle Sales: Rows territory, Columns quarter, Metrics SUM(sales) and AVG(sales), "Apply metrics on" Rows. Run it.
2. Check Show rows subtotal only. The chart is marked as needing an update; click Run. Each territory group now shows a Subtotal line.
3. Uncheck it, check Show column summaries only, and Run. The Total row is filled in.
4. Check all four options and Run; totals and subtotals are shown, and collapsing a group leaves its subtotal row.
5. Switch "Apply metrics on" to Columns and repeat with Show columns subtotal and Show row summaries.
6. With metrics on Columns and only Show rows subtotal checked, Run, then switch "Apply metrics on" to Rows. The chart is marked as needing an update; click Run and the Subtotal lines appear. Likewise, with only the column summaries option checked, toggle Transpose pivot and click Run: the Total row is filled in.
7. With metrics on Rows, only Show rows subtotal checked and Combine metrics checked, Run, then uncheck Combine metrics: the chart is marked as needing an update and Run adds the per-metric Subtotal lines.
8. cd superset-frontend && npx jest plugins/plugin-chart-pivot-table; pytest tests/unit_tests/mcp_service/chart/test_native_chart_regressions.py

ADDITIONAL INFORMATION

• Has associated issue:
• Required feature flags:
• Changes UI
• Includes DB Migration (follow approval process in SIP-59)
  • Migration is atomic, supports rollback & is backwards-compatible
  • Confirm DB migration upgrade and downgrade tested
  • Runtime estimates and downtime expectations provided
• Introduces new feature or API
• Removes existing feature or API