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


Comments

  1. Very Good information. I saw that they are all strings, we can do something to deal also with numeric numbers? Thank you

    ReplyDelete

Post a Comment