ADF DVT Hierarchy Viewer on demand – made to order multi table hierachy view

This morning, I noticed the following email sitting in my Inbox: “Hello, Lucas.

I found Hierarchy Viewer demo from https://technology.amis.nl/blog/5786/adf-11gr1-new-hierarchical-viewer-for-visually-pleasing-representation-of-data-structures.

But i need to implement some kind of solution. I attached my expected mockup and table structure.

Is it possible? If possible, please suggest me how to do that and put your solution based on your experience.

I’m looking forward to hearing from you soon.”

Obviously, I normall completely ignore such emails – I do have a job you know, and some semblance of a normal life too. However, this email triggered me in some way. And between other more important things, I tried to create the desired hierarchy. The mock up looks like this:

The most challenging part is probably to get the query right. Once that query is defined – in a ViewObject with a selfrefencing ViewLink – creating the hierarchy is very straightforward. Some final styling is required – and a different design to the Radio Buttons, because those are not supported in the Hierarchy Viewer – but otherwise I think I did the job. My end result looks like this (from far away):

Note that the data I received with the email was not entitrely consistent with the mock up of the Hierarchy View.

The email contained an illustration of the tables and the data:

It would of course have been more convenient to have ready to execute DDL scripts. But hey, you cannot have everything.

I created the tables with the data from JDeveloper (you will find the script to create the Database objects in the Resources section below):

and the same for the table PROGRAMS:

Now the interesting bits:

1. create the SQL query that selects all nodes of the hierarchy in a single set (even though they span two tables and multiple levels); each node record has an id, a parent id, a label and a node type indication
2. create a ViewObject based on that SQL query; ensure that the NodeId is set to be a (primary) Key attribute
3. create a ViewCriteria to select Root Nodes (nodes for which parent_id is null)
4. create a ViewLink for that ViewObject that links the VO to itself (source.node_id <= target.parent_id)
5. add the ViewObject to the Application Module’s data model – twice: once of the root nodes and once for the children linked via the ViewLink (note: ADF knows how to continuously traverse further down the tree; associate the first usage of the ViewObject with the ViewCriteria for root nodes
6. run the application module and verify that the correct data is returned

Create the SQL query

that selects all nodes of the hierarchy in a single set (even though they span two tables and multiple levels); each node record has an id, a parent id, a label and a node type indication

with nodes as
( select id+1000 node_id
, name
, Parent_OrgaId + 1000 parent_id
, 'ORG'  node_type
, amount
, administration
from organizations
union all
select id node_id
, name
, nvl(Parent_ProgramID,Organization_Id+1000)  parent_id
, 'PGM'  node_type
, amount
, ''
from programs
)
select name
,      node_id
,      node_type
,      parent_id
,      amount
,      administration
,      case (select count(*) from nodes n2 where n2.node_type ='ORG' and n2.parent_id = n1.node_id)
       when 0 then 'P'
       else 'A' end by_adm_or_pgm
from   nodes n1

Create a ViewObject based on that SQL query

; ensure that the NodeId is set to be a (primary) Key attribute

create a ViewCriteria to select Root Nodes

(nodes for which parent_id is null)

Create a ViewLink for that ViewObject

that links the VO to itself (source.node_id <= target.parent_id)

Add the ViewObject to the Application Module’s data model

– twice: once of the root nodes and once for the children linked via the ViewLink (note: ADF knows how to continuously traverse further down the tree);

associate the first usage of the ViewObject with the ViewCriteria for root nodes

Run the application module

and verify that the correct data is returned

Create the Web Page with the Hierarchy View

The second part is quite straightforward with the ViewObject at our disposal to bind the hierarchy to.

The steps in the ViewController project:

1. create a new JSF page
2. drag the collection based on the ViewObject to the page and drop as Hierarchy Viewer
3. configure the Hierarchy Viewer – include three levels: the ViewObject you dragged to the page, its child via the ViewLink and finally its self-referencing child
4. fine tune the page: ensure that the correct pieces of data are shown in the right format; tweak the inlineStyle to set the background based on node type etc.
5. run the page

Create a new JSF page and drag the collection based on the ViewObject to the page and drop as Hierarchy Viewer

Configure the Hierarchy Viewer

Press OK. – include three levels: the ViewObject you dragged to the page, its child via the ViewLink and finally its self-referencing child

Fine tune the page

Ensure that the correct pieces of data are shown in the right format; tweak the inlineStyle to set the background based on node type etc.

Slight disappointed: radio groups are not supported inside the Hierarchy Viewer – so I came up with a workaround:

The background color is set with a ternary EL expression:

Run the page

and:

and even:

There is so much you can do to further enhance the look and feel. That I leave as an exercise to the reader!

Resources

Download JDeveloper 11gR2 application with all sources: OrganizationAndProgramHierarchyViewerJDev11gR2app.