Oracle Apex 5.0: Wait to generate IR until criteria fields entered

0
votes

I have an APEX 5.0.3.00.03 page with dozens of criteria fields, followed by a region containing an Interactive Report (IR). I want to defer the IR being produced until a "Generate" button is clicked, but I am having troubles. The report without criteria would generate millions of rows, via a nasty 5 table join, so I want to defer it processing until the user is ready. I know that I can do a /*+ FIRST_ROWS */ hint, but still want to avoid invoking the SQL until the user picks some criteria.

I tried the following:

Created a Hidden Field P210_WAIT, initial value of 'TRUE', which I included in the SQL criteria like this:

select * from tab_1, tab_2, tab_3, tab_4, tab_5
where :P210_WAIT != 'TRUE' 
and  -- join, and dozens of other criteria here

and in a dynamic action on the Generate button, I do two things:

  1. Execupte PL/SQL Code to set :P210_WAIT := 'FALSE';
  2. Refresh Action:
    • Action: Refresh
    • Affected elements: Selection Type: Region. Region: (region containing my IR)

I have debug statements in the PL/SQL code, and they are appearing, but the SQL to produce the report is not being run, implying that the Refresh Action is not "waking up" the IR.

It does appear the IR SQL is being executed on page load, as I was dynamically setting the IR Attribute "When No Data Found" value to initially say "Please select criteria and click the Generate button to display report" and that was working.

1
oracleoracle-apex-5

1 Answers

1
votes

If I understand your problem correctly, your main problem here would be: the dyanamic action to refresh the region is not working, am i correct?

If that is the problem, try this:

  • First, make sure that all the bind variables in the source query is listed on the Page Items to Submit under the source query region.

  • Then, Assign Static ID for the IR region.

  • Next will be, changing the Refresh dynamic action to Execute Javascript and paste this

     $('#static_id').trigger('apexrefresh');

Hope this helps.