Interactive report Auto filter via URL ORACLE APEX Part 1

Share on:

How to Auto filter via URL in Interactive report | ORACLE APEX?

Preview

This is a series of two articles on Interactive report filters

Interactive report supports much more than it’s interactive features. Let see what we can do to Interactive via URLs.

  • Resetting the Interactive report
  • Resetting Peginations
  • applying filters

To achieve above we need to understand the APEX URL.

1http://127.0.0.1:1802/apex/f?p=101:77:7068458914237::NO:::

APEX URL Syntax

1f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly
  • 127.0.0.1 is the URL of the server
  • 1802 is port number
  • apex is the database access descriptor (DAD) name. The DAD describes how HTTP Server connects to the database server so that it can fulfill an HTTP request. The default value is apex.
  • f?p= is a prefix used by Oracle Application Express
  • 101 is the application ID
  • 77 is the page ID within the application
  • 7068458914237 is the session number

In URL Syntax ClearCache, itemNames and ItemValues are useful to achieve all the above bullet points. Now let’s under stand the Interactive Report cache using it’s attributes.

  • RIR: To Reset Interactive Report’s primary report
  • RP: To Reset Interactive Report’s pagination.
  • CIR: To Reset Interactive Report’s primary report with columns

Now try out the Interactive report attributes

  • Resetting
1http://127.0.0.1:1802/apex/f?p=101:36:7068458914237:::RP,RIR,36:

In the above url we are resetting the primary report and pagination and page cache.

  • Filters We have report on a page as below and going to apply filters. Preview Lets apply filter on CUST_FIRST_NAME with value Albert
1http://127.0.0.1:1802/apex/f?p=102:2:16440472847336::::IR_CUST_FIRST_NAME:Albert

Preview Let’s try with another column CUST_CITY with value Flushing

1http://127.0.0.1:1802/apex/f?p=102:2:16440472847336::::IR_CUST_CITY:Flushing

Preview

Hang on we got an issue, we did not clear the last filter. Let’s do that and see.

1http://127.0.0.1:1802/apex/f?p=102:2:16440472847336:::RIR:IR_CUST_CITY:Flushing

Preview As we can see in above examples filter are working like equal to operators. Let see all other operators in Interactive report

Interactive report filter Operators

ACCOUNTING RESEARCH
EQ Equal to (Default)
LT Less than
GT Greater than
LTE Less than Equal to
GTE Greater than Equal to
NEQ Not Equal to
LIKE Like
N NUll
NN Not null
C Contains
NC Does Not contains
  • Filter with Null operator : PHONE_NUMBER2 with NULL value
1http://127.0.0.1:1802/apex/f?p=102:2:16440472847336:::RIR:IRN_PHONE_NUMBER2:

Preview

  • IR_ROWFILTER: Filter on row with text Albert
1http://127.0.0.1:1802/apex/f?p=102:2:16440472847336:::RIR:IR_ROWFILTER:Albert

Preview

Working with multiple reports on page

If we have multiple reports on the page we have to define the report region Static ID. IR[region static ID] is used to pass the static ID.

Now Let’s create two reports on page with different Static IDs and try out filters

1http://127.0.0.1:1802/apex/f?p=102:2:16440472847336:::RIR:IR[REPORT_1]_ROWFILTER,IR[REPORT_2]_CUST_FIRST_NAME:Albert,Albert

Preview

You might also like: