Show report details in line

The other day I needed to create a report which could dynamically show and hide details of a row. I have seen examples before where either a pl/sql region was used or a function returning the required details was being used. In both cases you have create a procedure or function that returns html based on a query. You have to think of a way to collect the required data and then wrap it in HTML divs, td, etc. The result is very elegant, but the setup is rather time consuming. That can be worth the while if you can reuse that code on other reports in your application.

Unfortunately, in my case I only needed to show details in one report. To make it more complex, the details that I needed to show were subject to debate. It was likely that the exact details that needed to show were going to change during the development of the application. Therefore I chose a swift and simple solution based on jQuery and iFrames.

In the following example I will reconstruct what I’ve done, based on the DEPT and EMP tables. The result is a report on DEPT, which, as detail, shows all the employees of a department. We’ll make two pages, one for the DEPT report and one for the EMP report, which will be displayed in line in the DEPT report. Let’s start with the latter.

We need a page with a report on EMP. We only want to show the employees of the department we select in the DEPT report, so we need to add a where clause in the report query, and we need to add a page item that we can set. Here, I’ve created a hidden item calls ‘DEPTNO’.

The report query needs to be modified: we add a where clause that takes DEPTNO as a parameter

Just to make sure that our report shows up nicely as a detail, we will select page and report templates with little style features. Here I used “Popup” for page template and “No Template” for the report region. Also I’ve added a “nowrap” style element to the region header; this will ensure that the report columns will not break in two lines when the content gets to wide.

That’s all for the employee details. Now we’ll go to the DEPT page. Here too we create a page with a standard region, this time on the DEPT table.

On this page we need to create a dynamic action that will control the display and hide of the employees’ details. Here I’ve created a page load JavaScript action.

What the function does is as follows. When we click on a row, we check if we are already showing the details for that row. If we don’t we’ll create the URL of the employee page and put it in an iFrame. That iFrame we’ll wrap in a table row and paste it right after the row we clicked on. Else, if we already have the details for that row, we close the details section. The code is as follows:

And that's all. No we have a report showing all departments. When we click on a row we get a detail report of all the employees in that department. When we click the row again the detail closes. The great benefit of this setup is that we can easily change the way our detail section looks by simply modifying the report on employees.

See how it works in my sample application.
Good luck.