Sunday, October 20, 2013

Pivot Table plugin for Apex

UPDATE 10.02.2018: A new version of the plugin has been released, see this blog post.

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!

46 comments:

Dimitri Gielis said...

Very nice Morten!

Gilcrest said...

Wow - super cool!

Unknown 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 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

Unknown 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

Anonymous 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

Anonymous said...

Hello.

Great solution.
I have two questions:
1. Is it possible to disable "Totals" column and row?
2. Why doesnt "order by" work - from sql in region source?

Regards,
Dean

Walter Liu said...

Just wondering, you wouldn't have a Apex 4.1.1 version of this would you?

Guilherme said...

Hi Morten, congrats for the plugin. does it work on Apex 5 ? I installed on apex.oracle.com (already apex 5) and when we try to drag a column it expands the column width and does not allow the column to be dragged.

Thanks

Guilherme (from Brazil)

Anonymous said...

Great Work

This plugin work in APEX 5.0

Best regards
Eduardo

Anonymous said...

Hi,

When we try to drag a column it expands the column width not allow to be dragged in APEX 5.0
plzzzz any one help me.

Anonymous said...

Is there any way to include excel download option for this Region

GOD said...

Hi.

This plugin editable not work in APEX 5.0 ... Please develop it on APEX 5.0

Maybe correct????

Help me.

gereltod said...

This plugin not work on APEX 5.0 .. Please check it!!! Migrate and develop this plugin on APEX 5.0 version.

Anonymous said...

Interacting with the rendered plug-in APEX 5.0 generates hundreds of JavaScript errors (check the browser console) from the jquery.ui.sortable.min.js library included with the plug-in, indicating some incompatibility with the APEX 5.0 versions of other libraries.


The plug-in code includes a comment about this library:

-- note: this file is also found in /i/libraries/jquery-ui/1.8.22/ui/minified/ but is not included by default by Apex
apex_javascript.add_library (
p_name => 'jquery.ui.sortable.min',
p_directory => p_plugin.file_prefix);

APEX 5.0 has a more recent version of the library at /i/libraries/jquery-ui/1.10.4/ui/minified/jquery.ui.sortable.min.js, and the plug-in works when used with this.


Go to Shared Components > Plug-ins > Pivot Table, delete the original jquery.ui.sortable.min.js from the Files section, and upload the 1.10.4 version instead.

--
-- Thanks to Jawad who found and implemented the solution m.jawadkhan@yahoo.com
--

Unknown said...

Can we export data as CSV?

Unknown said...

Awesome, Just amazing..
A small ask.

In addition to the built-in table, heatmap and table-barchart renderers, PivotTable.js ships with a few optional extra renderers that add charting or TSV export support.

C3 Charts (demo)
Exporting to TSV (demo)
Google Charts (demo)

Any chance a upgrade to the plugin might include these as well.
Do you know it others have built new plugins into apex with these?
thank a million.

Anonymous said...

Thank you for the posting this is very useful.I am new to Oracle APEX and trying to build a form to capture the data in a Pivot table form.
Any guidance on how I can use this Plug-In to achieve it. I know this is an Interactive Report I am hoping I can use this Plug-In for Insert and Update data into tables.

Thanks in advance for your help.

Thanks
Pavan

Anonymous said...

Thank you for the plugin.. but it always show loading, please wait message.

First time it shows correct and when I logged out and login it shows loading, please wait message.

I am using apex 5.0

Please help.

Suraj Shetkar said...

Getting Loading, please wait... error. I am using apex 5.1. I have updated jquery.ui.sortable.min with latest version... still getting the same issue

Unknown said...

Apex Version 5.1.3 - Also getting "Loading, please wait . . . "

I have updated jquery.ui.sortable.min with latest version.
There is also corruption of the Navigation Menu and the Apex Developer Menu bar has disappeared.

It looks like everything on the page, below the Pivot has corrupted.

Uwe Onken said...

I had the same problem "Loading, please wait . . . " in APEX 5.1.0. In older APEX versions it worked fine for years.

The update of jquery.ui.sortable.min.js didn't bring any improvement.

The Chrome console gave the error "Uncaught SyntaxError: Unexpected token -".

It seems that the function apex_plugin.get_ajax_identifier can now return a string with a "-" inside.

I made a change in the PlugIn code:
old:
l_ajax_id varchar(2000) := apex_plugin.get_ajax_identifier;

new:
l_ajax_id varchar(2000) := REPLACE(apex_plugin.get_ajax_identifier, '-','_') ;

And now everything works fine.

Maybe it's the solution.

Unknown said...

Hi

how can i set default aggregator to intsum ?
and set default column?

Thanks
lior

Richie V said...

How can you modify or add constraints to one of the columns form the report? I'd like to create a link column in the report. I'm not able to access the report columns like I can from a regular interactive report.

Anonymous said...

Hey Morten,

cool stuff :-) very nice.
Just one question.
Is it possible to have more then one aggregate at pivotcell?

regards Udo

shan said...

This is not working in oracle apex 20 version . Any updated plugin available

shan said...

This is mot working in apex Version 20