Interactive report Auto filter via URL | ORACLE APEX (1\2)

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.

Default Interactive Report

Let 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

Filter on CUST_FIRST_NAME

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

Filter on CUST_CITY

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

Filter on CUST_CITY with RIR

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

Operator	Description
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:

Filter on 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

IR_ROWFILTER

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

IR Filter with multiple reports

Interactive Report filters using APEX_IR package Interactive report Auto filter using APEX_IR package | ORACLE APEX (2\2)