Applying a column based condition in an interactive report on first page load in Oracle APEX

Question

I have a requirement in Oracle Apex's Interactive report. At first load, only that data should appear whose Operation is not deleted. This should also reflect in the export of the report. However, they want to provide an option that is user should be able to see the deleted operation data when they require. Please note, there is no Operation filter on the page. Can anyone please help. Thanks in Advance.

Answer 1

• Add a page item P1_SHOW_DELETED to the page. Set the page item value to default "N" (or set it to "N" with a computation before regions). Use "Select List" and give it values "Yes" and "No" (return values Y and N)
• Update your query to use the page item. Make sure you have P1_SHOW_DELETED in "Page Items to Submit" attribute of the interactive report.

SELECT <columns>
  FROM <tables> 
 WHERE <existing where clause> AND operation_deleted = :P1_SHOW_DELETED

At this point the delete rows will no be shown on page load - test and confirm.

• Add a dynamic action on P1_SHOW_DELETED on change with a true action of type "Refresh" and region = your interactive report.

--- UPDATE ---

For multiple values for the operation column and you want to exclude/included the value "deleted", then this is an option.

SELECT <columns>
  FROM <tables> 
 WHERE <existing where clause> AND 
       (('Y' = :P1_SHOW_DELETED AND operation = 'Deleted') OR (operation != 'Deleted'))