Interactive Report Quick Filter: show all columns

Normally, the quick-filter column selection would not allow you to pick a non-displayed column to filter on. You could however apply a filter through the Actions > Filter menu.

If applying a contains-filter through the filter menu is no problem, why would it be through the quick filter? So i tapped into the code that fetches the columns for the dropdown and replaced it with an own call to a function returning me ALL the columns. That's it. The original code to apply the filters is still there, and it obviously has no issue applying an IR filter for a non-displayed column.

An example can be found at: http://apex.oracle.com/pls/apex/f?p=17948:1

This IR has 2 columns not shown.

• COMM is a HIDDEN column
• DEPTNO is a non-displayed column

 

 Install and setup

To install the plugin, go to your application, select “Shared Components”, then go to “Plugins”. From there select “Import”, and browse to the sql file.

To use it on a page, create a dynamic action on your page for the “Page Load” event. As true action you can find the plugin under the “Initialize” group.

Technical

$("#apexir_SEARCHDROPROOT").removeAttr("onclick").click(

$("#apexir_SEARCHDROPROOT") is the looking glass icon. The generated onclick has to go and is replaced with an own handler.

In this handler an ajax call is made to an ajax function specified in the plugin. It doesn’t do too much:

apex_util.json_from_sql(q'!
  select 'All columns' D, '0' R, '0' C
    from dual
   union all
  select sys.htf.escape_sc(report_label) D, column_alias R, '1' C
    from apex_application_page_ir_col 
   where application_id = :APP_ID
     and page_id = :APP_PAGE_ID
     and interactive_report_id = !' ||l_ir_base_id
);

This bit of code will fetch all the columns defined for the IR, joined with the ‘All columns’ entry also found in the default dropdown. I got the markup from inspecting the ajax calls made by the IR when it retrieves the columns.

Ajax success handler:

function(data, status, obj){
                             p = obj;
                             if(gReport){
                                gReport.l_Action = "CONTROL";
                                gReport.current_control = "SEARCH_COLUMN";
                                gReport._Return(obj);
                             };
                          }

This is probably the most interesting. The returned object is stored in “p”, a global variable used by apex in its ajax processes.

gReport is the javascript variable created and instantiated by apex for the interactive report functionalities. L_Action and current_control are variables used in the ajax calls to determine what is asked for and what should happen. _Return is a function that would normally handle the IR ajax success callback.

So effectively I’m making the ir javascript think that it has just put out a call to retrieve the columns, and it should now handle the return (which is obj). The assignment of obj to p is still necessary because some checks are made against that variable aswell. From obj the responsetext is actually the most important.

Download: HERE