Building IR filters with AJAX - not solved yet

Von Tobias Arnhold 3.17.2010
I got stuck on a problem with Interactive Reports.
I wanted to build filters during the application runtime with AJAX. These filters should be created if an user makes changes with select lists or trees similar to my old example:
IR with dynamic rules

What I need now are exact filters on hidden or visible fields:


My idea was to use the apex_util.ir_filter procedure:
apex_util.ir_filter(
p_page_id in number, -- Page that contains an Interactive Report
p_report_column in varchar2, -- Name of the report SQL column to be filtered
p_operator_abbr in varchar2 default null, -- Filter type
p_filter_value in varchar2); -- Value of filter, not used for N and NN

With an application process:
-- application process AP_GET_CAT
declare
  V_CAT varchar2(100);
begin
 select c.cat_name into V_CAT from categories where c_id = :P1_C_ID;

 apex_util.ir_filter(
  p_page_id=>1,
  p_report_column=>'CAT_NAME',
  p_operator_abbr=>'EQ',
  p_filter_value=>V_CAT);

 htp.prn(V_CAT);
end;

and some javascript:
// js get cat data
function set_cat(v_id) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=AP_GET_CAT',0);
  get.add('P1_C_ID',v_id)
  $x('P1_C_ID').value = v_id;
  gReturn = get.get();
  get = null; 
}

The functionality itself worked well but a submit is required for the APEX procedure apex_util.ir_filter.

My other idea was to imitate the user actions with javascript only:
// js code: set IR filter
$x('apexir_COLUMN_NAME').value = 'CAT_NAME';
$x('apexir_STRING_OPT').value = '=';
$x('apexir_EXPR').value = $x('P1_CAT_NAME').value;
gReport.column.filter();

Unfortunately it doesn't work this way... But it feels close to a solution.

It works if I use a sql where clause for my IR like this:
SELECT s_id, sales, sales_info
FROM sales, categories
WHERE s_c_id = c_id
AND s_c_id = :P1_C_ID;

and some javascript:
// not tested
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=AP_GET_CAT',0);
get.add('P1_C_ID',v_id)
$x('P1_C_ID').value = v_id;
gReturn = get.get();
init_gReport();gReport.reset();gReport.pull();
get = null; 

Maybe one of you guys did already solve this task and could help me out here.

Forum entry: http://forums.oracle.com/forums/thread.jspa?messageID=4167521

Tobias

3 Comments " Building IR filters with AJAX - not solved yet "

Martin Giffy D'Souza 18 March, 2010 15:12

Hi Tobias,

Great start on the issue. After running your AP, could you not automatically trigger the IR to refresh by executing the "go"?

Add the following at the end set_cat:


gReport.search('SEARCH');

This will trigger an IR refresh. I haven't tested it, so no grantees. If you wanted to preserve the search field, you could store the value before and put it back in after the called to gReport.search.

Martin

Tereska 08 April, 2010 03:10

Tobias,

Great blog, thank you, I'm stopping by from time to time :)
I've already answered you on the forum, but here again: either like Martin suggested

gReport.search('SEARCH');
or
gReport.pull();

I tested it. It works.

Best,
ter

Tobias Arnhold 08 April, 2010 07:49

Thanks Martin and Tereska! I didn't got the idea! But I had the thought that I was close. :D