Friday, March 17, 2017

APEX 5.1 Master detail (with Interactive/Classic Detail Report)

You probably know that in APEX 5.1 you can easily make a master-detail report regions declaratively by using an Interactive Grids (IG).

But what if you don't want all those fancy IG stuff (I know, I can hide/disable all of it) but plain old Classic (or Interactive) Report for a detail report. No problem, it's only few clicks away!

Let's take a well known example with the dept and emp tables.

First of all you have to create an Interactive Grid over the dept table (master):

Don't forget to put the DEPTNO into SQL Query. It can be hidden, but you have to put it in.

Next step is to create a hidden item (with Value Protected property set to No) where you will temporary store the DEPTNO of the selected grid row (in my case it's P28_DEPTNO):

Then you can create your detail report (Classic or Interactive) over the emp table with where condition that references your hidden item (don't forget to put it in Page Items to Submit):

Next step is to create a dynamic action with event Selection Change [Interactive Grid] over the dept region:

For first true action use Set Value with the Set Type Javascript Expression and define it as: != 1 ? '':[0], "DEPTNO")
and for an Affected element choose your hidden item:

The second true action should be a refresh of your detail region...and thats all.

Live demo is available here.

You can use similar approach if you want this to work with a multiple selected rows (for example in editable grid) but then you should change Set Value DA and where condition of your emp query to support this.


Tested on APEX