GitHub user dosubot[bot] added a comment to the discussion: PIVOT TABLE MODIFICATIONS
<!-- Answer --> Great questions! Here's guidance for both modifications: ## Server-Side Pagination for Pivot Table Currently, only the Table chart has server-side pagination in Superset. You'll need to follow its pattern by modifying these files: **1. Modify [`buildQuery.ts`](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/buildQuery.ts):** - Accept `ownState` parameter with `currentPage` and `pageSize` - Add `row_limit` and `row_offset` to the query object: ```typescript const pageSize = ownState.pageSize ?? formDataCopy.server_page_length; const currentPage = ownState.currentPage ?? 0; moreProps.row_limit = pageSize; moreProps.row_offset = currentPage * pageSize; ``` - Generate a [secondary count query with `is_rowcount: true`](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-table/src/buildQuery.ts#L343-L356) to get total rows **2. Add control panel options** in `controlPanel.tsx` for `server_pagination` and `server_page_length` **3. Handle pagination state changes** using [`setDataMask` to update `ownState`](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-table/src/TableChart.tsx#L1110-L1120) **Challenge:** Pivot tables aggregate data hierarchically, so server-side pagination is tricky since subtotals require access to complete data. Consider limiting pagination to leaf-level data only. --- ## Adding Custom WHERE Conditions (Month Filter) Yes, you can modify buildQuery to add filters! Add this to your [`buildQuery.ts`](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/buildQuery.ts): ```typescript // Add filter for categorical month column if (formData.month_filter && formData.month_filter.length > 0) { queryObject = { ...queryObject, filters: [ ...(queryObject.filters || []), { col: 'your_month_column', op: 'IN', val: formData.month_filter, // e.g., ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'] }, ], }; } ``` Then add a corresponding control in `controlPanel.tsx`: ```typescript { name: 'month_filter', config: { type: 'SelectControl', label: t('Month Filter'), multi: true, choices: [['Jan', 'January'], ['Feb', 'February'], /* ... */], }, } ``` **Alternative:** Users can already add adhoc filters via the UI without code changes—this may be simpler for ad-hoc filtering needs. --- Note that [this is a known gap](https://github.com/apache/superset/issues/37091) in the pivot table plugin. Would you like more details on any specific part of this implementation? <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/1609eda4-8c9b-4be3-a1a8-d18b580b528f?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=Great%20questions%21%20Here%27s%20guidance%20for%20both%20modifications%3A%0A%0A%23%23%20Server-Side%20Pagination%20for%20Pivot%20Table%0A%0ACurrently%2C%20only%20the%20Table%20chart%20has%20server-side%20pagination%20in%20Superset.%20You%27ll%20need%20to%20follow%20its%20pattern%20by%20modifying%20these%20files%3A%0A%0A%2A%2A1.%20Modify%20%5B%60buildQuery.ts%60%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/buildQuery.ts%29%3A%2A%2A%0A-%20Accept%20%60ownState%60%20parameter%20with%20%60currentPage%60%20and%20%60pageSize%60%0A-%20Add%20%60row_limit%60%20and%20%60row_offset%60%20to%20the%20query%20object%3A%0A%60%60%60typescript%0Aconst%20pageSize%20% 3D%20ownState.pageSize%20%3F%3F%20formDataCopy.server_page_length%3B%0Aconst%20currentPage%20%3D%20ownState.currentPage%20%3F%3F%200%3B%0AmoreProps.row_limit%20%3D%20pageSize%3B%0AmoreProps.row_offset%20%3D%20currentPage%20%2A%20pageSize%3B%0A%60%60%60%0A-%20Generate%20a%20%5Bsecondary%20count%20query%20with%20%60is_rowcount%3A%20true%60%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-table/src/buildQuery.ts%23L343-L356%29%20to%20get%20total%20rows%0A%0A%2A%2A2.%20Add%20control%20panel%20options%2A%2A%20in%20%60controlPanel.tsx%60%20for%20%60server_pagination%60%20and%20%60server_page_length%60%0A%0A%2A%2A3.%20Handle%20pagination%20state%20changes%2A%2A%20using%20%5B%60setDataMask%60%20to%20update%20%60ownState%60%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-table/src/TableChart.tsx%23L1110-L1120%29%0A%0A%2A%2AChallenge%3A%2 A%2A%20Pivot%20tables%20aggregate%20data%20hierarchically%2C%20so%20server-side%20pagination%20is%20tricky%20since%20subtotals%20require%20access%20to%20complete%20data.%20Consider%20limiting%20pagination%20to%20leaf-level%20data%20only.%0A%0A---%0A%0A%23%23%20Adding%20Custom%20WHERE%20Conditions%20%28Month%20Filter%29%0A%0AYes%2C%20you%20can%20modify%20buildQuery%20to%20add%20filters%21%20Add%20this%20to%20your%20%5B%60buildQuery.ts%60%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/buildQuery.ts%29%3A%0A%0A%60%60%60typescript%0A//%20Add%20filter%20for%20categorical%20month%20column%0Aif%20%28formData.month_filter%20%26%26%20formData.month_filter.length%20%3E%200%29%20%7B%0A%20%20queryObject%20%3D%20%7B%0A%20%20%20%20...queryObject%2C%0A%20%20%20%20filters%3A%20%5B%0A%20%20%20%20%20%20...%28queryObject.filters%20%7C%7C%20%5B%5D%29%2C%0A%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20% 20col%3A%20%27your_month_column%27%2C%0A%20%20%20%20%20%20%20%20op%3A%20%27IN%27%2C%0A%20%20%20%20%20%20%20%20val%3A%20formData.month_filter%2C%20//%20e.g.%2C%20%5B%27Jan%27%2C%20%27Feb%27%2C%20%27Mar%27%2C%20%27Apr%27%2C%20%27May%27%2C%20%27Jun%27%5D%0A%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%5D%2C%0A%20%20%7D%3B%0A%7D%0A%60%60%60%0A%0AThen%20add%20a%20corresponding%20control%20in%20%60controlPanel.tsx%60%3A%0A%60%60%60typescript%0A%7B%0A%20%20name%3A%20%27month_filter%27%2C%0A%20%20config%3A%20%7B%0A%20%20%20%20type%3A%20%27SelectControl%27%2C%0A%20%20%20%20label%3A%20t%28%27Month%20Filter%27%29%2C%0A%20%20%20%20multi%3A%20true%2C%0A%20%20%20%20choices%3A%20%5B%5B%27Jan%27%2C%20%27January%27%5D%2C%20%5B%27Feb%27%2C%20%27February%27%5D%2C%20/%2A%20...%20%2A/%5D%2C%0A%20%20%7D%2C%0A%7D%0A%60%60%60%0A%0A%2A%2AAlternative%3A%2A%2A%20Users%20can%20already%20add%20adhoc%20filters%20via%20the%20UI%20without%20code%20changes%E2%80%94this%20may%20be%20simpler%20for%20ad-hoc%20filtering%20ne eds.%0A%0A---%0A%0ANote%20that%20%5Bthis%20is%20a%20known%20gap%5D%28https%3A//github.com/apache/superset/issues/37091%29%20in%20the%20pivot%20table%20plugin.%20Would%20you%20like%20more%20details%20on%20any%20specific%20part%20of%20this%20implementation%3F) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/38230) GitHub link: https://github.com/apache/superset/discussions/38230#discussioncomment-15918661 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
