Narrow result set for APEX IR alternative default reports in query

Here is small tip how utilize APEX_APPLICATION_PAGE_IR_RPT view and APEX_IR.GET_LAST_VIEWED_REPORT_ID function to narrow down interactive report query result for developer saved alternative default reports.

First create interactive report from query

SELECT empno
  ,ename
  ,job
  ,mgr
  ,hiredate
  ,sal
  ,comm
  ,deptno
FROM emp

Go edit region attributes and set Static ID e.g. EMPREP

Next create hidden item. Set name to item and rest of attributes use wizard defaults.

Create computation for hidden item you just created

• Computation point: SQL Query (return single value)
• Computation type: Before Region(s)
• Computation:

  SELECT region_id
  FROM apex_application_page_regions
  WHERE static_id = 'EMPREP'
  	AND application_id = :APP_ID
  	AND page_id = :APP_PAGE_ID
  

  Change EMPREP accordingly your interactive report region static id.

• Condition Type: Value of Item / Column in Expression 1 Is NULL
• Expression 1: {your hidden item name}

Then run page and save four alternative defaults for report without making any changes to report filters.

Now you should have primary and four alternative reports in select list witch all brings same amount rows.

Go back to edit page and edit IR saved reports. Give all Alternative Default reports meaningful alias.

Go edit report query and add where clause. Whole query should be like

SELECT empno
  ,ename
  ,job
  ,mgr
  ,hiredate
  ,sal
  ,comm
  ,deptno
FROM emp
WHERE NOT EXISTS (
  SELECT 1
  FROM apex_application_page_ir_rpt
  WHERE application_id = :APP_ID
   AND page_id = :APP_PAGE_ID
   AND session_id IS NULL
   AND report_id = apex_ir.get_last_viewed_report_id(:APP_PAGE_ID, :Px_HIDDEN_ITEM)
   AND (
    (
     REPORT_ALIAS = 'NULL_COMM'
     AND COMM IS NOT NULL
     )
    OR (
     REPORT_ALIAS = 'SMALL_SAL'
     AND SAL > 1500
     )
    OR (
     REPORT_ALIAS = 'BIG_SAL'
     AND SAL <= 1500
     )
    OR (
     REPORT_ALIAS = 'NOT_NULL_COMM'
     AND COMM IS NULL
     )
    )
  )

Change Px_HIDDEN_ITEM to your hidden item name.

Now when you run page and select from reports select list different report each should bring different result set

See working example.