Home > APEX, SQL > Report Hierarchical Data With APEX

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.  

On my current project, I needed to build an interface against just such a dataset.  So I needed an alternative to the built-in tree.  I looked at either writing my own tree using javascript and AJAX or using an already developed one.  Neither one appealed to me.  They both added development complexity and maintaining them weighed on my final decision.

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 :P5_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> >>> ' || 
             '<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.

4. ???

5. Profit!

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.

It’s a quick simple technique, and 100% APEX. No additional javascript libraries or custom coding to accomplish a clean and simple interface. And it will be easily maintained, either by you, or by the developer that comes after you.

Let me know what you think.

Categories: APEX, SQL Tags: , , , ,
  1. September 5, 2008 at 4:47 pm

    Very neat solution!


  2. Stew
    September 5, 2008 at 6:01 pm

    Nice job, Chris. Very clean. It certainly depends on having your data set up right for this sort of drill-down, but it works perfectly in your example.

    Thanks for sharing.

  3. September 5, 2008 at 6:24 pm

    Like the breadcrumb!


  4. Christopher Beck
    September 5, 2008 at 8:50 pm

    Thanks for the positive feedback. I’m glad to see that techniques like this may be useful to others.

  5. peter
    October 29, 2010 at 6:43 pm


    I am creating a report in APEX on an application that is reached from Oracle EBS using the POPAY method. All seems to be working fine but when i create a report region using the SQL query below, I get no data found. If i remove the Connect By Prior and Start With portion (needed for recursive data fetch) i get data…so i know my connection and permissions are good. I am just trying to display the data at this point. My application is using APPS as the Parsing Schema. Any thoughts?
    WHERE 1=1

    • Christopher Beck
      October 29, 2010 at 7:13 pm

      If when you remove the start with and connect by, you get data, then I would verify that the start with subquery does in fact return data. Otherwise you start with NULL and will not get any results. Also, I don’t think the ‘&.’ syntax is correct. I think you just want to reference it as a bind variable. Use WHERE EMPLOYEE_NUMBER = :FND_GLOBAL_USER

  6. Rhonda Bernhardt
    December 29, 2010 at 10:43 pm

    I need to create a hierarchical report of a parent, child, grandchild, and great-grandchild. I need the parent to display on space 1 on the first row and take up the entire row. I need the child to display on space 4 on the second row and take up the remainder of the second row. I need the grandchild to display on space 7 of the third row and take up the remainder of the third row. I need the great-grandchild to display on space 10 of the fourth row and take up the remainder of the fourth row.

    In reality there are 1 to many children for each parent, 1 to many grandchildren for each child, and 0 to many great-grandchildren for each grandchild.

    I can report/display the data now in a table but it is very wide. If exported to Excel and printed, it is too wide to print. I can choose to group by Parent (and by child and by grandchild) and not repeat Parent for every child, and so on. That makes a nice looking spreadsheet.

    The Parent is called Domain. There is a table for domains containing Domain ID and Domain Name. The Child is called Discipline. There is a table for Disciplines containing Disicipline ID, Disicpline Name and Related Domain. The grandchild is called Technology. There is a table for Technologies containing Technology ID, Technology Name, and Related Discipline. The great-grandchild is called Product. There is a table for products containing Product ID, Product Name, and Related Technology.


    • Christopher Beck
      December 30, 2010 at 2:27 am

      Not 100% sure how this relates to my post, but I think something like this should work:

      select lpad( ' ', (level-1)*3, ' ' ) || name
      from ( select dom_id id, null parent_id, dom_name name from domain
      select des_id, related_dom_id, des_name from descipline
      select tech_id, related_des_id, tech_name from technology
      select prod_id, related_tech_id, prod_name from product )
      start with parent_id is null
      connect by prior id = parent_id

  7. uday
    January 11, 2012 at 3:44 pm

    i get no daya found error
    and if i set manually emp no e.g 7839
    then it gives

    Top >>> KING

    what i missed please tell me
    Thanks in advance.

    • Christopher Beck
      January 11, 2012 at 4:34 pm

      I don’t know. Do you have a working example that I can look at?

  8. uday
    January 12, 2012 at 5:42 am
  9. Chris
    November 24, 2015 at 6:10 am

    I think this is the right type of design for a report I will be putting together soon. Definitely bookmarking this.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: