Dynamic Interactive Report (Variable number of columns)

Problem

You want to create an interactive report based on a dynamic query. In my case I wanted to compare RICEW versions across X number of environments.

Solution

There are 2 good articles on this (see acknowledgements).

Approach.
1. create a function that returns a query as string
2. create a before header process that creates a collection based on the query string
3. create an interactive report based on the collection
4. create page items to map column headers and a process to set the column headers
5. Create display conditions for the report columns

1. create a function that returns a query as string

Doesn't matter what the quesry is. Mine ended up being quite complicated.

2. create a before header process that creates a collection based on the query string

Type is PL/SQL Code:

begin

  if apex_collection.collection_exists

        ( p_collection_name => 'RICEW_DIFFERENCES' )

  then

    apex_collection.delete_collection

      ( p_collection_name => 'RICEW_DIFFERENCES' );

  end if;

  

  :P1_DIFFERENCES_QUERY :=  ds_deployment_utils_pkg.get_ricew_versions_query('N',:APP_BRANCH_ID,'Y');

  

  apex_collection.create_collection_from_query

    ( p_collection_name => 'RICEW_DIFFERENCES'

    , p_query           => :P1_DIFFERENCES_QUERY

    );

end;

I chose to put my query in a variable to make it easier to debug.

3. create an interactive report based on the collection

In the original articles, they just say "select * from apex_collections..."

..but I knew I would never need all of those columns. I needed 5 columns, but coded 20 to be ultra safe. So I create an interactive report with this query.

SELECT c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020

FROM   APEX_collections

WHERE  collection_name = 'RICEW_DIFFERENCES'

4. create page items to map column headers and a process to set the column headers

I was stuck with this one for a while. 

First I created a hidden region on my page, and I put 20 text items on it named C001 to C020.

You could have put those 20 items anywhere on the page as long as you hid them, I though it neater to have them in their own region.

Then another process for giving them values. The values will become your column_names.

declare 

 cursor c_columns

  is select 'RICEW_NUMBER' column_name, 0 deployment_sequence from dual union

     select environment_name COLUMN_NAME, deployment_sequence

     from apeX_ebs_extension.DS_ENVIRONMENTS_v

     where branch_id = :APP_BRANCH_ID

     order by deployment_sequence;

  

  type column_rec is record ( column_name varchar2(30), deployment_sequence number );

  type columns_table is table of column_rec;

  t_columns columns_table;

begin 

  open  c_columns;

  fetch c_columns bulk collect into t_columns;

  close c_columns;

  

  for i in 1 .. t_columns.count

  loop

  

    APEX_UTIL.SET_SESSION_STATE('PC0'||lpad(i,2,0),t_columns(i).column_name);                           

  end loop;

  

  exception when others

  then

    if c_columns%isopen

    then

      close c_columns;

    end if;

end;

Now go to each of your columns in your report and set the header to be equal to the value of it's hidden page item counterpart.

Note the Ampersand and the period after the variable name 

Example Column C001 has a "Heading" property of &PC001.

5. Create display conditions for the report columns

If you don't do the next bit, you'll have a lot of columns hat you don't need. (20 in fact). So we now laboriously add a condition to each column in the report.

(Changing the C001 to C00x as you work your way through each column).

Result

Even though I catered for 20 column, in this case I'm coming back with 4. I knew that my first would be RICEW_NUMBER, and then who knows how many environments...

Acknowledgement

http://vincentdeelen.blogspot.co.uk/2014/02/interactive-report-based-on-dynamic-sql.html

https://wikis.web.cern.ch/wikis/pages/viewpage.action?pageId=96436849#InteractiveReport&DynamicQueries-Putcolumnnamesonpageitems