APEX

Hello,

I have a requirement to create pivot financial report using interactive report. I have managed to create a function retutning the dynamic query for the pivot report as follows:

CREATE OR REPLACE FUNCTION
fnc_pivot
RETURN VARCHAR2 AS
CURSOR cur_qry IS
SELECT DISTINCT ', SUM (CASE WHEN financial_year = '''
|| financial_year
|| ''' ' AS case_st
, 'THEN annual_allocation END) AS '
||'"'
|| financial_year
||'"' AS case_head
FROM finance_table
ORDER BY case_head;

l_query VARCHAR2(32000) DEFAULT NULL;
BEGIN
FOR i IN cur_qry
LOOP
l_query := l_query
||i.case_st
||i.case_head
||CHR(10) ;
END LOOP;
l_query := 'SELECT id'
||l_query
||', SUM(annual_allocation) AS'
||'"'
||' Grand Total'
||'"'
||'FROM finance_table '
||'GROUP BY id';

RETURN(l_query) ;

END;

Which returns the following query:

SELECT fin._id, SUM (CASE WHEN fin.financial_year = '2007/8' THEN fin.annual_allocation END) AS "2007/8"
, SUM (CASE WHEN fin.financial_year = '2008/9' THEN fin.annual_allocation END) AS "2008/9"
, SUM (CASE WHEN fin.financial_year = '2009/10' THEN fin.annual_allocation END) AS "2009/10"
, SUM (CASE WHEN fin.financial_year = '2010/11' THEN fin.annual_allocation END) AS "2010/11"
, SUM (CASE WHEN fin.financial_year = '2011/12' THEN fin.annual_allocation END) AS "2011/12"
, SUM (CASE WHEN fin.financial_year = 'Future Years' THEN fin.annual_allocation END) AS "Future Years"
, SUM(annual_allocation)
FROM financial_table fin
GROUP BY fin.id

And this are the results




ID 2007/8 2008/9 2009/10 2010/11 2011/12 Future Years Grand Total
CHWP01L201 0 121 541 2545 1454 5454 3545454
CREP01L201 80000 654534 45654 4545 545 54545 212545



So if I create a normal SQL report (PL/SQL function returning query) it works splendidly but as we can't do the same in Interactive report I have created an apex_collection based on the above function.

Here's my problem, as the columns are dynamic I can't just go and create a report based on 'Select * from apex_collection' which returns all 50 apex_collection columnswhether they have data or not. And even if I do ' Select c001, c002, c003 .... FROM apex_collection' the headers are 'c001, c002... rather then the financial years that I require.

Has anyone come across similar issue? Is there a solution to this problem or should I just wait hoping that the next version of Apex has this feature (and no, Oracle peeps, no sarcasm here, just resignation after day of head breaking with no solution).

Any help will be much appreciated.

Best regards,
Sophia