1) A select list dependent on another select list
2) A report the updates based on the selections.
The original post submitted the page after each select change - APEX 4.x makes this unnecessaary thanks to cascading select lists.
And the report can also be refreshed without submitting the page thanks to Dynamic Actions.
So a solution can be formed declaratively without submitting the page, we just need logical SQL to go with it.
|Open the sample page, modify the Dept list and see how Emp list changes.|
Change the employee to see Dynamic action refresh report based on selection.
The final result doesn't have many components.
|Page rendering properties|
select dname, deptno from dept
The P25_EMP item has a 'Cascading LOV Parent Item' of "P25_DEPT", and LOV which ensures employees are shown even with no dept selected.
select ename, empno from emp where deptno = :P25_DEPT or :P25_DEPT is null
I've enabled display of null values in both select lists.
My report SQL is
select empno, ename, job from emp where job = (select job from emp where empno = :P25_EMP) or :P25_EMP is nullwith 'Page Items to Submit' as "P25_EMP", and I've ensured 'Enable Partial Page Refresh' report attribute is Yes.
The dynamic action fires on Change of P25_EMP, and refreshes the report region.
Simple, effective use of declarative APEX features - so you can spend more time on stuff like jQuery ;-)