Can anyone share any screenshots of anything along the lines of the goal here? I'll be honest; I've never used a pivot-anything to make sense of, well, anything, and it might help further the understanding?

Oracle Discoverer Desktop

You know everything you may need, unfortunately it is no longer supported.

Syncfusion PIVOT

Has great functionality, but is slow on a lot of data due to client-side processing.

https://ej2.syncfusion.com/demos/#/bootstrap5/pivot-table/toolbar.html

Conditional formating:

https://ej2.syncfusion.com/demos/#/bootstrap5/pivot-table/conditional-formatting.html

If the select and processing run on the server side - in the oracle database - and the client side only performs the rendering, we get a very good solution. Oracle Discoverer also has a web interface, but it would be obsolete.

The importance of the PIVOT function is given by the fact that the user can flexibly shape the display of the data, - e.g. drill into the details -,  save the settings, create calculated data ...

Bud Endress @Oracle KScope 2017 used a demo APEX application called Oracle Analytic View Express that put pivot, table, chart and map functionality over AV data sets.

That would be great, the only question is how/where to get Oracle Analytic View Express.

This will be great function!!

There is a commercial plugin for this:

https://www.apexsmartpivot.com/

Unfortunately it is a bit “outdated” but as far as I know the owner is in Oracle's APEX team now…

Here's a nice example of a pivot table with color rules.  Key characteristics of a pivot table include dill down/up, navigation and nesting of columns or dimensions/hierarchies on ‘edges’ (rows, columns), and page filters to ‘slice’ the data.

Here's a example with page filters (along the top). Pivot tables usually come with a layout manager where columns or hierarchies are positioned and filters are set.

+1 

Very badly needed region. Working with a customer to address this need as  I write this. This would be a “mega” apex feature if implemented. Even Jurgen would be blown away! The current workarounds are somewhat limited + require significant know-how + time to implement. Thank you!

+1  This would be very useful

+1 Needed for sure

There is a commercial plugin for this currently : https://www.apexsmartpivot.com/

I believe that this was created by Christian Rokitta. Could this be folded into the core?

Having created a pivot table in APEX only yesterday I'd like to comment.  I realize with APEX you can do  anything if you are clever enough and you have a good grasp of java scripting and other html skills.  Although I love APEX resorting to extensions is frustrating and often I just figure sc..w it…it's too much trouble.  The current implementation has a confusing UI (ie: 2 sum icon drop downs on the total line…sum the sum???), does not support column totals, and most importantly does not allow for drill to detail (of course you want to know what in in that summed figure).  Not sure  if APEX supports drag and drop but just take a look at OAC implementation of crosstab.  Things like conditional formatting are nice but not essential.

Does the data-grid with the "expand/collapse" option of oracle jet approximate a pivot type region?
If so, would it be easier to integrate a Jet component instead of building a new type region pivot ?

https://www.oracle.com/webfolder/technetwork/jet/jetCookbook.html?component=dataGrid&demo=expandHeaders

Hey @michel.lessard 👋🏻

The Expand/Collapse data grid from JET only provides part of the equation. 

A key part of any decent pivot is the ability for the end user to choose what goes on the left and what goes across the top. Not to mention what to display in the grid itself (Raw data, sum, pct of total, etc). 

Then there's the topic of conditional formatting and ability to have multiple values displayed per row/column pair.

It all boils down to the fact that a good, fully functional and dynamic pivots are hard. 

While the data grid component gets you part of the way there, much of what people would expect from a pivot plugin/item would be missing.

My opinion - your milage may vary

The ap that ‘Bud’ Endress’s team created to sit over Analytic Views would do the job, it would just need to be updated

Hi @doug.gault,

I'll agree that a good, no great, pivot table is hard. The actual coding is probably not the hardest part (but by no means would I trivialize that).  The JET grid does provide a good foundation. IMHO, the hard parts are the specification (it really helps to be fluent in thinking multidimensional) and (as you suggest) the supporting UI (a layout manager, selection and filtering, cell-level formatting, etc).  Such a layout manager could also be used for dynamic charts, maps, etc.

The examples in my comments show several of the supporting elements.  Those are from an app written for Analytic Views (AVs).  AVs made the job much easier because they provided multidimensional metadata, dimensional query structures, and a consistent query ‘target’ from one data set to the next.

It would be easy to envision a pivot table and layout manager regions. This would be much easier to build as an integrated system with Analytic Views.

If anyone is interested in creating plug-ins, I would be interested in collaborating.

@william.endress This is indeed a badly needed feature in APEX.  I miss the older EXPRESS based reporting tools - like Oracle Sales Analyzer and Discoverer OLAP - where we could easily do things that you mentioned -  dill down/up, navigation and nesting of columns or dimensions/hierarchies on ‘edges’ (rows, columns), and page filters to ‘slice’ the data.

We are a company that has leveraged some Oracle Express-based Tools including Oracle objects, Oracle express Analyzer, and even written native code in  Oracle OLAP DML (previously Oracle Express)  since inception to satisfy our multidimensional aware reporting Frontend needs. It was plain simple to work with these legacy tools. We could drill down hierarchical dimensions, slice, and dice multidimensional  cubes, move dimensions between  Edges  in a Table Grid (i.e between  Rows, Columns, and page Edges), and perform very  complex data formatting/highlighting   via Formulae sitting on top of any  fact data with minimal coding 

We are currently migrating our Multidimensional OLAP Cubes to Analytic views this year, 2023 which to me is the perfect successor of Oracle OLAP  Cubes.  We think APEX is the tool to address our reporting needs. The big issue is that to achieve the same reporting capabilities like drilling down into hierarchical dimensions, slicing and dicing, and rotating between Edges in a Grid, we would need to code extensively in APEX from scratch.

We think Oracle or the Apex APEX Team could make life easier for clients by providing an app that would just sit on top of an Analytic view, manipulate the underlying dimensional/hierarchical metadata, and build the reporting Grid without much hassle. I was on YouTube a few weeks back and came across a demo. This is exactly what we need.   Here is a link to the Video if needed

https://www.youtube.com/watch?v=gWphpslF4xw&t=3026s     

We badly need this feature in APEX. I am convinced other clients would be also very happy

I have recently created a Pivot report in APEX on my Interactive Report.  

The 3 most glaring needs I found are:
1. Sum across rows does not exist.  I had to create it in my query.  
2. Formatting of values does not exist at all.  Would be nice to have conditional formatting. 
3. Ability to natively print the pivot report to PDF (AOP is my only option)

We do use this plug-in ORA-00001: Unique constraint violated: Pivot Table plugin for APEX (Updated) 
Drag and drop work like a charm, but it has no drill-down and needs to fetch all the data at load. 

Anyway, I miss the old discoverer and its drag&drop, drill down and slice and dice

@michael.weinberger-2 - I was going to mention the same plugin. 

It may need a little tweaking as it's a bit older and may have some issues with newer versions of APEX, but the core library is sound. 

Might be a good place for the APEX team to start building a native plugin for pivots. :-D

 I think the mentioned plugin stopped working on the more recent versions of APEX. 

The issue with that plugin was that you could not declaratively save the settings (what goes in rows, columns, default computation, etc)

In the meantime I managed to “enhance” interactive report providing "drilldown" using javascript. My solution ist posted here: https://stackoverflow.com/questions/72726784/oracle-apex-interactive-report-pivot-link-to-page  

To make it work, I had to make one pivot report for each level of depth (years, month, days) and every column/rows combination.
For a start Oracle could enhance the APEX Interactive Report  url syntax not only for defining filters and loading reports, but for defining a pivot (rows, columns, value, aggregation) as well. A javascript api would help, too

That's a neat solution Michael, but it's still a long way from the desired functionality.

In my opinion, a feature-rich, native PIVOT element could be used in many applications. Its implementation would be a huge step forward in APEX and in the way APEX is perceived compared to its competitors.

Agreed - a native PIVOT component would be superb.

@william.endress  - are your sample screenshots taken from DV or is it some other tool?  i.e. if Oracle already have a native PIVOT that works within a web front end - then couldn't that be re-used by the APEX team - rather than building something from the ground up?

@brendan.flanagan My screenshots are from an internal application that was focused on querying analytic views (dimensional representations of data).  That app had a very good pivot table.  Certainly, a good working example to emulate.

@sorin.apalaghie I found the problem today and wrote Morten about the fix.
In the latest version there are attributes for rows and columns. And you can set the aggregation function via javascript

@michael.weinberger-2  A few years ago when I was exploring this plugin the error was due to some html component ID's that were longer than expected. I got it working and I did manage to set some defaults using Js, but I'm not aware whether the plugin has any JSAPIs. 

I hope I'm not mistaken but a bit concerning for me at that time was the fact that at least for the charts part it sent data to a 3rd party to generate the charts.

After reviewing this idea with the team, we decided to reframe this idea from “New Region Type: Pivot” to “Improve Pivot Functionality” and we will carefully review other ideas related to pivot which are now marked as duplicate for easier tracking purposes.

Thanks everybody for chiming in.

Hello Team!

Is there any information about when it will be implemented?

Hello @zolo , I asked Mr. @jayson hanes  about this approximately 1 month ago, and this was his kind response:

"We don't forecast nor release dates in which certain new features or ideas make it into a future release of APEX - "roadmap" is the best we can share and that in no way guarantees which future APEX release It will become a available."…

I understand that this is Oracle's policy. In any case, I hope that this idea proposed more than 2 years ago can soon see the light.

This is the Roadmap: https://apex.oracle.com/en/learn/resources/roadmap/

Regards

This is downright bad news.
In several ways:
- it was included in the Roadmap for the first time, so we have been waiting for 2 years when it will become something, suspending our developments
- no high-complexity UI components have been added to APEX since IG
- the development of the basic functions used on a daily basis has slowed down a lot, we understand that the AI ​​assistant is needed from a marketing point of view... BUT... do the users need it too???
- in our opinion, APEX will become obsolete without productive and complex UI components

Hi @zolo , I understand the sentiment. This closure status is generic for labeling purposes, but it does not exclude a reevaluation in the future with a more focused use case. While reviewing this idea, it was decided that we can't prioritize such a vague idea just to mark it as delivered.

Pivot functionality is important and so are complex UI components.

@zolo 

I completely agree with your assessment and, with the utmost respect for the creators and developers of APEX, I believe that strategically, they have chosen the path of "more marketing and fewer solutions for developers." It feels as though their target audience is the non-professional user, and they have neglected improving and evolving the elements that truly make us more productive and allow us to stand out from our competition. I don’t want to think negatively and suggest that there’s no evolution because there are paid solutions that do improve productivity or address shortcomings, like AOP and—what a coincidence—Smartpivot! Well, let's not think badly. We'll simply wait for the right moment, and surely APEX will continue to surprise us... or not!

I agree with @zolo this really bad news. 

Even if @juancarlos suggest the audience is the non professional this would then be even more important for the end users.

This really needs to be addressed.

I m waiting this feature from 2.5 years but it still pending

When I think that the IG (with all its real and good, as well as its flawed features) was supposed to be sold to us as THE future and when I think that it was said that the IR was outdated and should be replaced by the IG...
When I think about it, I can't help but shake my head.
It turned out quite differently. The IG started out as a tiger and ended up as a bedside rug. 
Some warned from the start, others hoped that everything would be fine.
A (simple) grid-based input option is just as urgently needed as a really powerful and stable analysis tool. And APEX was supposed to deliver both out-of-the-box. 
We can only speculate as to why this has not happened after so many years of promises and announcements, but after such a long time we definitely no longer have to understand why.  
It is sad to see how much potential is not being used here. 
It is clear that AI will perhaps make some things possible that were previously not or not so easily possible; but it cannot and will not solve THIS problem.
You say you listen to the community - and I believe the APEX team. 
But there's obviously an elephant in the room somewhere...

Hi @vincent morneau , “vague idea”?

I would rather say it's complex, as there are many implementations of PIVOT functions, available, etc., we specifically referred to some of them above. Also, Oracle has/had several products related to this.

I completely understand and share the opinion of @andreml and the other commenters, we increasingly lose sight of what the focus is and what influences the priorities in APEX development.

It would be great if the development team communicated a little more transparently about what to expect, at least within 1-2 years.

The Oracle APEX development team seems disconnected from the community. After more than 10 years, the Interactive Grid has proven to be a failure still incomplete and lacking real evolution. The new features introduced in recent APEX versions are mostly marginal and do not address developers’ real needs.

Meanwhile, third-party companies like United Codes are doing much more in terms of innovation, filling the gaps left by the official team. A proper grid management system and a fully functional Pivot Table are still missing.

Most of the frontend APEX features used daily by developers date back over 15 years, and the most significant innovations come from the Oracle database rather than the APEX framework itself.

APEX Ideas, initially created to listen to the community, has removed open discussions, and many features announced as "coming soon" have been stuck for years. The whole situation gives the impression of a rubber wall that slows down innovation instead of fostering it.

I have great respect for internal APEX development team and the work they do, but the real issue seems to be at the leadership and strategic vision level. There is no clear direction for the product’s evolution, and this lack of guidance is reflected in the slow pace at which critical issues are addressed.
 

My criticism is not just empty polemics, I develop and provide training exclusively in Oracle APEX for a living. It is in my best interest to see the product improve and evolve.

My latest comment to FR-1907 seem appropriate here too:

Yes we realize some ideas have been sitting in the roadmap for too long. This is partly the result of reviving this app which accumulated a decade of customer ideas. Our challenge is find a balance between actioning popular ideas, valuable ideas regardless of popularity, while aiming for a high volume of delivered ideas to keep this app flowing and useful.

The roadmap is a reflection of what the APEX team thinks is worth doing. It is not a commitment on timeframe and the roadmap is subject to change over time. This situation is clearly not desirable and we will discuss how best to handle it going forward.

To @roberto.capancioni, that's understandable and thank you for the constructive criticism. I won't defend this app or make excuses. APEX customers deserve more transparency about the strategic vision, and this app should have a shorter turnaround from triage to delivery. I'll make sure your message is shared internally.

Hello everyone, I feel exactly the same as @roberto.capancioni. I've been an APEX developer for over 15 years, and there was a time when each new version included features that, in some way, helped me improve both in productivity and in the perception of end users.

For several years now, I've noticed that developers' needs are not being considered as much. It wouldn't be fair to say that APEX is not evolving, but the question is "where is it evolving to?" and how efforts are balanced between the continuous improvement of components that developers require versus integration with other technologies like AI.

I regret to say that when a new version is released, and I eagerly read about the new features, I immediately feel that something is off—I don't feel represented. I end up updating to new versions just to avoid major jumps, but I barely use any of the new features offered. It's sad, but it's real, and based on comments from other developers, it's clear that many of us feel the same way.

I'm a devoted APEX supporter, but I feel like I'm not evolving because something has changed in APEX's strategy. It would be greatly appreciated if this could be reviewed and contrasted with the rest of the community.

THANKS

The lack of real Pivot reporting functionality is unfortunately just another example of how weak APEX is as a reporting tool.  This APEX Idea thread provides great examples of needed PIVOT functionality. (can't even sum across rows at this point.)  Plus, a good review of PIVOT functionality in Excel offers plenty of ideas.  I also want to be clear that I LOVE APEX, and offer these comments because I, like the others here, want it to be even better.

There seems to be lots of relatively good examples of pivot funtionality available - one that we evaluated (as nothing seemed to be happening with expand/collapse sytle tree grids) was www.flexmonster.com.  Folding something similar to that within an APEX declarative control would certainly bolster the APEX front end capability that seems to have been a bit “left behind” in lieu of backend connectors like JSON and AI sources etc.  Which are all great and help make the product better overall - but I think the front end controls are starting to look a bit dated and other tools start to look more attractive.

Perhaps some new/refreshed/re-imagined components could be discussed/prioritised with the APEX DEV team and discussed with the community and added to the roadmap for a couple of “front end” sprints … 

Regards,

Bren

Thanks everyone for your time and comments (not just on this submission). We take the community input that we get very seriously - even if those of you feel that we don't - but we are limited in time and scope of resources with each release cycle -we really do make efforts to address as much as we can with each new APEX release. 

There are 100's of “low hanging fruit/good/great” idea submissions currently road-mapped (which is GREAT!) - we just cannot hope to catch up on all of them within a couple years - so we must prioritize time, assess risk vs reward, and availability for developer specification creation, development/implementation, QA/testing, documentation, and so forth. It is not easy. Consider also sometimes that certain features will be swept together with the next greatest feature of APEX (there is a lot going, coming soon to future APEX release!)

Let me be clear, though - It is not the “APEX Team” nor the “APEX Developers” nor the “APEX PM's” (certainly not just @vincent morneau nor myself) making decisions to ignore the community asks nor to delay their implementation.

We are guided largely by practicality and strategic initiatives - and I can tell you without question that our management does acknowledge that “We should do better”.

Having said that - it's tough to reply to every one of these comments (even though you all deserve it, and that we'd love to chat 1:1 with each of you!) - so here's one detail we'd like clarification on:

@juancarlos (and all) - I think it is safe to say that the general sentiment is that IG is not suitable for Pivot functionality to be added (the development impact would be too large for this region type), and that IR would be - what specific improvements can be made to IR pivot support that would address your requirements?

@jayson hanes you asked: IR would be - what specific improvements can be made to IR pivot support that would address your requirements?

IR Pivot is very basic, so now 3 years later you are saying you can improve IR (and not IG)  to allow us to do the examples we see here in this idea. Great, do it, just don't take another 3 years.

tks

I'll be (hopefully) constructive and give some ideas on how the IR Pivot could be improved. 

1. A better dialog for defining the metrics of the pivot. The current dialog is very limited and can be confusing. If you look at other pivot tools/libraries out there, they all seems to follow a similar pattern with a list of the fields available and then a series of boxes (usually 4 - Filters, Columns, Rows, Values). You can then drag the fields from the list into the boxes where you want them.  
2. Conditional Cell Formatting  - The current implementation in IR doesn't allow for cell formatting when you're viewing a PIVOT report. Being allowed to format a cell based on the fact that a given data attribute is “<= 2500” would be a huge step forward. 
3. Collapsible / Tree Style Rows & Columns - Right now IR will always show all elements that are selected as “ROW COLUMNS" or “PIVOT COLUMNS”.  If you have more than 1 or 2, it gets ugly really fast.  Having them nested in an expandable/collapsible format would allow a more readable (and drillable) pivot.

There are others. but I think this would be a great start!

These are a great start.  If I could also add being able to sum across a row that would be great.

@doug.gault  - I second Dougs ideas - and specifically option 3 - collapse / expand is something all our users ask for - they are so used to doing that in Excel then can't believe we can't do it.

Regards,

Bren

I think this conversation has gone a bit astray ...
From the above, it seems to me that the APEX team does not understand the task, because they have not worked with a PIVOT table yet. Some of the commenters also keep referring to IR PIVOT, which is just the bottom of a BASIC functionality. I referred to specific, complex implementations above, you just have to click on it and look. But similar functions of Excel or Power Query can also be considered a starting point.

Thanks for the follow-ups. It is very unlikely that any complex approach to pivot requirements will be handled by an APEX enhancement or new component. The 3rd party options via plug-ins are APEX developers' best choice in such regards.

Good morning, everyone, especially @jayson hanes .

I will try to be constructive, and first of all, I want to say that I believe I understand all the positions recently expressed by all of you.

As a developer, I have certain needs or simply pending evolutions, but like me, there are hundreds of developers, each with their own "problems." On the other side, there's APEX, which, in principle, should be the one evolving the tool, following countless priorities that I am not capable of evaluating.

That said, and going back to the beginning, it is obvious that we need some kind of pivot functionality, whether more or less complex... Right now, there is almost nothing available, and we have to start somewhere. I agree that this should be developed on IR or, if the scope is large, I would even consider a new region type specifically for pivots.

What is the maximum I aspire to? More or less what my colleagues have mentioned, but this could take several years. What is the minimum? The ability to create the basic nesting structure, without nesting limits, the possibility to define columns—also without limits—and the ability to define operations within each row.

I mention the limits because even today (in the year 2025), only three columns can be included in dynamic IRs, which I find very restrictive and almost ridiculous.

So, my intention is for this idea to genuinely move forward, for a long-term goal to be set—one as ambitious as you deem appropriate—but for something to start as soon as possible. The sense of abandonment I feel is serious, and it is clear that I am not the only one.

Thank you for listening to me.

Then let's see what the expected functions would be (only those that suddenly come to mind in 5 minutes, the list can be expanded / modified):

Basic:
B1. User can easily modify the structure of the table
B2. Can select the data in the PIVOT
B3. Can easily filter (AND), sort
B4. Can save / load settings
B5. Can set at the row and column level whether to request a summary, and if so, what type (sum, avg, min ...),
B6. Handling subtotals

Advanced:
A1. Defining calculated fields (formulas) (using SQL functions in formulas, e.g. RANK() )
A2. Conditional formatting (e.g. coloring, highlighting, font)
A3. Defining complex filter conditions (OR / AND / subquery)
A4. Support for multiple PIVOT layouts

Master:
M1. Dynamically build the PIVOT base table based on a multi-table star schema/data cube definition
M2. Drill down to a cell value

It might be useful to think about this space in layers in order to simplify the initial requirements.  It's probably easier for the APEX team to think along these lines.

1.  At the core is the ability display data with any column in the row or column position, with columns nested in either position.  For discussion purposes, data is presented to the pivot table as a JSON object.

It is the responsibility of the application to provide all data via the JSON object.  The pivot table does not calculate any data.

1a)  The pivot table display capabilities can be enhanced with cell level formatting (colors, fonts, etc.).  All formatting data is provided by the app as additional properties in the JSON object.

2.  The pivot table is enhanced to support drilling and collapsing.  This requires more information the JSON object, particularly whether a row or column label is drill-able or not. (For those who think in terms of hierarchies, an IS LEAF property). When the user requests a drill or collapse action, there needs to be some sort of a call back to the calling app that lets the app know the desired action (e.g., drill) and the selected value.

It remains the responsibility of the application to respond to a drill/collapse request and provide updated data. 

3.  Here we begin to think of the pivot table as part of a collection of regions and items that work together as provide pre-built components that follow typical UI patterns used by busines intelligence applications.  These are analogous to APEX Faceted Search, where one region provides input to another.

The prime example is a Layout Manager, where users to position columns into positions of the pivot table (row, column, page/filter; nested, of course).

The application remains responsible for generating the data.

4. The most functional pivot tables leverage semantic models and automatically generate code to access data. These are usually dimensional/hierarchical models.  Oracle provides a semantic model with Analytic Views.  Analytic Views provide dimensional metadata, semantics, and includes a SQL generator.

Examples include Excel Pivot Tables and Oracle Analytics pivot table when used with dimensional data sources.  (E.g,, Excel with MS Analysis Services or Analytic Views, Oracle Analytics with a repository, Analytic Views, or Essbase).

Here we can an envision a region or other object that understands Analytic Views, interacts with the Layout Manager region, generates SQL and provides the data (JSON) to the pivot table.  The same could be done with Essbase cubes.  

4a) At some point the Layout Manager adds support for filters.  Maybe this can be done without a semantic model. It's probably much easier with a semantic model.

I'm personally biased against having this pivot table perform calculations ‘on the glass’ due to the possibility that the calculation is not appropriate for the provided data. A classic example if summing or averaging ratios (percent change year ago) on the glass.  It's pretty easy for a user to make a mistake within realizing it.

If the pivot table were ever to get to the point where it offered calcs on the glass, there should be the option to disable calcs.

Bud,

If Oracle already have such controls (as seen in the Oracle Analytics Express demo) - then wouldn't that be a starting point for itnegration into core APEX as a new component type - or is there some oracle inter-department politcs going on there?

A huge organization like Oracle could surely look for and aquire a component from a third party that could be folded into APEX and provide some form of PIVOT functionality?  (e.g. flexmonster) It might not have ALL requirements to match all developers implementations - but surely something is better than nothing?

Regards,

Bren

Yes, Oracle has many man-years of experience and probably millions of lines of code on the subject... but it seems that this knowledge has been lost :-)