The ADF 11g Tree Table component can be used for the compact presentation of fairly complex data sets. It allows the user to quickly drill down to a specific area of interest. For example to find all Roles within a specific Department and for any Role all Employees in that role.
On many occasions, it may be desired to show sub-totals at the various levels in the tree. For example in the way shown in this screenshot:
In this article we will see how we can achieve this fairly easily, by creating a ViewObject with a SQL query that does most of the heavy lifting.
This example is – again – based on EMP and DEPT (the age old SCOTT schema). Additionally, I have created this entire tree table using a single ViewObject. I now believe the result could have been achieved in a more straightforward fashion by using different ViewObjects for each level in the tree. However, what I did works and serves well as an example.
The steps:
1. Set Up: create JDeveloper 11g Fusion Web Application; create database connection to Scott Schema.
2. Create ViewObject to retrieve the dataset with Department and Job level Rollup Aggegation
3. Create self-referencing ViewLink to tie parent and children together
4. Add ViewObject usages to Application Module data model
5. Create new JSF page
6. Drag collection from Data Control Panel and drop as Tree Table
7. Extend the tree binding in the Page Definition for this page
8. Add some columns to the Tree Table; also add some styling to the table.
9. Run the page and see the results.
Let’s go over the more interesting steps in detail:
2. Create ViewObject to retrieve the dataset with Department and Job level Rollup Aggegation
The query uses the ROLLUP aggregation operator to add rows that the various grouping levels; The group by expression is
group by rollup( (d.deptno, d.dname) , job, (ename,empno))
which means subtotals are added for Job and Department and a Grand Total is added as well (aggregation over all records). Note: the inclusion of (ename, empno) in the ROLLUP expression means that we get all Employee records; since they are at the lowest level and empno is the primary, in all cases this level entails aggregating a single record.
The key to the solution is the creation of additional records for the various aggregation levels.We want to have the subtotal rows twice: once to include the Department node or the Job node within the Department, and a second time to include a Subtotal over the entire Department or all Employees in a certain Job. This is achieved through the join with an inline query that returns two records: one for the normal row and one for the aggregated row; that second row is only joined for rows that are aggregates (subtotals):
... all records from EMP complemented with subtotals at JOB and DEPT levels join ( select 'Subtotal' label , 2 aggregating from dual union select to_char(null) label , 0 from dual ) totaler on (agg_deptno + agg_job + agg_ename>= totaler.aggregating or totaler.aggregating = 0)
I have added a condition to the where clause that uses a bind parameter. This will be used to return the first level of the tree. The ViewLink is used to access the detail nodes.
where nvl(:bind_treelevel, 3 - (agg_deptno + agg_job + agg_ename) + aggregating) = 3 - (agg_deptno + agg_job + agg_ename) + aggregating
Note: I am sure the SQL can be made more elegant and efficient. . In addition it was a little overly complex I believe to try to produce the entire tree structure with a single ViewObject. It is probably easier to make this work with separate ViewObjects for the different levels.
The entire query:
select case aggregating when 1 then case (agg_deptno + agg_job + agg_ename) when 3 then 'Grand Total' when 2 then 'Department Total' else 'SubTotal (for Job)' end else nvl(ename, nvl(job, dname)) end node_label , deptno , dname , job , ename , salary node_value , salary_average , label , case aggregating when 1 then 'aggregate' else 'data' end node_type , 3 - (agg_deptno + agg_job + agg_ename) + aggregating tree_level , deptno||';'||job||';'||empno||case aggregating when 1 then 'aggregate' end node_id , case agg_job when 1 then case aggregating when 1 then to_char(deptno) else '' end else to_char(deptno) end ||';' ||case agg_ename when 1 then case aggregating when 1 then job else '' end else job end ||';' parent_node_id from ( select d.deptno , d.dname , job , ename , empno , grouping(d.deptno) agg_deptno , grouping(e.job) agg_job , grouping(e.ename) agg_ename , sum(sal) salary , avg(sal) salary_average from emp e right outer join dept d on (e.deptno = d.deptno) group by rollup( (d.deptno, d.dname) , job, (ename,empno)) ) hrm_agg join ( select 'Subtotal' label , 2 aggregating -- every record is already an aggregation on agg_ename from dual union select to_char(null) label , 0 from dual ) totaler on (agg_deptno + agg_job + agg_ename>= totaler.aggregating or totaler.aggregating = 0) where nvl(:bind_treelevel, 3 - (agg_deptno + agg_job + agg_ename) + aggregating) = 3 - (agg_deptno + agg_job + agg_ename) + aggregating
Note the support this SQL offers the to the View developer that wants to leverage it. It specifies the node type (data or aggregate), returns a pretty aggregation node label, provides a generic NodeId and ParentNodeId reference, and is altogether helpful to its consumers.
3. Create self-referencing ViewLink to tie parent and children together
The ViewLink is from the ViewObject and to the ViewObject. It is based on the NodeId attribute in the Source and the ParentNodeId attribute in the Destination.
Its join-condition:
parent_node_id = :Bind_NodeId
4. Add ViewObject usages to Application Module data model
7. Extend the tree binding in the Page Definition for this page
The self referencing ViewLink for some reason is not interpreted correctly by the tree binding editor – I can not successfully add tree rules. Perhaps I do not even need them? Anyways, I end up with the following tree binding definition:
<bindings> <tree IterBinding="MasterTotalingView1Iterator" id="MasterTotalingView1"> <nodeDefinition DefName="model.MasterTotalingView"> <AttrNames> <Item Value="NodeLabel"/> <Item Value="NodeType"/> <Item Value="NodeValue"/> <Item Value="Deptno"/> <Item Value="TreeLevel"/> <Item Value="SalaryAverage"/> <Item Value="Job"/> </AttrNames> <Accessors> <Item Value="TreeLevelMasterTotalingView"></Item> </Accessors> </nodeDefinition> <nodeDefinition DefName="model.MasterTotalingView"> <AttrNames> <Item Value="NodeLabel"/> <Item Value="NodeType"/> <Item Value="NodeValue"/> <Item Value="Deptno"/> <Item Value="TreeLevel"/> <Item Value="SalaryAverage"/> <Item Value="Job"/> </AttrNames> </nodeDefinition> </tree>
I made some additional changes in the Page Definition: I want the ViewObject to be initially queried for the level 1 nodes (with bind parameter bind_treelevel equals 1). This is achieved with an ActionBinding for the ExecuteWithParams that is invoked through an InvokeAction:
<?xml version="1.0" encoding="UTF-8" ?> <pageDefinition xmlns="http://xmlns.oracle.com/adfm/uimodel" version="11.1.1.51.56" id="HrmTreeTablePageDef" Package="view.pageDefs"> <parameters/> <executables> ... <invokeAction Binds="ExecuteWithParams" id="initializeHrmTree"
Refresh="ifNeeded"/>
</executables> <bindings> ... <action IterBinding="MasterTotalingView1Iterator" id="ExecuteWithParams"
RequiresUpdateModel="true" Action="executeWithParams">
<NamedData NDName="bind_treelevel" NDType="java.lang.String"
NDValue="${1}"/>
</action>
</bindings> </pageDefinition>
Note the value of 1 being passed to the bind_treelevel bind parameter.
8. Add some columns to the Tree Table; also add some styling to the table.
<af:treeTable value="#{bindings.MasterTotalingView1.treeModel}" var="node" selectionListener="#{bindings.MasterTotalingView1.treeModel.makeCurrent}" rowSelection="single" id="treeTable1"> <f:facet name="nodeStamp"> <af:column align="#{node.NodeType=='aggregate'?'right':'left'}" inlineStyle="#{node.NodeType=='aggregate'?'font-weight:bold;':''}"> <af:outputText value="#{node.NodeLabel}"/> </af:column> </f:facet> <f:facet name="pathStamp"> <af:outputText value="#{node}"/> </f:facet> <af:column> <f:facet name="header"> <af:outputText value="Department"/> </f:facet> <af:outputText value="#{node.Deptno}"/> </af:column> <af:column> <f:facet name="header"> <af:outputText value="Job"/> </f:facet> <af:outputText value="#{node.Job}"/> </af:column> <af:column inlineStyle="#{node.NodeType=='aggregate'?'font-weight:bold;':''}; text-align:right;"> <f:facet name="header"> <af:outputText value="Salary Average"/> </f:facet> <af:outputText value="#{node.SalaryAverage}"/> </af:column> <af:column inlineStyle="#{node.NodeType=='aggregate'?'font-weight:bold;':''}; text-align:right;"> <f:facet name="header"> <af:outputText value="Salary"/> </f:facet> <af:outputText value="#{node.NodeValue}"/> </af:column> </af:treeTable>
Resources
Download JDeveloper 11g Application: to be provided
Hi Lucas,
Thanks for your post. Its very very useful.
Iam working on JDeveloper 11g,
i need some sample application on Tree,TreeTable components,
can you please send me the sample applications.
Thanks in advance.
Lucas,
Thanks for your post.
Is it possible to acheive the same tree table structure using Web Service DataControl instead ofView Objects.
Thanks
Hi Lucas,
thanks for your posting, it’s usefull.
Can you please post also a sample 11G Application?
I have still some problems to get it work, when clicking a root node no child data is displayed.
Stefan
Hi Lucas,
Thanks for the post, Its helpful.However I am just wondering if i can use the tree table to display checkboxes in place of output text.I want to use those checkboxes to recieve user inputs and save those somewhere.
Hi DS,
It will not work as is for JDeveloper 10.1.3.4 as it has ADF 10g rather than 11g. The database version is fine, 9iR2 or higher will do. For ADF 10g you should be able to do something very similar, as the TreeTable is also a component in ADF 10g. (the styling and the definition of the tree (data binding) rules is somewhat different in 10g).
Good luck! best regards
Lucas
Hi Lucas,
Thanks for your post. Its very very useful.
I have 1 quick q:
I work on Oracle Jdeveloper 10.1.3.4 and Oracle DB 10g. Would this example work as it is in the same or I need some change?
Thank yo Lucas for all your help.
Regards,
DS
Hi Lucas, thank you for the posting, it gave me hope of build something complex with the tables..
i tried your tutorial…but failed. I think the mistake related to the view link that i made, because i couldnt find TreeLevelMasterTotalingView accessor anywhere.
please provide the sample application so i can analyze it..or can i send my application to u?
and could u please elaborate more on the query suggestion from rob?
thanks
Hi Lucas,
I’m trying to do your sample with grouping sets, but i can’t do it. Plz help me,
On grouping sets and MapReduce.
See: http://www.data-miners.com/blog/2008/01/mapreduce-and-sql-aggregations.html and http://www.data-miners.com/blog/ .
Thanks Rob, that is very useful. I should have taken a little longer tweaking the query. But now you did it for me. Great! Lucas
Hi Lucas,
You already suspected it, and indeed it can be done easier using grouping sets:
group by grouping sets
( (d.deptno,d.dname,job,ename,empno)
, (d.deptno,d.dname,job)
, (d.deptno,d.dname)
, (d.deptno,d.dname)
, ()
, ()
)
Then you can skip the join with the totaler inline view.
Regards,
Rob.