[data grid] Implement Pivoting

[oliviertassinari]

What a pivot table is?

TLDR

Transform row data from given selected field(s) into column headers; values under these columns are aggregations of all the rows that share the same value on the selected fields(s)

A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or data table to obtain a desired report.

It’s an extension of the behavior of combining row grouping and aggregation.
With the ability to pivot columns, users can break down the row-grouped values in a new dimension, and the aggregation functions are used to compute the crossed values between rows and columns.

It’s a tool that targets end-users and enables them to build a new table by combining different computed values based on the original dataset.

How does pivoting work?

A pivot table is built over four distinct concepts.

• Rows: When a field is chosen to provide the rows, it populates the first column with the unique values of that field. It’s the logic of Row grouping.
• Columns: When a field is set for the columns, the unique values of that field are listed across the top as column headers. It is similar to Row grouping, but with columns.
  This property is what effectively gives the sense of data pivoting.
• Computed values: When a field is chosen, its values are aggregated based on the cross between rows and columns established by the previous inputs. The most common aggregation are sum, average, minimum and maximum.
  It’s the logic from Aggregation, but extended to aggregate the value in multiple columns represented by a field’s unique values.
• Filters: Filters original dataset.

Examples of use cases

• https://www.youtube.com/watch?v=-LM6-BJd1HA

• Extracting the sum of gold medals per country each year:

  Original dataset
  

  Row group: Country,
  Columns: Year,
  Computed Values: Gold (sum)
  

  Row group: Year,
  Columns: Country,
  Computed Values: Gold (sum)
  

When a user turns on the pivoting:

1. The Data Grid stops displaying the original dataset
2. The Data Grid displays the tool for building the pivot table:
   • A list of all the available fields.
   • 3 inputs for columns, rows, and computed values.
   • Access to the filter panel to apply filters to the original dataset
3. The Data Grid renders the pivot table based on which fields are used on each of the four inputs.

Where can users access this function?

At the new “Manage columns” panel
#5700

Challenges

• UI/UX design of a pivoting panel
• Rendering autogenerated Columns (maybe Column definitions)
• Crossing Aggregation (Rows vs columns)

Benchmark

• https://airtable.com/marketplace/blkyAnUVk7O0NMrQp/pivot-table
• https://help.tableau.com/current/prep/en-us/prep_pivot.htm
• https://www.ag-grid.com/javascript-grid-pivoting/
• https://ej2.syncfusion.com/react/demos/#/material/pivot-table/default
• https://demos.telerik.com/kendo-ui/pivotgrid/index
• https://demos.telerik.com/kendo-ui/pivotgridv2/index
• https://webix.com/pivot/
• https://www.htmlelements.com/docs/pivot-table/
• https://js.devexpress.com/Demos/WidgetsGallery/Demo/PivotGrid/Overview/jQuery/Light/
• Excel

[ghost]

im looking foward to it :D

[deepankarmalhan]

Has this been confirmed to be in Jul - Sep timeframe?

[shubhamgoswamiofficial]

I thought we are the only one who always extend the deadline 😆

[oliviertassinari]

We don't yet have a deadline for this feature. We have changed the roadmap presentation a bit. We now have an internal roadmap where we set ambitious targets and a public one where we try to under promise, so we can more consistently overdeliver.

[KlausFabian]

Main issue for me is that I can't make column headers stick - not being scrolled out.
Maybe there is a way but I didn't see it.

[m4theushw]

@KlausFabian Is this what you're looking for? https://mui.com/components/data-grid/columns/#column-pinning

[KlausFabian]

@m4theushw That looks exactly what I have been looking for!!! Thanks a lot. But does that only work with the DataGridPro?

[srgg]

Is there any progress on this?

[oliviertassinari]

Is there any progress on this?

@srgg We recently moved the feature to "Next in queue" in our public MUI X roadmap https://github.com/mui/mui-x/projects/1#card-45123122

Our next step is to run a proof of concept, we recently started to look into it.

Can anyone please please provide ETA for this

@h4r3en Assuming we plan for the worse case: the first unstable API release before the end of year and a stable API for March 2024 feels like something we won't struggle to pull off.

[scamden]

the first unstable API release before the end of year

did this happen? can i test it somewhere?

[syuan100]

the first unstable API release before the end of year

did this happen? can i test it somewhere?

Would also like to know if unstable API is available somewhere

[cherniavskii]

Hi everyone,
We plan to release the Pivoting feature this quarter!
Here is a proof of concept PR: #9877.
You can preview the feature here https://deploy-preview-9877--material-ui-x.netlify.app/x/react-data-grid/pivoting/ - give it a try.
The UI for pivoting will be different, but the overall concept will stay the same.
Let us know what you think!

[anu365]

Can we support fields to be searchable text instead of select box. As we can have thousands of columns and it will be difficult to scroll through them.

[srinivaassunil]

If you are looking for an alternative meanwhile, i have found this library
https://perspective.finos.org/

[delpiersos]

Is this still on track for this quarter?

[cherniavskii]

Hi @delpiersos
We didn't make it in Q1, but it's on track for Q2.
You can check out the preview in #9877: https://deploy-preview-9877--material-ui-x.netlify.app/x/react-data-grid/pivoting/
We would appreciate your feedback!

[delpiersos]

Hi @delpiersos We didn't make it in Q1, but it's on track for Q2. You can check out the preview in #9877: https://deploy-preview-9877--material-ui-x.netlify.app/x/react-data-grid/pivoting/ We would appreciate your feedback!

Hi @cherniavskii - thanks for the update, really hoping to see it soon.
Some feedback on the preview you've shared (not sure if there's anything more recent); note i'm a PM, not an engineer - apologies if my views appear to take things too simply or I'm missing a technical point somewhere:

• When opening the "Columns" or "Filters" menus in the top - columns get repeated - seemingly to n (i.e. the matching the number of occurrences across the data set). Really, these data elements are common entity across all entries, so by removing "Gross", it should be removing it from all pivot columns.
• Filters similarly should only contain each unique item in that way, when I filter on "Gross", I'm expecting to just pick Gross and my filter, and show items that match, regardless of the Year group or whatever it might be. I should also be able to filter on "Year" (the column) in this example, but it's not available.

- Given that the Values contain items that should allow mathematical operators - i.e. sum of Gross, avg of rating; I would expect the filters to allow for numeric filtering like the below, but perhaps the data set is poorly defined?: -- greater than, greater than or equal to -- less than, less than or equal to -- between

• Let me hide the pivot controls to take back screen real estate to view the results

It's really encouraging progress; the feedback above make it a bit difficult to incorporate into my use case in a way that my users would be able to leverage.

[RomanMain]

Hey. @cherniavskii Can we know the approximate delivery time for this functionality in the stable version? Since Q2 is over, when can we expect it? Thank you!

[anu365]

Hey. @cherniavskii Can we know the approximate delivery time for this functionality in the stable version? Since Q3 is also over, when can we expect it?