Linking one apex report to values in another

I've been trying (for a while) to get some (what i though was simple) apex functionality to work - what i wanted was a classic report at the top of the page with a second classic report just below it. The second report's data would be based on a value clicked  in the top section. So a screen that looked something like this:

If i click on the 'click me' link the data displayed in the bottom section returns only rows relative to the id column i clicked next to. So if i click on the row with an id of 1 the report at the bottom shows me rows relative to that id.

The query is essentially then (in the top section)

select  DBID
,null As link
 from SUMM

Followed by (in the detail section)

select * from det where dbid= (value picked in the top section).

Now that sounds like a pretty simple requirement right? Well it is pretty easy as long as you know what you are doing (which i didn't....) So mainly for my benefit i'm writing this down in case i need to do it again...

So the elements we need are as follows:

1) query to populate the top report
2) query to populate the second report (that we somehow pass a variable in to)
3) the ability to click some on screen value and that to pass a value to point 2

To make this work in Apex you have to use the following components

1) a hidden page item to hold a variable value
2) a javascript function to populate that variable value
3) a dynamic action to refresh the bottom section of the screen when i call the javascript function
4) and the queries of course, one simple and the other that somehow has a link to this variable value

So how do we then build this?

For point 1 (the easy bit) we just add a page item with the following definition

So thats really nothing special at all (and all we use it for is to hold a variable value)

For point 2 we just call a very simple javascript function - all that it does is accept a single parameter - it then assigns that value to the hidden page item we created in step 1)  This is of the following form

javascript:$s('PN_SELECTED_NODE', #DBID#);

This built in function $s is essentially setting values - so we set PN_SELECTED_NODE (The hidden page item) to a value of #DBID# - this is the value of the column of the row we are currently 'on'. 

 For point 3 we create a simple dynamic action - that fires when the value of the hidden page item is changed - when it does change the lower part of the screen is refreshed

So now we are nearly there all we need to be able to do is trigger the javascript function, this will populate the hidden item which in turn causes the dynamic action to fire and refresh the lower part of the page

 Now the bit i didn't mention so far is the slight amendment to the second sql statement - we just need to add the clause "where column= hidden page item value" - as demonstrated below. Then when the page is submitted - the value of the hidden item is used to restrict the dataset and show only the rows we are interested in. The SQL for this is shown below.

All that remains then is to enable the javascript to be clicked/called from the top section of the page (this is the part that gave me the most trouble). In the end it's very easy.

If we navigate to the column properties of the second column (called link in my case - which was not meant to be confusing....). Here we define what we want the text to say underneath the hyperlink (click me in this case). We set the target to url, and then in the url section we paste the javascript function call i mentioned earlier

javascript:$s('PN_SELECTED_NODE', #DBID#);

So this is saying when i click on this link call the function and set the hidden page item to #DBID# - this then sets the other events in motion and the screen magically works

So now my screen behaves like this

I click on the 'click me' next to dbid 1 and the report at the bottom then shows the detail rows only for dbid 1.

So for my own understanding the steps are as follows

Easy when you know how......

Thanks to Scott Wesley (http://www.grassroots-oracle.com/) who helped me out via the oracle forums with getting this to work