Interactive Report – Planet of the APEX

I recently developed a tool that would allow users to select a data view and use the built in tools of an Interactive Report to sort, filter, and download the data. The different views available to the users have a different number of columns and different column headings, so the display of the report had to be dynamic.

In comes collections again. To accomplish this takes a little bit of time but it is actually quite simple and once it’s setup you can view any data view in the report as long as it contains 50 columns or less.

First you’ll need a select list to display the list of data views and an interactive report. Set the Page Action on Selection for the select list to Submit Page and create a page process to create your collection:

DECLARE
 v_sql VARCHAR2(2000);
BEGIN
 IF :P1_VIEW_NAME IS NOT NULL THEN
   v_sql := 'select * from ' || :P1_VIEW_NAME;
   IF apex_collection.collection_exists 
    (p_collection_name => 'EXTRACTION_VIEW') THEN
     apex_collection.delete_collection (p_collection_name => 'EXTRACTION_VIEW');
   END IF;

   IF NOT APEX_COLLECTION.COLLECTION_EXISTS 
    (p_collection_name => 'EXTRACTION_VIEW') THEN
     APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
     p_collection_name => 'EXTRACTION_VIEW',
     p_query => v_sql);
   END IF;
 END IF;
END;

Set your source for the interactive report:

select *
from apex_collections
Where collection_name = 'EXTRACTION_VIEW';

Here’s the tedious part. You’ll need to create a page item for each of the 50 column headings. I named mine PX_C0XX_HEADING. Then in your interactive report you’ll need to do two things:

Set the Column Heading for each column to the corresponding page item (for example &P1_C001_HEADING.)
In the Conditional Display for each column, set the condition type to Exists (SQL query returns at least one row) and enter the following, substituting the appropriate column name:

select 1
from apex_collections
where c0XX is not null
and collection_name = 'EXTRACTION_VIEW';

The final piece is to populate the column heading page items. I created a database function to determine the column name:

CREATE OR REPLACE FUNCTION get_extraction_column_heading (p_view_name IN VARCHAR2
 , p_data_type IN VARCHAR2, p_column_id IN NUMBER) RETURN VARCHAR2 IS
  CURSOR get_column_heading_c IS
  SELECT column_name
  FROM all_tab_columns
  WHERE table_name = p_view_name
  AND column_id = p_column_id;

  v_column_name all_tab_columns.column_name%TYPE;
BEGIN
  OPEN get_column_heading_c;
  FETCH get_column_heading_c INTO v_column_name;
  CLOSE get_column_heading_c;

  RETURN v_column_name;
END;

Then create an After Header computation for each column heading page item to call the function:

get_extraction_column_heading(:P1_VIEW_NAME, 'VARCHAR2', 1);

The number for the last parameter (P_COLUMN_ID) will correspond to the number of the column heading.

Note: You may want to consider putting the same condition that you put on your columns on the computations for the column headings as well to eliminate unnecessary database calls. I did not have any issues with this on my production application, but when creating the demo app I recieved numerous I/O errors which resolved after putting the condition on the computations.

You can access the demo application here (credentials are guest/demo): Dynamic IR Column Headings Demo

Tag: Interactive Report

Displaying text with carriage returns

An Interactive Report with Dynamic Column Headings