Interactive report Auto filter via URL ORACLE APEX Part 1

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.

  1. Resetting the Interactive report
  2. Resetting Peginations
  3. applying filters

To achieve above we need to understand the APEX URL.

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

APEX URL Syntax

f?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 ClearCacheitemNames 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
http://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. 

 Lets apply filter on CUST_FIRST_NAME with value Albert

http://127.0.0.1:1802/apex/f?p=102:2:16440472847336::::IR_CUST_FIRST_NAME:Albert

 Let’s try with another column CUST_CITY with value Flushing

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

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

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

 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

ACCOUNTINGRESEARCH
EQEqual to (Default)
LTLess than
GTGreater than
LTELess than Equal to
GTEGreater than Equal to
NEQNot Equal to
LIKELike
NNUll
NNNot null
CContains
NCDoes Not contains
  • Filter with Null operator : PHONE_NUMBER2 with NULL value
http://127.0.0.1:1802/apex/f?p=102:2:16440472847336:::RIR:IRN_PHONE_NUMBER2:
  • IR_ROWFILTER: Filter on row with text Albert
http://127.0.0.1:1802/apex/f?p=102:2:16440472847336:::RIR:IR_ROWFILTER:Albert

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

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