17 September 2014

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.

10 comments:

  1. Hello,

    Well I tried this and it get an error "Unable to find item ID....". How does your item P9_DEPTNO get created ?

    Thanks,

    ReplyDelete
    Replies
    1. Hi Paubry,
      P9_DEPTNO is a hidden item. Right click on the region > Create Page Item > type: hidden and that's it.

      Alex

      Delete
  2. Thanks Alex,

    I tried this with no success, nothing happens when I hit the link. There is a class called show-employees in your example. Does this have to be created ?

    Steve.

    ReplyDelete
    Replies
    1. The class "show-employees" is a method to attach the DA to the row. When the link is clicked the value of the clicked EMPNO is placed in a hidden item.
      The detail query uses this hidden item in the WHERE clause.
      There are two TRUE actions: 1 to set the hidden item and 1 to refresh the details report.
      Did you include the refresh of the detail report?

      Delete
  3. Yes I did. I have gone over your example carefully and tried to replicate it. The Dynamic action has 2 true actions, first executes the java script second refreshes the detail report region.

    The detailed report works fine if I hard code the id into it, so I guess somehow the id is not being passed in the link or the dynamic action is not firing.

    Are there any common mistakes people make that you can think of ?

    Thanks

    ReplyDelete
    Replies
    1. Hmmm... not that I can think of - Do you have your application on apex.oracle.com? Or could you send me an export of your application?

      Delete
  4. Yeah, yeah, it's over a year, but I'd like to share my experiences, as it helped me tremendously, but I also ran into problems. (i'm on apex 4.2 for this)

    1. I had to do a submit instead of a refresh as the second true action
    2. As my main report had navigation buttons, I had to set the event scope to Dynamic (as opposed to static), otherwise it would not work on the next page.
    3. As the field is hidden, I had to set it to Value Protected = No (mainly because of the submit).

    With that it worked beautifully. Many thanks.
    Klaas

    ReplyDelete
  5. I'm trying to do this in Apex 5 and it won't work; any help would be greatly appreciated.
    The part that doesn't work is populating the hidden field. I've set that to Value Protected = "No" so there's no reason why it can't be populated but it just won't.
    As I come to think of it; the field DOES get populated, it just gets populated with Null. If I pre-populate it with a valid value (10,20...), the details query picks that up but when I click on a link in the master section, the refresh results in a empty area so I conclude the hidden item is set to Null.
    Please help?

    ReplyDelete
  6. Hello,

    I have also tried this solution in Apex 5 and it won't work and help would be greatly appreciated.

    ReplyDelete
  7. It works in APEX 5.0 I've just implemented it. Maybe, you are something missing?!

    Thanks, Alex for this tutorial!

    ReplyDelete