Creating a Read Only Hierarchical Table Report with ADF 10g

November 9, 2008 IT, Oracle, Oracle Application Development Framework No Comments

 

Presenting information to end users can be somewhat challenging, especially if you want to provide a lot of information on a single page in a structured way. You can swamp the users with information and data, claim all they need to know is there and be on your merry way (that is the way most Excel sheets look to me). On the other hand, creating the perfect Dashboard can be a very time consuming task. In this article I try to describe a simple, structured and rather effective way of presenting a lot of information – I did not devise this presentation, that’s from one of my current customers – that can be implemented fairly rapidly. Perhaps it can provide  some inspiration.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport21

Steps:

1. Create new JDeveloper 1og Application – ADF Web Application template

2. In the Model Project, create default Entity and View Objects for DEPT and EMP

3. Create Read Only View Objects for specific information to be presented to the user:

Staff in Job Count per Department

select deptno
,      job
,      count(*) num_in_job
from   emp
group
by     deptno
,      job

Seniority distribution per Department 

select deptno
,      case extract(year from hiredate)
       when  1980
       then 'Senior'
       when  1981
       then 'Medior'
       else  'Junior'
       end   seniority
,      count(*) num_in_seniority
from   emp
group
by     deptno
,      case extract(year from hiredate)
       when  1980
       then 'Senior'
       when  1981
       then 'Medior'
       else  'Junior'
       end

Earning levels per Department

select deptno
,      case
       when sal < 1200
       then 'Low'
       when sal > 2800
       then 'High'
       else 'Middle'
       end salary_range
,      count(*) num_in_salaryrange
from   emp
group
by     deptno
,      case
       when sal < 1200
       then 'Low'
       when sal > 2800
       then 'High'
       else 'Middle'
       end

4. Create ViewLinks between Department (Source) and the RO ViewObjects from Step 3 (Destination), all based on the deptno attribute.

5. Update the Application Module’s Data Model

Add detail usages for all three aggregating ViewObjects under the DeptView usage.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport2

Run the Application Module Tester to get a first impression.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport3

6. Create ViewLinks between the aggregating ViewObjects (Source) and the EmpView (Destination)

This requires the following View Link SQL Where clauses:

For StaffInJob:

(:Bind_Deptno = Emp.DEPTNO) AND (:Bind_Job = Emp.JOB)

and for EarningLevels:

( :Bind_Deptno = Emp.DEPTNO)   AND (:Bind_SalaryRange = case
when emp.sal < 1200 then 'Low'
when emp.sal > 2800 then 'High'
else 'Middle'
end )

and finally for StaffSeniority:

(:Bind_Deptno = Emp.DEPTNO) AND (:Bind_Seniority = case extract(year from Emp.hiredate)                       when  1980 then 'Senior'                       when  1981 then 'Medior'                       else  'Junior'                       end)

Note: make sure that when you set up the ViewLink, you select an attribute from the EmpView that has the same Data Type as the attribute you need from the Source (Master) for creating the link condition. For example, in the last example, pick Emp.Ename to match StaffSeniority.Seniory (a String); if you try to match with Emp.Hiredate, you get conversion exception at runtime.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport5

7. Update the Data Model of the Application Module: based on these new ViewLinks, add detail ViewObject Usages

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport4

Also run the Tester again for the Application Module:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport6

8. Create a new Updateable ViewObject EmployeesView

Set the Where clause to:

nvl(:bind_deptno, deptno) = deptno
and nvl(:bind_job, job) = job
and nvl(:bind_seniority, case extract(year from Emp.hiredate)                                                 when  1980                          then 'Senior'                                                 when  1981                          then 'Medior'                                                 else  'Junior'                                                 end) = case extract(year from Emp.hiredate)                                when  1980                                 then 'Senior'                                                        when  1981                                 then 'Medior'                                                        else  'Junior'                                                        end and nvl(:bind_earninglevel, case                                                        when emp.sal < 1200                             then 'Low'                                                        when emp.sal > 2800                             then 'High'                                                        else 'Middle'                                                        end) = case                                                               when emp.sal < 1200                                    then 'Low'                                                               when emp.sal > 2800                                    then 'High'                                                               else 'Middle'                                                               end

Specify four bind parameters: bind_deptno, bind_seniority, bind_job, bind_earninglevel.

Add a usage for this ViewObject to the application module’s Data Model.

Time to move to the ViewController project for creating the Web Application.

Creating the ReadOnly Hierarchical Table based Web Page Report

Steps:

1. Create a new JSF page – HrmReport.jspx

2. Drag DeptView from the Data Control palette and drop as (read only) Table

3.Select the columns to display in the table. Make the table sortable by checking the appropriate checkbox.

Because the Tree Binding (in ADF 10g at least) does not support multiple node types per level, we cannot simply create a tree-binding that has DeptView as root with the three details ViewObjects as first level of detail nodes. We have to do some programmatic intervention.

4. Create a new Class DepartmentAnalyzer. Configure a managed bean DepartmentAnalyzer – request scope – based on this class.

<managed-bean>
  <managed-bean-name>DepartmentAnalyzer</managed-bean-name>
  <managed-bean-class>view.DepartmentAnalyzer</managed-bean-class>
  <managed-bean-scope>request</managed-bean-scope>
</managed-bean>

This bean will take the current Department row and expose its detail accessors – the rows from the three aggregate views.

5. Add three columns to the table created when you dragged/dropped the DeptView. Each column will hold one of the three aggregate view results.

In the first column, insert the following components and attribute settings:

<af:column headerText="Staff in Job">
  <af:iterator value="#{DepartmentAnalyzer.staffInJob}" var="staff" varStatus="status">
    <af:outputText value="#{status.index!=0?', ':''}#{staff.Job}  #{staff.NumInJob}"/>
  </af:iterator>
</af:column>

Note how the column – or rather each cell stamped in the column – contains an iterator that retrieves a collection called staffInJob from the DepartmentAnalyzer bean. For each element in this collection, an outputText is rendered that prints the value of the properties Job and NumInJob in the elements in the collection. For all elements but the first, it also prints a comma.

The getStaffInJob method is implemented as follows:

 public List<Map> getStaffInJob() {
    return getRowMapList("StaffInJobView", new String[] {"Job","NumInJob"});
 }
 private List<Map> getRowMapList(String deptAttribute, String[] attributes) {
   Row dept = ((JUCtrlValueBindingRef)getExpressionValue("#{row}")).getRow();
   ViewRowSetImpl vrsi = (ViewRowSetImpl)dept.getAttribute(deptAttribute);
   return viewRowSetToListOfMaps(vrsi, attributes);
 }
 private List viewRowSetToListOfMaps(ViewRowSetImpl vrsi, String[] attributes) {
   List<Map> rowMapList = new ArrayList();
   while (vrsi.hasNext()) {
            Row row = vrsi.next();            Map map = new HashMap();            for (String att: attributes) {                map.put(att, row.getAttribute(att));                            }            rowMapList.add(map);        }
return rowMapList;    }

Method getStaffInJob() calls the more generic method getRowMapList, passing in the name of the ViewAccessor name to be used on the current Dept Row to access the detail records – in this case the StaffInJobView – as well as a String[] with the names of the Attributes in the detail rows that should be included in the Map elements in the List. We will see somewhat later on that this generic method can easily be reused for the two other detail aggregates.

When we now run the page, we see  this:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport11

It is easy to add the details for the other two detail aggregates: the two additional columns are virtually similar to the one we just added – and the same goes for the counterpart methods in the DepartmentAnalyzer class.

<af:column headerText="Staff Seniority">  <af:iterator value="#{DepartmentAnalyzer.seniority}" var="staff"               varStatus="status">      <af:outputText value="#{status.index!=0?', ':''}#{staff.Seniority}  #{staff.NumInSeniority}"/>  </af:iterator></af:column><af:column headerText="Earnings">  <af:iterator value="#{DepartmentAnalyzer.earnings}" var="staff"               varStatus="status">      <af:outputText value="#{status.index!=0?', ':''}#{staff.SalaryRange}  #{staff.NumInSalaryrange}"/>  </af:iterator></af:column>

The corresponding methods in the DepartmentAnalyzer class:

    public List<Map> getSeniority() {        return getRowMapList("StaffSeniorityPerDepartment", new String[] {"Seniority","NumInSeniority"});    }    public List<Map> getEarnings() {        return getRowMapList("EarningLevelsPerDepartmentView", new String[] {"SalaryRange","NumInSalaryrange"});    }

Running the page now gives us:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport12

While this already gives us access to a lot of information, we want more of course. For example, we want to know who the employees are with a given Seniority in a Department. We would like to be able to disclose every value (Junior 2, Medior 5) to see a list of the actual employees.

To achieve this, we have to make some very simple changes.

First of all, in the method getSeniority() in the DepartmentAnalyzer class, we have to take into account the EmpView Accessor in the StaffSeniorityPerDepartment ViewObject and turn this ViewRowSet into a List<Map> just like we did before with the StaffSeniorityPerDepartment itself:

    public List<Map> getSeniority() {        List<Map> rows = getRowMapList("StaffSeniorityPerDepartment", new String[] {"Seniority","NumInSeniority","EmpView"});        // loop over the rows and for each row, turn the EmpView into a ListOfMaps with attributes Ename and Hiredate        for (Map row:rows) {            row.put("employees",  viewRowSetToListOfMaps((ViewRowSetImpl)row.get("EmpView"), new String[] {"Ename","Hiredate","Job"}));        }        return rows;    }

Now we can get the list of all employees with a given seniority using the employees property. We make use of this in a table component we add to the column we created for the Seniority:

            <af:column headerText="Staff Seniority">              <af:panelGroup layout="vertical">                <af:iterator value="#{DepartmentAnalyzer.seniority}" var="staff"                             varStatus="status">                  <af:showDetailHeader text="#{staff.Seniority}  #{staff.NumInSeniority}">                    <af:table value="#{staff.employees}" var="emp">                      <af:column headerText="Name">                        <af:outputText value="#{emp.Ename}"/>                      </af:column>                      <af:column headerText="Hiredate">                        <af:selectInputDate readOnly="true"                                            value="#{emp.Hiredate}"/>                      </af:column>                      <af:column headerText="Job">                        <af:selectInputDate readOnly="true"                                            value="#{emp.Job}"/>                      </af:column>                    </af:table>                  </af:showDetailHeader>                </af:iterator>              </af:panelGroup>            </af:column>

Note how the table is wrapped in a ShowDetailHeader – a component that allows toggling its content to be visible or hidden. Also note how the table is based on the employees collection available on the staff variable – the current row in StaffSeniorityPerDepartment.

Run the page and this is how it looks:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport13

One other way of making such information is available is through the tooltip – the bubble up text that appears when the mouse hovers over a piece of text in the page. We can for example show the list of Employee Names when hovering over the text values such as CLERK 2 and MANAGER 1 in the Staff In Job column.

Two small modifications are required: one to add the string with Enames to the Map created in getStaffInJob() and one in the page itself, to set the shortDesc attribute on the outputText component.

Here is how that goes:

in method getStaffInJob():

    public List<Map> getStaffInJob() {        List<Map> rows = getRowMapList("StaffInJobView", new String[] {"Job","NumInJob","EmpView"});        // loop over the rows and for each row, turn the EmpView into a ListOfMaps with attributes Ename and Hiredate        for (Map row:rows) {            List<Map> enames = viewRowSetToListOfMaps((ViewRowSetImpl)row.get("EmpView"), new String[] {"Ename"});            StringBuffer names = new StringBuffer();            for (Map ename:enames) {                names.append(", "+ename.get("Ename"));            }            row.put("EmpNames", names.substring(2));        }        return rows;            }

In the <af:column> in the page:

              <af:iterator value="#{DepartmentAnalyzer.staffInJob}" var="staff"                           varStatus="status">                <af:outputText value="#{status.index!=0?', ':''}#{staff.Job}  #{staff.NumInJob}"                shortDesc="#{staff.EmpNames}"/>
              </af:iterator>

Note: only the shortDesc attribute was added – that specifies the tooltip.

With this in place, when the mouse hovers over a text in the Staff in Job column, the tooltip indicates the names of the Employees involved:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport15

Note: the mouse hovered over CLERK 2 when the screenshot was taken.

Adding the Employees Table

The last thing we will add to this page is a table that displays employees. Not all employees but those employees that satisfy the condition specified by the user by clicking on a hyperlink in the page. Every row from each of the three detail aggregate views will be represented by a commandLink. When that link is clicked, the table will be refreshed.

Steps:

1. Add properties to DepartmentAnalyzer. Generate Accessor methods for each of them.

    private oracle.jbo.domain.Number deptno;    private String senioritylevel;    private String earninglevel;    private String job;

2. Drag the EmployeesView to the table, drop as Table.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport16

3. Wrap the Table inside a PanelHeader.

4. Bind Employees Table to DepartmentAnalyzer bean – that will make it easier to partially refresh the table from within the bean:

    private CoreTable employeesTable;    public void setEmployeesTable(CoreTable employeesTable) {        this.employeesTable = employeesTable;    }

And in the page

            <af:table value="#{bindings.EmployeesView.collectionModel}"                      ...                       binding="#{DepartmentAnalyzer.employeesTable}">

5. Also drag Operation ExecuteWithParams on the EmployeesView collection in the Data Control Palette and drop it as Command Button on the page.

Note: we do not care for the command button, we only need the ActionBinding in the PageDefinition and this is the easiest way to get it.

6. Go to the page definition and modify the NamedData elements – change the NDValue attribute to make them all refer to the DepartmentAnalyzer bean.

      <NamedData NDName="bind_earninglevel" NDType="java.lang.String"                 NDValue="${DepartmentAnalyzer.earninglevel}"/>      <NamedData NDName="bind_deptno" NDType="oracle.jbo.domain.Number"                 NDValue="${DepartmentAnalyzer.deptno}"/>      <NamedData NDName="bind_job" NDType="java.lang.String"                 NDValue="${DepartmentAnalyzer.job}"/>      <NamedData NDName="bind_seniority" NDType="java.lang.String"                 NDValue="${DepartmentAnalyzer.senioritylevel}"/>

Here we specify that the properties on the DepartmentAnalyzer bean are to be used to provide the values for the bind parameters on the EmployeesView.

7. Remove the CommandButton – ensure that the actionBinding stays.

8. Implement method showEmployees() in

    public String showEmployees() {        DCBindingContainer bc = (DCBindingContainer)this.getExpressionValue("#{bindings}");        FacesCtrlActionBinding requery = (FacesCtrlActionBinding)bc.getOperationBinding("ExecuteWithParams");        requery.invoke();        // make sure the employeesTable refreshes        AdfFacesContext.getCurrentInstance().addPartialTarget(employeesTable);        return null;    }

This method retrieves the ExecuteWithParams action binding and invokes it. The action binding will use the properties on this bean that we will set using setActionListener components on the CommandLink that invokes this showEmployees() method.

9. Wrap the outputText in the Staff in Job column in a CommandLink component. Assign an Id to the command link, set partialSubmit to true and have the action attribute reference the DepartmentAnalyzer bean’s showEmployees() method:

<af:commandLink id="jobLink" partialSubmit="true"                action="#{DepartmentAnalyzer.showEmployees}">   <af:outputText value="#{status.index!=0?', ':''}#{staff.Job}  #{staff.NumInJob}"                  shortDesc="#{staff.EmpNames}"/>   <af:setActionListener from="#{row.Deptno}"                         to="#{DepartmentAnalyzer.deptno}"/>   <af:setActionListener from="#{staff.Job}"                         to="#{DepartmentAnalyzer.job}"/></af:commandLink>

Use setActionListener elements to pass the values of deptno and the current job to the appropriate properties on the DepartmentAnalyzer bean, for use for the bind variables in the EmployeesView query.

Do something similar for the Earnings column:

<af:column headerText="Earnings">  <af:iterator value="#{DepartmentAnalyzer.earnings}" var="staff"               varStatus="status">    <af:commandLink id="earningsLink" partialSubmit="true"                    action="#{DepartmentAnalyzer.showEmployees}">      <af:outputText value="#{status.index!=0?', ':''}#{staff.SalaryRange}  #{staff.NumInSalaryrange}"/>      <af:setActionListener from="#{row.Deptno}"                            to="#{DepartmentAnalyzer.deptno}"/>      <af:setActionListener from="#{staff.SalaryRange}"                            to="#{DepartmentAnalyzer.earninglevel}"/>    </af:commandLink>  </af:iterator></af:column>

10. Run the page. When we click on one the ANALYST 2 link in the row for Department 30, the table is refreshed and now shows the relevant Employees.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport17

The same happens when we click on Middle 2 in the Earnings column for Department 20.

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport18

11. Make the PanelHeader title adjust according to the requested information

Add a method the DepartmentAnalyzer class:

    public String getSelectedEmployeesTitle() {        String title = "Employees for Department " + deptno;        if (job != null)            title = title + " and Job " + job;        if (senioritylevel != null)            title = title + " and Seniority Level " + senioritylevel;        if (earninglevel != null)            title = title + " and Earning Level " + earninglevel;        return title;    }

Slightly change the showEmployees() method in the same class

AdfFacesContext.getCurrentInstance().addPartialTarget(employeesTable.getParent());

This makes the method partial refresh the Table’s parent – which is the PanelHeader – rather than the table itself

Change the text attribute of the PanelHeader:

<af:panelHeader id="EmpDetPHTitle" text="#{DepartmentAnalyzer.selectedEmployeesTitle}">

Now run the page. The PanelHeader gives a clear indication of which employees are shown:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport19

And when I click on CLERK 2 in Department 10:

Creating a Read Only Hierarchical Table Report with ADF 10g hierarchicaltablereport20

Resources

Dowload JDeveloper 10.1.3.3 Application: HierarchicalTableReport.zip.

Related Posts

Tags:, , ,

About The Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.