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

Record navigation: refinement

First off: Dan McGhan, Thanks ;-) I wouldn’t have figured this one out by myself!

Second: my previous posts included the package APEX_IR. It has now been replaced by APEX_IR_PKG because APEX_IR is a new API in apex 4.2!

Finally, the code has been cracked :-) : it IS possible to retrieve the currently active interactive report through SQL! This is actually stored in a preference, residing in wwv_flow_preferences$, but also retrievable through apex_util.get_preference.
Code below is how the report id is retrieved:

IF p_report_id IS NULL THEN
   BEGIN
      SELECT interactive_report_id
        INTO v_report_id
        FROM apex_application_page_ir
       WHERE application_id = p_app_id
         AND page_id = p_page_id;

      apex_debug_message.log_message('interactive report_id: '||v_report_id);

      lv_pref := apex_util.get_preference(p_preference => 'FSP_IR_'||p_app_id||'_P'||p_page_id||'_W'||v_report_id, p_user => p_app_user);
      lv_pref := substr(lv_pref, 1, instr(lv_pref, '_')-1);
      apex_debug_message.log_message(': '||lv_pref);

      SELECT report_id
        INTO v_report_id
        FROM apex_application_page_ir_rpt
       WHERE application_id = p_app_id
         AND page_id = p_page_id
         AND base_report_id = lv_pref
         AND session_id = p_session_id;
   EXCEPTION
      WHEN no_data_found THEN
         apex_debug_message.log_message('no IR id could be found. Check input parameters. -> end');
         RETURN;
   END;
ELSE
   v_report_id := p_report_id;
END IF;

The returned preference value could look like ‘3522014783654717____X’. We’re only concerned with the first value as this is the id of the currently active report. Note that the preference name uses a report id: this is the id of the interactive report itself and not of a saved or session instanced version. The id we get from the preference is the id of the SAVED version of the report that is instanced for the session of the user. If you take a look at how the ids and saved reports work below you’ll understand.

There is a lot of writing I have done already on these report structures, and there is more in the comments in the package, but here is another illustration:

APEX_APPLICATION_PAGE_IR
Metadata for Interactive Reports
Interactive Report Id:
Region Id:

APEX_APPLICATION_PAGE_IR_RPT
Metadata for saved reports and session instances of those
For example (from my sample app on apex.oracle.com):

•    Reports with no session_id are saved reports.
•    All reports are based on the same interactive report
•    All reports have a unique id: REPORT_ID
•    Default (Primary) and alternative have an own application user. Named reports use the name of the creator evidently. Note that in my example app the report "Only Space Two" is a PRIVATE report! You'll see this of course when you log in with the test user.
•    My own instances of the reports are in the view aswell. You can identify them because they have a session_id, base_report_id and their report_type.
•    The base_report_id is the report_id of the report it is based on. "Report it is based on": the report_id of a saved version of the interactive report


Debugging from sql command line is still possible. There are two changes in the parameter list: p_app_user and p_report_id have been added. App_user is straightforward, but p_report_id is not. Leaving p_report_id blank from a command line to resolve the report_id is not possible. The preference fetch will only work in an apex session. You’ll have to retrieve the id yourself.
To do this, basically take the code block above, but query wwv_flow_preferences$  instead of using apex_util.

SELECT attribute_value
FROM apex_040100.wwv_flow_preferences$
WHERE user_id='YOUR_APEX_USERNAME' 
AND preference_name like 'FSP_IR_130_P11%'

Use preference_name like 'FSP_IR_130_P11_W5555555555' if you have retrieved the base interactive report id (that’d be from apex_application_page_ir). And of course, substitute the application and page id!

DECLARE
   v_next      VARCHAR2(50);
   v_prev      VARCHAR2(50);
   v_top       VARCHAR2(50);
   v_bot       VARCHAR2(50);
   v_cur_tot   VARCHAR2(50);
   v_debug     VARCHAR2(5000);
   v_binds     DBMS_SQL.VARCHAR2_TABLE;
   v_binds_val DBMS_SQL.VARCHAR2_TABLE;
BEGIN
    v_binds(1) := 'P52_COMPROD_ID';
    v_binds_val(1) := '106672'; 

    apex_ir_pkg.get_navigation_values
   (
      p_app_id      => 190,
      p_session_id  => 4133013019922250,
      p_column_id   => 'ID', --column id for IR!
      p_value       => 153876,
      p_page_id     => 52, 
      p_report_id => 55555555555555,
      p_app_user => ‘YOUR_APEX_USERNAME’, --APP_USER in apex session
      p_use_session_state => FALSE,      
      p_binds       => v_binds,
      p_binds_val   => v_binds_val,
      p_next        => v_next,
      p_prev        => v_prev,
      p_top         => v_top,
      p_bot         => v_bot,
      p_cur_tot     => v_cur_tot,
      p_debug       => v_debug 
   );   

   dbms_output.put_line('v_next: '||v_next);
   dbms_output.put_line('v_prev: '||v_prev);
   dbms_output.put_line('v_top: '||v_top);
   dbms_output.put_line('v_bot: '||v_bot);
   dbms_output.put_line('v_cur_tot: '||v_cur_tot);
   dbms_output.put_line('v_debug : '||v_debug );
END;

Sample application:  http://apex.oracle.com/pls/apex/f?p=54687:30

Download: HERE