APEX 101: Cascading Select Lists & Dynamic Actions

This post was inspired by a question on the OTN APEX forum, which contains requests for two fairly common scenarios:
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

The P25_DEPT LOV definition is simply
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 null

with '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 ;-)