Geting Apex pivot SQL

Robert_D. · Mar 14, 2017 5:38AM

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;

Sven W. · Mar 14, 2017 1:59PM

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

Mike Kutz · 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. · Mar 15, 2017 3:39AM

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

Mike Kutz · 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.

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

Forum Stats