APEX 5 Reset IR w Multiple IR on Page

With APEX 5 Interactive Reports (IRs) we have the luxury of having multiple IRs on the same APEX page.

That is wonderful, until it comes time to Reset a particular IR. The usual RIR and CIR syntax operates on ALL IRs on the page. Probably not what you want.

To refresh a single IR, use the standard APEX_IR API REFRESH_REPORT procedure.

Briefly:

  • Add a Static ID to your IR. As best practice, do this for all your IRs.  The Static Id attribute is under the Advanced attribute section.
  • Create a button to trigger the IR refresh. Set the Action to Defined by Dynamic Action.
  • Create a dynamic action, on Click of your new  button.
  • Add two True events, one Execute PL/SQL for the APEX_IR.REFRESH_REPORT call, one Refresh to refresh the IR region.
  • The PL/SQL calls APEX_IR.RESET_REPORT:
DECLARE
    v_region_id apex_application_page_regions.region_id%type;
BEGIN
 -- get the IR region id
 SELECT region_id 
   INTO v_region_id
   FROM apex_application_page_regions
  WHERE application_id = :APP_ID
    AND  page_id = :APP_PAGE_ID
    AND  static_id = 'my-region-id'; -- use the Static Id set in the IR Advanced attribute section
    
 APEX_IR.RESET_REPORT(
   p_page_id => :APP_PAGE_ID,
   p_region_id => v_region_id,
   p_report_id => NULL );   -- resets the last-used report
END; 

If you need to reset a particular, saved report, you will need to query for that report_id and enter it instead of NULL in the APEX_IR.RESET_REPORT call.

The Refresh True action is a simple Refresh on your IR Region.

Be sure to uncheck the Fire on Page Load option for both True actions.

That’s it!

Leave a Reply