Oracle APEX 20.1 IG filters using link
Interactive Grid URL Filtering
Linking to Interactive Grids
It has been a week since APEX 20.1 has been released which gave us some time to upgrade/install or visit apex.oracle.com to check out some of the new features.
Many things to pick from where do you start:
If you by any chance visit apex.oracle.com for more detailed info and read latest release notes there are even more hidden gems in there.
Since many blog posts already covered Mega menus and friendly URLs I wanted to bring to your attention mine most useful feature of APEX 20.1.
Having worked with Interactive reports for a few years now and lately Interactive grids what I really like is that team decided to include feature that has been with us for some time on IRs which many of us found so handy and now it is finally available for Interactive Grids too - you guessed it, it is a URL filtering.
So far we were able to filter our Interactive Grids using exposed JS APIs but now we can do this far simpler and declarative using standard APEX links. Similar to how we were able to do this using Interactive reports
1. Setting filtering option on IG
To create a filter on your interactive grid region using a link we can simply apply similar logic IG<operator>_TARGET_COLUMN.
Place this in the Item Names section, and pass the filter value in the corresponding location in the Item Values section of the URL our grids will be filtered out. Fantastic! Thank you Oracle APEX team.
Sounds more complex than it really is. Create an interactive grid on your page decide a column on which you want to filter on
let's say now I want to create a button that will navigate to this page and automatically filter my report to say list all records where CREATED_BY equals to 'DANY'.
Create new button under Behavior set Action to be redirect to Page in this Application and set these properties:
and if you Run your page your report would be filtered out.
If I wanted to filter rows under Group Size greater than 100 you would do
Notice IGGT_GROUP_SIZE syntax. You get the picture. :)
So what other IG filters can we use. Pretty much same as with IRs before:
2. Link to Saved Reports
Now best comes last what we can also do is link directly to any of our Saved Reports. To do this simply add static ID to your IG region in my case this is emp. Change your link settings to
Many things to pick from where do you start:
- Report Printing generating PDFs directly from your Interactive Grids
- Session Timeout Alerts
- Friendly URLs
- Mega Menus
- Export App as Zip
If you by any chance visit apex.oracle.com for more detailed info and read latest release notes there are even more hidden gems in there.
Since many blog posts already covered Mega menus and friendly URLs I wanted to bring to your attention mine most useful feature of APEX 20.1.
Having worked with Interactive reports for a few years now and lately Interactive grids what I really like is that team decided to include feature that has been with us for some time on IRs which many of us found so handy and now it is finally available for Interactive Grids too - you guessed it, it is a URL filtering.
So far we were able to filter our Interactive Grids using exposed JS APIs but now we can do this far simpler and declarative using standard APEX links. Similar to how we were able to do this using Interactive reports
which enabled us to create/modify filters on IRs for our end users. Now we can do the same with Interactive grids. Let's see how.f?p=100:1:&APP_SESSION.:IR_REPORT_12345::RIR,CIR:IR_ENAME:KING
1. Setting filtering option on IG
To create a filter on your interactive grid region using a link we can simply apply similar logic IG<operator>_TARGET_COLUMN.
Place this in the Item Names section, and pass the filter value in the corresponding location in the Item Values section of the URL our grids will be filtered out. Fantastic! Thank you Oracle APEX team.
Sounds more complex than it really is. Create an interactive grid on your page decide a column on which you want to filter on
Create new button under Behavior set Action to be redirect to Page in this Application and set these properties:
If I wanted to filter rows under Group Size greater than 100 you would do
Notice IGGT_GROUP_SIZE syntax. You get the picture. :)
So what other IG filters can we use. Pretty much same as with IRs before:
EQ = EqualsIf we inspect friendly URL generated for this page it is very easy to understand how this works.
NEQ = Not Equals
LT = Less than
LTE = Less than or equal to
GT = Greater Than
GTE = Greater than or equal to
N = Null
NN = Not Null
C = Contains
NC = Not Contains
IN = SQL In Operator
NIN = SQL Not In Operator
https://apex.oracle.com/pls/apex/lschilde/r/sb-pdf-printing/home?iggt_group_size=100&clear=RR,1&session=XXXXLive example you can see here.
2. Link to Saved Reports
Now best comes last what we can also do is link directly to any of our Saved Reports. To do this simply add static ID to your IG region in my case this is emp. Change your link settings to
Similar again friendly URL would look like:
../home?request=IG[emp]_nostatus&clear=RR,1&session=XXXX
I am not sure if we can link directly to Detailed view or Icon view yet. It may be just something to look forward to in releases to come. ;)
If you need more info how it all works you can check out Sample Interactive Grid application which has been upgraded to include few examples of link filterings. Or visit lates API documentation to explore APEX_IG package and how to do this from withing your PL/SQL code.
Happy APEXing,
Lino
If you need more info how it all works you can check out Sample Interactive Grid application which has been upgraded to include few examples of link filterings. Or visit lates API documentation to explore APEX_IG package and how to do this from withing your PL/SQL code.
Happy APEXing,
Lino
Very nice
ReplyDeleteI want to link to a specific report within an IG, but for some reason it always displays the primary report. For instance if I have 3 reports in an IG ( Primary, TEST1, TEST2 ), I want a direct link to TEST2, according to your blog and oracle's the link should be this:
ReplyDeletef?p=110624:4:708885114544713:IG[IGRID]_TEST2:
But it is not working, can you tell me why?
I have found it, in 20.2 there is a new field called static_id in table apex_appl_page_ig_rpts.
ReplyDeleteif you use that in the url it works (e.g: https://apex.oracle.com/pls/apex/f?p=110624:4:708885114544713:IG[igrid]_199103134 (username demo / password demodemo ).
It works for primary, alternatives and public reports. But not for private ( as it does not have a static_id ).