I have an interactive report that runs a large query, which takes about 20 seconds to execute.
There are many filter items on the APEX page, which affect the data in the report.
The report uses a PL/SQL query, which references the items on the page, to pull the data.
Since most users need to set multiple filters before submitting, I created a Run Report button which submits and refreshes the page, so users don’t have to wait 20 seconds multiple times while they set multiple filters.
The problem is, some of the filters are select lists.
And these select lists are populated based on PL/SQL queries, which pull data from the database, and also reference values in other items on the APEX page as part of the queries.
So when the user selects an item in one of the select lists, I need to submit the item that was “changed” and then refresh the other filter items on the page, but WITHOUT refreshing the whole page/report.
Ajax should be able to do this, right?
How do you accomplish this?
I currently have dynamic actions that refresh individual items, and this seems to be working.
But it doesn’t pull the correct data from the database to populate the select lists, because it doesn’t submit the other filter items first.
Advertisement
Answer
So when the user selects an item in one of the select lists, I need to submit the item that was “changed” and then refresh the other filter items on the page, but WITHOUT refreshing the whole page/report.
That should be fairly simple as Select List items, under the “List of values” group of properties, contain Cascade LOV Parent item(s) property. It is used for the purpose you described.
For example, if there were select list items P1_CONTINENT, P1_COUNTRY, P1_CITY, then – when you select a continent, list of countries displays only countries on previously selected continent. You’d then put P1_CONTINENT into P1_COUNTRY list item’s “Cascading LOV Parent item(s)” and that’s all.