Sunday, October 20, 2013

Pivot Table plugin for Apex

An interactive Pivot Table lets the user analyze a data set by selecting which fields to use as rows and columns in a grid. You've probably seen this in Excel, something like this:



I've created a region plugin for Apex which turns any SQL query into an interactive pivot table. The plugin is a simple wrapper for a pivot table written in Javascript by Nicolas Kruchten. There's also a nice tutorial page that explains the user interface.

This animated screenshot shows how the plugin is set up and used in Apex:


Note that you can have multiple Pivot tables on the same page if you want. You can also turn off the interactive features if you just want to display a preset summary.

You can download the plugin here. Enjoy!

25 comments:

Dimitri Gielis said...

Very nice Morten!

Gilcrest said...

Wow - super cool!

Thanh Thanh said...
This comment has been removed by the author.
Christian Rokitta said...

This looks really cool. I'm going to give it a try in my current project. This is a great reporting feature to turn Excel users away from the dark side of the force ...

Joni Vandenberghe said...

This looks pretty cool, well done!

Tobias Arnhold said...

Great solution. A year ago I build an example app showing the different possibilities using pivot techniques. When I find the time I will include your solution as well. Hope this is ok for you.

If you are interested:
http://apex.oracle.com/pls/apex/f?p=65580

Cheers Tobias

tim.jacobs said...

This is amazing!!!

You for hire?

Morten Braten said...

@Dimitri, Gilcrest, Joni, Tim: Thanks! As noted, Nicolas Kruchten did all the hard work with the Javascript, all I did was put a thin Apex wrapper around it... :-)

@Thanh: I'll update the plugin with the ability to preset the aggregation (sum, etc.) and field to use for that.

@Christian: Yup, with a combination of Interactive Reports and Interactive Pivots the users should have little reason to keep using Excel to analyze their data... Looking forward to use this plugin in my own projects as well! :-)

@Tobias: Nice overview of pivot options, please go ahead and add this plugin to the list.

- Morten

Anonymous said...

A colleague and I talked a few days ago just about this topic...means it would be great to have it.
I we ended with the conclusion that this would take a huge of work (in particular regarding Java Script)...
Mortan - Thank you very much!
Andre

Anonymous said...

Hello Morten,

First of all, Great work. Magic !
Thanks a ton.

I have a few things need to ask you regarding this.
Such as I want "SUM" to be default when the region loads. Also, When we give default column and row, I also want to give another default column in the area just below the drop down list where you have added COST, there I need to give COST as default column when the region loads.
in short, I dont want user to customize it after the load. I want to give default columns and SUM the content and just show it as a report.

for eg.
Default Column - Project
Default Row - Assigned to
Default aggregation - SUM
Default content - COST

Can I please get you email address where I can contact you and give more details?

Thanks,
QD

Unknown said...

Great work! I have a question or two...

Did you continue to use CoffeeScript, or just Javascript? (I'm hoping to reduce code dependencies in our environment.)

Also, do you have plans to enable download of the resulting analysis somehow? Cut/paste into Excel does work, but is not so elegant.

Thank you!

Anonymous said...

Brilliant!
With a download functionallity it would be even more brilliant.

Nice work

shan sundaram said...


First of all salute to your Great work.

Is there any way to include excel download option for this Region as like classic report in Oracle Apex .

Martin Schaap said...

It's cool indead.

You wrote 'You can also turn off the interactive features if you just want to display a preset summary.'

Do you have an example of that?

Morten Braten said...

@Martin: To display a preset summary, simply specify the default columns and rows to use in the plugin attributes. Set "Enable Interaction" to "No" to create a static (non-interactive) pivot table.

- Morten

Martin Schaap said...

Thanks Morton,

I figured out what I did wrong. If using multiple columns no spaces are allowed in the column list.

How can I get a 'intsum' of an amount in the preset summary?

Shahana Shafiuddin said...

Very good way to show the work.

Anantha Narayanan said...

This is an amazing solution, thanks a lot...

walter - croma software said...

Hi Morten, congratulations !

how to show change the Decimal Marker to a Comma?

Example:

from value 1234.56
to value 1.234,56

Anantha Narayanan said...

I have been using this plugin a lot, however got a doubt. There are options to allow default Rows & Columns. How can i set the default value below Count dropdown?

Megan said...

I also would like to set the default value field and type of aggregation (sum) in the settings. Is that possible?

Aris Lazaridis said...

Congratulations for the plugin

I wonder if thereis a way to load data throug PL/SQL

Aris

Anonymous said...

Hi,
How is it possible to have the result for a pivot displayed as a chart, I mean the output of the pivot will be the input for a chart.

Regards,
Omar

Aris Lazaridis said...

Morten Hi,
First of all great work.

Finally I managed to set default value for aggregator as intSum.
now I am trying to set as default a specific field to work with the aggregator without having the user to drag and drop it.

Any help would be appreciated.

Thanks in advance
Aris

Unknown said...

Hi ARis

How did you set the default to intSum ? And did you manage to have a default var to sum over also?

regards
mette