Multirecord Master-Detail Report

A common requirement is to show a Master-Detail Report where both the Master as well as the Detail Report show multiple records. When you click on one of the Master records, the connected detail records are shown as well.
This is very easy to accomplish with a Hidden item and a Dynamic Action.
First the Master Report, for this example I am using the DEPT table:

select d.deptno
      ,d.dname
      ,d.loc
  from dept d

The detail report will consists of the employees which belong to the DEPT record which is clicked by the user.

select * from emp
 where deptno = :P9_DEPTNO

You will notice that there is a reference to P9_DEPTNO in the query, that will be the hidden item.
So there are two reports and a hidden item on the page.
The user will need to click on something, so the DEPTNO column from the first report will act as a link. Navigate to the column and fill in the section labelled "Column Link".

For the Link attributes fill in the following information:

 
onclick="return false;" class="show-employees" id="#DEPTNO#"

Now all components are in place, time to create the Dynamic Action.
The Dynamic Action will respond to a click on the master report(DEPT). The link column has a class attribute of "show-employees" which act as the jQuery Selector. Fill in the details when the Dynamic Action needs to fire as follows:

What does the Dynamic Action need to do? First it needs to set the value of the clicked DEPTNO in the hidden item. This can be done with a little bit of javascript:

apex.item( "P9_DEPTNO" ).setValue( this.triggeringElement.id );

The second part of the Dynamic Action is to refresh the Employees report. Add a TRUE action to the Dynamic Action where you specify "Refresh Region" and choose the details report (Employees).
You can find the demo right here.