ADF 11g – TreeTable with sub totals – how the SQL query can make life easier for the View developer

11

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<font face="Arial" size="2"><br /></font>

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&gt;= 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:

  &lt;bindings&gt;
    &lt;tree IterBinding=&quot;MasterTotalingView1Iterator&quot; id=&quot;MasterTotalingView1&quot;&gt;
      &lt;nodeDefinition DefName=&quot;model.MasterTotalingView&quot;&gt;
        &lt;AttrNames&gt;
          &lt;Item Value=&quot;NodeLabel&quot;/&gt;
          &lt;Item Value=&quot;NodeType&quot;/&gt;
          &lt;Item Value=&quot;NodeValue&quot;/&gt;
          &lt;Item Value=&quot;Deptno&quot;/&gt;
          &lt;Item Value=&quot;TreeLevel&quot;/&gt;
          &lt;Item Value=&quot;SalaryAverage&quot;/&gt;
          &lt;Item Value=&quot;Job&quot;/&gt;
        &lt;/AttrNames&gt;
        &lt;Accessors&gt;
          &lt;Item Value=&quot;TreeLevelMasterTotalingView&quot;&gt;&lt;/Item&gt;
        &lt;/Accessors&gt;
      &lt;/nodeDefinition&gt;
      &lt;nodeDefinition DefName=&quot;model.MasterTotalingView&quot;&gt;
        &lt;AttrNames&gt;
          &lt;Item Value=&quot;NodeLabel&quot;/&gt;
          &lt;Item Value=&quot;NodeType&quot;/&gt;
          &lt;Item Value=&quot;NodeValue&quot;/&gt;
          &lt;Item Value=&quot;Deptno&quot;/&gt;
          &lt;Item Value=&quot;TreeLevel&quot;/&gt;
          &lt;Item Value=&quot;SalaryAverage&quot;/&gt;
          &lt;Item Value=&quot;Job&quot;/&gt;
        &lt;/AttrNames&gt;
      &lt;/nodeDefinition&gt;
    &lt;/tree&gt;

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:

&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; ?&gt;
&lt;pageDefinition xmlns=&quot;http://xmlns.oracle.com/adfm/uimodel&quot;
                version=&quot;11.1.1.51.56&quot; id=&quot;HrmTreeTablePageDef&quot;
                Package=&quot;view.pageDefs&quot;&gt;
  &lt;parameters/&gt;
  &lt;executables&gt;
...
<strong>    &lt;invokeAction Binds=&quot;ExecuteWithParams&quot; id=&quot;initializeHrmTree&quot;<br />                  Refresh=&quot;ifNeeded&quot;/&gt;<br /></strong>  &lt;/executables&gt;
  &lt;bindings&gt;
...
<strong>    &lt;action IterBinding=&quot;MasterTotalingView1Iterator&quot; id=&quot;ExecuteWithParams&quot;<br />            RequiresUpdateModel=&quot;true&quot; Action=&quot;executeWithParams&quot;&gt;<br />      &lt;NamedData NDName=&quot;bind_treelevel&quot; NDType=&quot;java.lang.String&quot;<br />                 NDValue=&quot;${1}&quot;/&gt;<br />    &lt;/action&gt;<br /></strong>  &lt;/bindings&gt;
&lt;/pageDefinition&gt;

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.

        &lt;af:treeTable value=&quot;#{bindings.MasterTotalingView1.treeModel}&quot;
                      var=&quot;node&quot;
                      selectionListener=&quot;#{bindings.MasterTotalingView1.treeModel.makeCurrent}&quot;
                      rowSelection=&quot;single&quot; id=&quot;treeTable1&quot;&gt;
          &lt;f:facet name=&quot;nodeStamp&quot;&gt;
            &lt;af:column align=&quot;#{node.NodeType=='aggregate'?'right':'left'}&quot;
                       inlineStyle=&quot;#{node.NodeType=='aggregate'?'font-weight:bold;':''}&quot;&gt;
              &lt;af:outputText value=&quot;#{node.NodeLabel}&quot;/&gt;
            &lt;/af:column&gt;
          &lt;/f:facet&gt;
          &lt;f:facet name=&quot;pathStamp&quot;&gt;
            &lt;af:outputText value=&quot;#{node}&quot;/&gt;
          &lt;/f:facet&gt;
          &lt;af:column&gt;
            &lt;f:facet name=&quot;header&quot;&gt;
              &lt;af:outputText value=&quot;Department&quot;/&gt;
            &lt;/f:facet&gt;
            &lt;af:outputText value=&quot;#{node.Deptno}&quot;/&gt;
          &lt;/af:column&gt;
          &lt;af:column&gt;
            &lt;f:facet name=&quot;header&quot;&gt;
              &lt;af:outputText value=&quot;Job&quot;/&gt;
            &lt;/f:facet&gt;
            &lt;af:outputText value=&quot;#{node.Job}&quot;/&gt;
          &lt;/af:column&gt;
          &lt;af:column inlineStyle=&quot;#{node.NodeType=='aggregate'?'font-weight:bold;':''}; text-align:right;&quot;&gt;
            &lt;f:facet name=&quot;header&quot;&gt;
              &lt;af:outputText value=&quot;Salary Average&quot;/&gt;
            &lt;/f:facet&gt;
            &lt;af:outputText value=&quot;#{node.SalaryAverage}&quot;/&gt;
          &lt;/af:column&gt;
          &lt;af:column inlineStyle=&quot;#{node.NodeType=='aggregate'?'font-weight:bold;':''}; text-align:right;&quot;&gt;
            &lt;f:facet name=&quot;header&quot;&gt;
              &lt;af:outputText value=&quot;Salary&quot;/&gt;
            &lt;/f:facet&gt;
            &lt;af:outputText value=&quot;#{node.NodeValue}&quot;/&gt;
          &lt;/af:column&gt;
        &lt;/af:treeTable&gt;
 

Resources

Download JDeveloper 11g Application: to be provided

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

11 Comments

  1. 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.

  2. Lucas,

    Thanks for your post.
    Is it possible to acheive the same tree table structure using Web Service DataControl instead ofView Objects.

    Thanks

  3. 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

  4. 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.

  5. 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

  6. 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

  7. 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

  8. Hi Lucas,
    I’m trying to do your sample with grouping sets, but i can’t do it. Plz help me,

  9. 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.