Feature Request: Pivot Table

[pbower]

Clear and concise description of the problem

Hi there,

I'm wondering if there's any possibility of adding a Pivot Table to Slickgrid ? Or if one exists already? I noticed this old thread, where it's possible a customisation was used?

Suggested solution

Common Pivot table functionality - with a right-pane for drag/dropping columns.

Alternative

It seems like there are various workarounds in the product, but there remains a gap vs other offerings such as AG-Grid, and other products. Note: the AG-Grid one is very ugly, so I'm sure Slickgrid can look nicer!!

Additional context

This would be a massive help. However, of course it is a significant investment of time so completely understandable if it's not possible at this time. Other than that, loving the library. Thanks!

P.s @ghiscoding if you are still checking out and working with this repo, I would raise in slickgrid-universal however I've not yet migrated due a delay and flicker upon loading Slickgrid-React. If you would like further details on this I'm happy to lodge a separate issue. Thanks a lot.

Pete.

Validations

• [X] Follow our Code of Conduct
• [X] Read the Wikis.
• [X] Check that there isn't already an issue that request the same feature to avoid creating a duplicate.

[ghiscoding]

There is this Pivoting example that @6pac created, but it's not using drag&drop (like Ag-Grid) and I don't have any use for pivot grids on my side. If you have any questions then Ben might be better answering them. I know Ag-Grid offers pivot grids with drag&drop but it's an enterprise feature and it means you'll pay a hefty price to get it. I hardly understand how Excel pivot works, so I didn't do any coding related to the subject. I don't think that we'll support more than the example that I pointed out, it's probably too much work to support.

I also only use Slickgrid-Universal myself like you mention though I keep maintaining both because they share the same codebase.

[6pac]

As usual, the meaning of 'add Pivot Table' varies considerably depending who you talk to.
Are we just adding the distinct row values of a column as additional columns? Do we need to filter them? Is the value numeric or non numeric, and how does this respond to aggregation? Do we want multiple value columns? Additional aggregated columns?

That's why I've found this easier to deal with by using code to create a flexible framework that can handle all of these scenarios. It's in the code for that sample. I haven't got around to attaching that to a UI yet though.

I'm possibly embarrassed to admit using it, but in my experience Microsoft Access had one of the best and most intuitive pivoting setups I've ever seen. SQL Server Reporting Services had a much more sophisiticated 'data cube' setup that was more flexible but quite cryptic, and with the many bugs it had and the almost complete lack of useful documentation, I found it highly frustrating and I had to re-educate myself about how it worked by running little experiments almost every time I used it. So I've modelled the code framework on the MS Access concepts, with some judicious tweaks and enhancements.

[pbower]

Great, that link's super helpful, thanks. I haven't used the Microsoft Access one, but it sounds like it's a great setup. I really like the flexibility of Excel, but if I can suggest that if the grid could do the 'drag and drop' part, would be super useful. As, it's hard to build the UI to look right, with all the updates etc E.g., if one drags a textual column name to the right hand side, etc.

Potentially with the option to roll one's own aggregations. E.g., the ability to make COUNT, SUM, DISTINCT COUNT, etc. be overridable functions, so that they can be done server-side if necessary, would be sick.

[ghiscoding]

The only library that we require in SlickGrid is SortableJS and I'm not sure if it would fully support that kind of dragging, maybe at least partially since I was able to convert Draggable Grouping to work with it. SortableJS supports 2 kind of scenarios

1. reordering (e.g. column reorder in SlickGrid)
2. drag&drop to a drop zone (like Draggable Grouping)

It would be helpful to have this feature but let's face it, we will probably never have time to implement it ourselves, at least not without any external contributions. I've already spent way too much time on this library (past 7 years) and I consider the project to be pretty much feature complete. The only other thing that I might look into in the long term future might be row spanning (as pointed in this comment). Pivot Table seems to be more of an enterprise feature request which Ag-Grid is gladly providing with of course a bag of money handle to them :D ... instead of having Pivot Table in the grid, why not just export to Excel and do the Pivot there which Excel is super good at? I already have Export to Excel in Slickgrid-Universal and recently rewrote excel-builder-vanilla to use native browser code and is now very lightweight... anyway that's just my point of view, there's a ton of Excel experts which are always looking for reasons to keep using Excel, so here's one more reason 😉

Contributions are always welcome.

[pbower]

Hi, sure no problem at all, thanks for considering it. That's duly noted, I'll see what I can do on that in future and if it's useful I'll be sure to share it back. Cheers

[6pac]

FYI Here's a sample of MS Access:

It restricts the Crosstab Query (this is the Access name for a pivot) to a single 'Column Heading' and 'Value' column.

[pbower]

Cool, thanks for sharing that back.