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

	select
	"EMPNO",
	"ENAME",
	"JOB",
	"MGR",
	"HIREDATE",
	"SAL",
	"COMM",
	"DEPTNO"
	from #OWNER#.EMP
	where deptno = :DEPTNO

view raw gistfile1.txt hosted with ❤ by GitHub

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.

	<style type="text/css">
	.uReportStandard tr td {white-space:nowrap;}
	</style>

view raw gistfile1.html hosted with ❤ by GitHub

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:

	//add a class to each table row, this will help to easily select a row.
	$('.uReportStandard >tBody>tr').addClass('deptRow')
	//Set some global variables of the page that we will navigate to - application nr, page nr and session id.
	var appId = $v('pFlowId')
	var pageId = 5
	var sessId = $v('pInstance')
	// when we click on a row we want something to happen.
	$('.deptRow').click(function(){
	// if the next row is also a row of the DEPT table than there are no details
	// , so we can create them. Else: we already have details row, so lets close it.
	if ( $(this).next().hasClass('deptRow') || $(this).is(":last-child") ){
	//get the deptno of the clicked row
	var deptNo = $(this).find('td[headers="DEPTNO"]').html()
	//create the url based on the global settings and the deptNo
	var url = 'http://apex.oracle.com/pls/apex/f?p='+appId+':'+pageId+':'+sessId+'::NO:'+pageId+':DEPTNO:'+deptNo
	//create the iFrame
	var frame = '<iframe class="iframe" id="empFrame" src="'+url+'" height="250px" width="600px" frameborder ="0"/>'
	//we're going to wrap the iframe in a table row. First we check how wide the td has to be.
	var colSpan = $('.deptRow:nth-child(1) td').length
	var newRow = '<tr><td colspan="'+colSpan+'">'+frame+'</td></tr>'
	// add the new row with the iframe just after the row we clicked on
	$(this).after(newRow)
	} else {
	$(this).next().remove()
	}
	})

view raw gistfile1.js hosted with ❤ by GitHub

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.