Report Hierarchical Data With APEX
Anyone familiar with Oracle and hearing the word hierarchical immediately thinks of the sql CONNECT BY clause. And I would bet that when they think about displaying this data, they would use some sort of tree widget. APEX has a built-in tree widget and for many applications, it works fine.
The drawback to the built-in tree widget is that it brings back the entire dataset on the initial page render. Good and Bad. Saves round trips to the server, but could take a lot of initial time depending on the size of the data. Showing/hiding branches of the tree with many elements also can make the application feel sluggish.
What I chose to do was build a breadcrumb-like report displaying the path traversed and a second report displaying the elements at the current level. All default APEX functionality. Simple clean interface. Easily developed and easily maintained. You can check it out here.
Let me quickly walk you through how it was built:
1. Create a report on the base table constrained by the hidden item. The query should look something like:
select * from emp where nvl(mgr,-1) = :p5_empno
I then linked the ENAME column back to the same page passing the EMPNO value into 5_EMPNO.
2. Create a hidden Item to hold the manager’s id. I put it in the step 1′s report region and called it P5_EMPNO and set its default value to -1.
3. Create a PATH report to manage the traversing the data. This is where the magic happens. I make use of the SYS_CONNECT_BY_PATH() function in conjunction with the START WITH…CONNECT BY clause. The query I used was:
select '<a href="f?p=' || :app_id || ':5:' || :app_session || '::::p5_empno:-1">Top</a> >>> ' || substr( sys_connect_by_path( '<a href="f?p=' || :app_id || ':5:' || :app_session || '::::p5_empno:' || empno || '">' || ename || '</a>', ' : ' ), 4 ) path from emp where empno = :p5_empno start with mgr is null connect by prior empno = mgr
Some other tweeks to this region. No Pagination. Report Template of Value Attribute Pairs. Layout above the first report region. No region template.
Now you can use the main report to drill into the children of the row you selected, all the while maintaining the context of where you are in the hierarchy with the path.
Let me know what you think.