Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Geting Apex pivot SQL

Robert_D.Mar 14 2017 — edited Mar 15 2017

Hello All,

I need to get SQL from pivoted IR. Function apex_050100.get_report_sql, is returnig only base report SQL, not pivoted one. 

I work on Apex 5.1.

DECLARE

   v_sql   VARCHAR2 (4000);

BEGIN

   v_sql := get_report_sql (<app_id>,<page_id>, FALSE);

   dbms_output.put_line(v_sql);

END;

Comments

Sven W.

There is no function get_report_sql. I assume this is just a kind of wrapper for APEX_IR.GET_REPORT.

https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_ir.htm#AEAPI29379

Mike Kutz

Dynamic Pivots are best performed within the Display Tier.

For a web site, this means "The Browser".  Example: APEX IR Pivot

For a report, the pivot is defined by the Report Template.

For a file, this needs to be performed by the software reading the file.  (eg Excel)

What is your Business Requirement?

MK

Robert_D.

I need this SQL, to use it during export report to excel via my custom plugin.

Mike Kutz

Robert_D. wrote:

I need this SQL, to use it during export report to excel via my custom plugin.

You do realize that the pivoting is being done with JavaScript in the Display Tier?

(ie your browser is doing the Pivoting, not the database)

If you want to "simulate" that in Excel, then train the end-users to "click the right buttons" in Excel.

pastedImage_1.png

Your other option:

Beyond that, you will need to use a Report Generation Engine that can create the pivoted Excel file.

I'm sure BI Publisher can. Jasper Reports might be able to do it. I'm not sure of the capabilities of either; I haven't used them before.

Again, dynamically Pivoting data is for the Display Tier, not the Database Tier

MK

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 12 2017
Added on Mar 14 2017
4 comments
427 views