How to Auto filter via URL in Interactive report | ORACLE APEX?
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.
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 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
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
| 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
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
