Pivot Tables?

asiTechsupport

I'm evaluating DataTables for use as a data viewer for a CRM solution. And one thing that happens currently is people will pull out data into excel and then create pivot tables from that. My thought was, "I wonder if there are any javascript pivot table code out there!"

In my quest I ran across the OpenLink AJAX Toolkit (OAT) framework, which has a pivottable widget (http://oat.openlinksw.com/index.html?pivot). However, the OAT framework looks to be a similar framwork to JQuery, so I wondered if there were any JQuery plugins to do the job... I found several project descriptions, and what looked like many false-starts, but no actual partial or finished code.

Datatables is a very compelling implementation, and I'd rather stick with it since there's already so much here in terms of easily manipulating the table data. However, I've not been able to find any mention of "pivottable", "pivot table", "cross table" or the like in the forums.

Has anyone looked into bringing a pivot table, or similar functionality, to Datatables?

allan

Hello,

By pivot table do you mean something like this: http://webdesignernotebook.com/examples/style-table.html ? If so, then yes, DataTables is perfectly capable of displaying data in that manner. You just need to set up the HTML for the table as required (or use an Ajax source) and run DataTables over it. DataTables 1.8 (currently in beta) has support for TH elements in the TBODY of the table, which makes a pivot table like that more semantically correct.

I'll be writing a blog post in the next few days to show how a table like the one linked to can be used and styled with DataTables.

If however you are looking to be able to sort and filter horizontally rather than vertically, then I'm afraid that this is not something that DataTables currently support.

Regards,
Allan

asiTechsupport

I saw the 1.8 notes about the spanning TH elements, which looks very nice!

However, I'm talking about the functionality you can see here: http://oat.openlinksw.com/index.html?pivot

The dark-gray headers can be moved around to slice and dice the data.

I'm looking at this from the standpoint of a plugin, not core. But I just wanted to bring it up, since it doesn't seem to be something that has been discussed before.

allan

That's quite cool - I like that :-) Thanks for the link.

I'm afraid that there is nothing in the core that provides that kind of manipulation, nor is there a plug-in that I am aware of which would allow it either, although I am sure that it is possible - I just thing it would take a bit of development effort in order to create the plug-in to do it (and manipulate the table through the API presented). The exact manipulation would probably need to be reasonably well defined, but it should be possible! Sorry there isn't a piece of code ready to "go" though.

Regards,
Allan

asiTechsupport

Well that's what I needed; just to know that it would be possible and that I wouldn't be spinning my wheels to try to implement something like that... So thanks for the encouraging words!

At the very least I wanted this out there, in case someone else was googling for the same thing. But I may try to tackle this in the not too distant future and try to provide a plugin. Unless, of course, someone more skilled than I beats me to it... (anyone!?!?) :)

jshannon

Hi. Did anything happen with this? I'm also looking for pivot table functionality, but I would be happy with something a bit more simple:

You have a table with x columns, certain columns are defined as pivotable (categories, products, dates, etc), while others are data.

When you click on a pivotable column, it gets removed from the layout and a grouping happens and, within that row, data columns are summed or averaged for matching rows (not sure if this would be defined per column, or would make more sense as user-configurable).

Basically, it'd be like http://www.datatables.net/release-datatables/examples/advanced_init/row_grouping.html . Except that it would start off with Engine being a column (which is how the table is laid out initially). Then you click on "Engine" and it would look like the demo, except with, for the sake of example, an average in the Engine Version and CSS grade columns.

The next step would be to be able to click on the Platform column and get something like:
[code]
Gecko | avg(gecko engines) | avg(gecko ratings)
- Win 98+ / OSX.2+ | avg(win98/osx engines) | avg(win98/osx ratings)
Firefox 1.0 | actual value | actual value
Firefox 2.0 | actual value | actual value

[/code]

So, basically dynamic multi-level row grouping with summary columns.

James

jshannon

Oh. PS... The grouping/summary would ideally start off closed until it's clicked, then expands.