Saturday, February 10, 2018

Pivot Table plugin for APEX (Updated)

Some years ago I created an APEX region plugin for displaying the results of a query as a Pivot Table. You can read the original blog post about that here.

The plugin proved quite popular, but people were having problems using it with APEX 5 due to a jQuery versioning conflict.

I've now updated the plugin so it works with APEX, both 5.0 and 5.1.



To use it, simply add a region to the page, change the region type to "Pivot Table" and write an SQL query as the region source:



On the region attributes page, specify the options. I've added some help text to explain the various options. Note that you can have more than one Pivot Table region on the same page, but be sure to use a unique name in the "DOM Element Name" attribute for each pivot table.


I've also added/enabled some extra features such as spreadsheet export (via TSV - tab separated values) and charting.



It should be noted that the plugin is just an APEX wrapper for the Javascript Pivot Table component by Nicolas Kruchten who did all the hard work. I merely packaged it up for use with APEX.

You can download the plugin from my APEX plugins page. Enjoy! :-)



15 comments:

Unknown said...

Hi Morten,

Great plugin. Is it possible to use column aliases in the Default Column(s) section. For example SELECT JOB_NAME "Job Name" from JOB? I have tried then to add "Job Name" in the column name aliases but doesn't seem to work.

Cheers

Paul

Wayne len said...

May I know how to default aggregation - SUM?
Thank you.

Unknown said...

Hi

I have the same question
May I know how to default aggregation - SUM?

Thanks
lior

Anonymous said...

May I know how to default aggregation - SUM?
Thank you.

PaulP said...

Where do I go for support? I have a number of questions regarding caching data and performance.

Anonymous said...

in order to change default aggregation i used the following js code on page load:

var x = document.getElementsByClassName("pvtAggregator");
x[0].value = "Sum";
$(".pvtAggregator").trigger("change");

Cheers,
Sorin

Rolando Guiñazu said...

Increíble, gracias desde argentina

Unknown said...

HELLO,
IT IS POSSIBLE TO USE A COLUMN FOR THE PIVOT VALUES

Miguel Rosemberg said...

Hi, i'm migrating from apex 20.1 to 22.1 (with the latest ords) but in the new server just shows de JSON (as a plain text) and nothing more.

What am i doing wrong?

this is a portion of the page
{
"row":[
{
"ID_TIPO_EVENTO":77
,"ID_DEPARTAMENTO":57
,"DESCRIPCION":"Seguros"
,"VALIDA_NIVEL_JERARQUICO":"N"
,"ACTIVO":"S"
,"ASIGNA_USUARIO":"N"
,"CANT_AUTORIZANTES":0
}
,{
"ID_TIPO_EVENTO":78
,"ID_DEPARTAMENTO":57
,"DESCRIPCION":"Hogar"
,"VALIDA_NIVEL_JERARQUICO":"N"
,"ID_TIPO_EVENTO_PADRE":77
,"ACTIVO":"S"
,"ASIGNA_USUARIO":"N"
,"CANT_AUTORIZANTES":0
}

Miguel Rosemberg said...

Hi Morten, i am migrating my app in a new server from apex 20.1 to 22.1 (latest) and the latest ORDS too, but a page with the pivot table plug in (that works on 20.1) doesn't work on 22.1, it just shows the Report in plan text as JSON.

Here is the first lines
{
"row":[
{
"ID_TIPO_EVENTO":77
,"ID_DEPARTAMENTO":57
,"DESCRIPCION":"Seguros"
,"VALIDA_NIVEL_JERARQUICO":"N"
,"ACTIVO":"S"
,"ASIGNA_USUARIO":"N"
,"CANT_AUTORIZANTES":0
}
,{
"ID_TIPO_EVENTO":78
,"ID_DEPARTAMENTO":57
,"DESCRIPCION":"Hogar"
,"VALIDA_NIVEL_JERARQUICO":"N"
,"ID_TIPO_EVENTO_PADRE":77
,"ACTIVO":"S"
,"ASIGNA_USUARIO":"N"
,"CANT_AUTORIZANTES":0
}

Miguel Rosemberg said...

Hello Morten, the Pivot Table Plugin doesn't work with Apex 22? With version 21.xx still works.
Anyone try it on the last apex version?

Morten Braten said...

@Miguel: See https://github.com/mortenbra/apex-plugins/issues/7#issuecomment-1319981497

Michael said...

I think, I fixed it.

I posted the solution in your github thread.

Please contact me for more details.

thanks,
Michael

michael.weinberger@verbund.com said...

Hi Morten!
I did enhance your plugin with the subtotal js. plugin: https://nagarajanchinnasamy.github.io/subtotal/examples/index.html
We should put this new version on github

I hope, you like it

Michael.Weinberger@verbund.com said...

I put a working copy on oracle:
https://apex.oracle.com/pls/apex/f?p=DEMO-PIVOT-2023