Wednesday, March 21, 2007

Apex: Nested reports

Have you ever wondered how to display additional detail records together with the master records in a single report?

For example, in a recent project I had to display a list of orders. Together with the order details (who, when) I had to display the order items in the same row.

The result should look like this:


How can this be done?

The "trick" is to create a stored function which generates the HTML for the detail records so that they can be displayed with the master records.

Here is a step by step example based on the tables EMP and DEPT, what else ;).

1. Create the package and stored function to generate the html for all employees in a specific department:

CREATE OR REPLACE PACKAGE emp_pck
AS
/******************************************************************************
NAME: EMP_PCK
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 16.03.2007 1. Created this package.
******************************************************************************/
FUNCTION get_emps_inline_f (p_deptno NUMBER)
RETURN VARCHAR2;
END emp_pck;
/


CREATE OR REPLACE PACKAGE BODY emp_pck
AS
FUNCTION get_emps_inline_f (p_deptno NUMBER)
RETURN VARCHAR2
IS
l_str VARCHAR2 (32767);
l_cnt NUMBER := 0;
BEGIN
l_str := '<table class="inlineTable">';
l_str :=
l_str
|| '<tr><th>No.</th><th>Name</th><th>Job</th><th>Salary</th></tr>';

FOR cur IN (SELECT empno, ename, job, sal
FROM emp
WHERE deptno = p_deptno
ORDER BY ename)
LOOP
l_str := l_str || '<tr>';
l_str :=
l_str
|| '<td>'
|| cur.empno
|| '</td><td>'
|| cur.ename
|| '</td><td>'
|| cur.job
|| '</td><td>'
|| cur.sal
|| '</td>';
l_str := l_str || '</tr>';
l_cnt := l_cnt + 1;
END LOOP;

l_str := l_str || '</table>';

IF l_cnt = 0
THEN
RETURN '';
ELSE
RETURN l_str;
END IF;
END;
END emp_pck;
/
2. Create the styles in the page header, in order to format the inline table:
<style type="text/css">
<!--
.inlineTable{border:1px solid #cfe0f1;border-collapse:collapse;width:100%;}
.inlineTable th{color:#336699;border:1px solid #cfe0f1;padding:2px;}
.inlineTable td{border:1px solid #cfe0f1;border-left:none;border-top:none;padding:2px;}
-->
</style>
3. Create the query in a report region
SELECT dept.*, emp_pck.get_emps_inline_f (deptno) employees
FROM dept

Here you can see the final result:


The forum thread can be found here and the online demo is located here.

Regards,
~Dietmar.

23 comments:

Patrick Wolf said...

That's a nice one! I have to add it to my knowledge database.

Patrick

Denes Kubicek said...

Ein sehr schönes Beispiel Dietmar.

Denes

Anonymous said...

Very nice example Dietmar. I'm sure that is a fairly common requirement.
Jon.

Unknown said...

A more generic method is to use a function that turns a ref cursor to an html table (example here). Then call the function while passing it a correlated subquery within a cursor() call. I have further modified this example to use a stored stylesheet so the function can use different stylesheets as needed.

Unknown said...

Hi Maline,

interesting approach. I will post a full working sample later.

Thanks,
~Dietmar.

Unknown said...

Dietmar,

I am new to APEX and very much enjoyed your blog on nested reports.

The report works well if I want all departments. I assume from the function argument that their is a way to limit output to a specific, single department, but I am unable to make that work.

I thought there would be a dynamic LOV somewhere in a search region to limit the answer set. I must be missing something.

Your thoughts or any guidance is appricated.

Barry

Unknown said...

Hi Barry,

nice that you enjoyed the Post :).

I guess I don't fully understand your question.

The sample query I use lists all departments.
SELECT dept.* FROM dept

In addition to that I display all employees in that specific department. I do that for each row in the departments, this is why I use p_deptno as an input parameter to the stored function.

If you want to limit the output to a specific department, you would limit the base query:
SELECT dept.* FROM dept
where deptno=:p1_deptno

P1_DEPTNO would be a select list based on a lov.

or if you display a null value in the select list for showing all departments, you could use the following:
SELECT dept.* FROM dept
where (:p1_deptno is null or deptno=:p1_deptno)

And you would need a calculation for removing the %null% from the select list:
replace(:p1_deptno, '%'||'null%', null)

Hope that helps. If you have more questions, feel free to ask.

Regards,
~Dietmar.

Anonymous said...

Dietmar,
This is very nice sample, very useful. However,
i am experiencing a problem that the styles doesn't work when i put it in the page header.
What could be wrong?

Thanks,
Jennifer

Unknown said...

Hi Jennifer,

can you put up an example on apex.oracle.com? Then I can have a look at it.

Or you can post your style definition and the source code of your function.

Please encode the html tags, e.g. using this tool:
http://daust.blogspot.com/2007/03/small-tool-to-escape-html-text-for.html

Regards,
~Dietmar.

Anonymous said...

Thank you. I am new to APEX and this is a perfectly sensible solution to displaying details. Joe S.

Anonymous said...

very nice example, just what I was looking for. Is it possible to format the cells of the detail table to be of fixed width, so they align under each other. Also is it possible to ad hyper-links to the detail text.

Unknown said...

Hi "anonymous",

I have updated the sample, it now includes the formatting for the different columns of the nested report:
http://apex.oracle.com/pls/otn/f?p=daust_demos:nested_report

How does it work?

Just add another specific css class to the different columns (i.e. colEmpno, colEname, ...). Then use CSS to format the columns. That's it.

But you could also use the html syntax to make the columns a certain width: <td width="50px">...

You have full control over the generated html, so do whatever you feel most comfortable with.

Regards,
~Dietmar.

Anonymous said...

thanks! a lot. You have been a great help.

Anonymous said...

This is very good and example. Can you please elaborate the methode used to highlite a row when the cursor moves on it.

Anonymous said...

I have implemented a report based on your example and the output looks really good. However I have run into a issue and wondered if you had any thoughts.
The pl/sql package returning the inner records has the return type as clob.
When I return more than eight inner records, I get a PL/SQL Numeric or character value error.
However if I reduce the number of inner rows being returned to 8 rows, the output displays correctly and no error message.
Also for test purposes all the 9 inner records were identical in values other than the primary key.
I don't know if I am hitting some size limit or something else is going wrong.
I'd really appreciate any kind of suggestion or help.
Thanks!

Unknown said...

Hi Anonymous,

this shouldn't be a size limitation since you are using the clob version.

Can you post the code for the function?

Please encode the html tags, e.g. using this tool:
http://daust.blogspot.com/2007/03/small-tool-to-escape-html-text-for.html

Regards,
~Dietmar.

Anonymous said...

Dietmar:

This would be more useful if we could show/hide the detail records by clicking on the master record ..

Do you have some examples of something like that ?

Thanks - Yesh

Anonymous said...

hi deitmar

have a function that display a matrix format report, i managed to format it but it displays a recurring output equivalent to the number of items in my query.
thanks.

cons

Unknown said...

can you put up an example on apex.oracle.com and give me access to the workspace?

or give us some more code details.

Regards,
~Dietmar.

Janel said...

I was curious about Yesh's comment - wondered if anyone had been able to implement this with show/hide onHover? Would like to incorporate that into my application, but not sure where to start.

Thanks!
Janel

Unknown said...

Hi guys,

should be easy building on Carl's example here:
http://htmldb.oracle.com/pls/otn/f?p=11933:13

Regards,
~Dietmar.

Term Papers said...

I have been visiting various blogs for my term papers writing research. I have found your blog to be quite useful. Keep updating your blog with valuable information... Regards

Anonymous said...

Thanks a lot, this was exactly what I was looking for!