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!
Very nice Morten!
ReplyDeleteWow - super cool!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis 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 ...
ReplyDeleteThis looks pretty cool, well done!
ReplyDeleteGreat 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.
ReplyDeleteIf you are interested:
http://apex.oracle.com/pls/apex/f?p=65580
Cheers Tobias
This is amazing!!!
ReplyDeleteYou for hire?
@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... :-)
ReplyDelete@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
A colleague and I talked a few days ago just about this topic...means it would be great to have it.
ReplyDeleteI we ended with the conclusion that this would take a huge of work (in particular regarding Java Script)...
Mortan - Thank you very much!
Andre
Hello Morten,
ReplyDeleteFirst 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
Great work! I have a question or two...
ReplyDeleteDid 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!
Brilliant!
ReplyDeleteWith a download functionallity it would be even more brilliant.
Nice work
ReplyDeleteFirst 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 .
It's cool indead.
ReplyDeleteYou 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?
@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.
ReplyDelete- Morten
Thanks Morton,
ReplyDeleteI 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?
Very good way to show the work.
ReplyDeleteThis is an amazing solution, thanks a lot...
ReplyDeleteHi Morten, congratulations !
ReplyDeletehow to show change the Decimal Marker to a Comma?
Example:
from value 1234.56
to value 1.234,56
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?
ReplyDeleteI also would like to set the default value field and type of aggregation (sum) in the settings. Is that possible?
ReplyDeleteCongratulations for the plugin
ReplyDeleteI wonder if thereis a way to load data throug PL/SQL
Aris
Hi,
ReplyDeleteHow 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
Morten Hi,
ReplyDeleteFirst 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
Hi ARis
ReplyDeleteHow did you set the default to intSum ? And did you manage to have a default var to sum over also?
regards
mette
Hello.
ReplyDeleteGreat 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
Just wondering, you wouldn't have a Apex 4.1.1 version of this would you?
ReplyDeleteHi 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.
ReplyDeleteThanks
Guilherme (from Brazil)
Great Work
ReplyDeleteThis plugin work in APEX 5.0
Best regards
Eduardo
Hi,
ReplyDeleteWhen 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.
Is there any way to include excel download option for this Region
ReplyDeleteHi.
ReplyDeleteThis plugin editable not work in APEX 5.0 ... Please develop it on APEX 5.0
Maybe correct????
Help me.
This plugin not work on APEX 5.0 .. Please check it!!! Migrate and develop this plugin on APEX 5.0 version.
ReplyDeleteInteracting 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.
ReplyDeleteThe 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
--
Can we export data as CSV?
ReplyDeleteAwesome, Just amazing..
ReplyDeleteA 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.
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.
ReplyDeleteAny 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
Thank you for the plugin.. but it always show loading, please wait message.
ReplyDeleteFirst 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.
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
ReplyDeleteApex Version 5.1.3 - Also getting "Loading, please wait . . . "
ReplyDeleteI 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.
I had the same problem "Loading, please wait . . . " in APEX 5.1.0. In older APEX versions it worked fine for years.
ReplyDeleteThe 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.
Hi
ReplyDeletehow can i set default aggregator to intsum ?
and set default column?
Thanks
lior
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.
ReplyDeleteHey Morten,
ReplyDeletecool stuff :-) very nice.
Just one question.
Is it possible to have more then one aggregate at pivotcell?
regards Udo
This is not working in oracle apex 20 version . Any updated plugin available
ReplyDeleteThis is mot working in apex Version 20
ReplyDelete