Forum Stats

  • 3,872,737 Users
  • 2,266,464 Discussions
  • 7,911,289 Comments

Discussions

Geting Apex pivot SQL

Robert_D.
Robert_D. Member Posts: 92
edited Mar 15, 2017 12:14PM in APEX Discussions

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;

Tagged:

Answers

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Mar 14, 2017 2:00PM

    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
    Mike Kutz Member Posts: 6,251 Gold Crown
    edited Mar 14, 2017 3:07PM

    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.
    Robert_D. Member Posts: 92
    edited Mar 15, 2017 3:39AM

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

  • Mike Kutz
    Mike Kutz Member Posts: 6,251 Gold Crown
    edited Mar 15, 2017 12:14PM
    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

This discussion has been closed.