Friday Mar 02, 2012

Refreshing One Column based on the value of Another Column in ADFdi Table

When using ADF Desktop Integration, quite frequently, we get into a situation where we would like to refresh one column based on the value of another column. In ADF Faces, we can achieve this by setting the autoSubmit property and partialTriggers property for the corresponding columns.

However, in ADFdi, we do not have such option. Though we can achieve this by using LOVs and Dependent LOVs. But, in some scenarios we would like to achieve this when using an Input Text Component.

In this article, we will simulate this Auto Refresh functionality in a ADFdi Table.

Note : Since we would be using VBA code to achieve this, we can use this only on the Macro Enabled Excel Workbooks.

Let us assume that we have a View Object based on the Emp table. We could take an example of having a transient attribute in the VO, that gives the sum of Salary and Commission attributes.


In the above example, we've added a new transient attribute (SalPlusComm) to the EmpVO, that would give the sum of Sal and Comm attributes. Since we need this attribute to get refreshed when either Sal or Comm attribute changes, we set the Sal and Comm attributes as Dependencies. Also, we set the AutoSubmit property (under UI Hints tab) for the Sal and Comm attributes.


Now, we are done with the model layer. We can now, create a jspx page and then Drag and Drop EmpView as ADF Table. After this, we create an Excel Workbook (macro enabled), enable ADF Desktop Integration for it, set the required Workbook Properties, and then add a Table based on the EmpView.


As there are no straight forward way in ADFdi to trigger a request to server when a value of a cell is changed, we will now add a DoubleClickActionSet for the Sal and Comm columns. This DoubleClickActionSet will have the Table.RowUpSync and Table.RowDownSync actions.


Above example image shows the DoubleClickActionSet for Sal column. In the same manner, we need to add the DoubleClickActionSet for the Comm column as well.

Now, we have the workbook, that would fetch the SalPlusComm attribute (after recalculation in the model), when we change the Sal / Comm attribute and then double click on that column. To do this automatically when the user tabs out / presses enter key on the cells, we'll write a bit of VBA Code on the Worksheet where we've this table (Go to Developer Tab and Click on Visual Basic).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 14 Or Target.Column = 15 Then
    Application.CommandBars("Cell").Controls("Invoke Action...").Execute
  End If
End Sub
Excel would trigger Worksheet_Change event when a cell in the worksheet is modified. So, we would code our logic in that event. The above code assumes that Sal column is present in the N (14th) column on the worksheet and Comm column is present in O (15th) column. So, we would execute our logic only when the contents in these two columns change.

ADFdi would provide a context menu (Invoke Action...) when a DoubleClickActionSet is added to a particular column. We'll make use of that context menu and invoke it programatically.


We invoke that context menu programatically using the following line of code

Application.CommandBars("Cell").Controls("Invoke Action...").Execute

Now, we run our workbook, modify the value of Sal column for any row and tab out of that field would automatically update the value of SalPlusComm column.


Here, a simple example (Transient Attribute) is taken for the explanation. In the similar fashion, we can also have a DoubleClickActionSet to contain a method in the Impl that would perform this calculation as well.

Tip: If you are not able to view the image fully, right click on the image and choose View Image option to see it completely.

Wednesday Feb 22, 2012

Dynamic Pie Graph Generation by Drag And Drop Rows from ADF Table

In normal scenarios, we display the graph and chart to get graphical representation of data. In this article, we'll see how to create graph dynamically by dragging the contents from ADF table and dropping on the graph.

Environment : JDeveloper PS5 (

Assuming that we have a table that displays the Department's details. We'll build a dynamic pie graph to display the Employee details for the departments that are dragged from the Department's table.


For our usecase, we need to drag the Departments from Table and Drop it on the Pie Chart to get the Employees details corresponding to the Depts selected.

For this, we need to add DragSource in the Dept Table and DropTarget in Emp Chart.

<af:table value="#{bindings.DeptView1.collectionModel}" var="row"
                 emptyText="#{bindings.DeptView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                 rowSelection="multiple" id="t1">
            <af:dragSource discriminant="Dept" 
                  <dvt:pieGraph id="pieGraph1" subType="PIE_MULTI"
                      <af:dataFlavor flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"

Now that we've added the drag source and drop target, we need to have a method in our AM/VO's impl class, that take the department number as input and return the list of employees under it and their details as ArrayList.

    public ArrayList getEmpDetsForDept(int Deptno) {
        ArrayList empDetsAL = new ArrayList();
        getEmpView1().setWhereClause("Deptno=" + Deptno);
        if(getEmpView1().getRowCount()>0) {

            empDetsAL.add(new Object[]{""+Deptno,
                                        new Double(getEmpView1().getCurrentRow().getAttribute("Sal").toString())}

            while (getEmpView1().hasNext()){
                empDetsAL.add(new Object[]{""+Deptno,
                                            new Double(getEmpView1().getCurrentRow().getAttribute("Sal").toString())}

        return empDetsAL;

Now, the main part comes. Pie Graphs can be based on ArrayList. So, in our backing bean, we'll have an attribute of type ArrayList. Also, we'll have a variable to to bind the graph for triggering PPR.

    private List chartData=new ArrayList();
    private UIGraph empChart;

    public void setChartData(List chartData) {
        this.chartData = chartData;

    public List getChartData() {
        return chartData;

    public void setEmpChart(UIGraph empChart) {
        this.empChart = empChart;

    public UIGraph getEmpChart() {
        return empChart;

As we need to find out the Departments that are dragged from table, we'll have a method in the backing bean to get the list of dragged Departments, iterate through them, pass each to the AM / VO method created to get Emp details and then construct the ArrayList.

    public DnDAction dropDeptInPie(DropEvent dropEvent) {
        RichTable table = (RichTable) dropEvent.getDragComponent(); 
        Transferable t = dropEvent.getTransferable(); 
        DataFlavor<RowKeySet> df = DataFlavor.getDataFlavor(RowKeySet.class, "Dept"); 
        RowKeySet rks = t.getData(df); 
        Iterator iter = rks.iterator();
        if(getChartData()!=null) getChartData().clear();
        while (iter.hasNext()) { 
            List key = (List); 
            JUCtrlHierNodeBinding rowBinding = (JUCtrlHierNodeBinding) table.getRowData(); 
            Row row = (Row) rowBinding.getRow();
            String Deptno = row.getAttribute("Deptno").toString();
            BindingContainer bindings = getBindings();
            OperationBinding operationBinding = bindings.getOperationBinding("getEmpDetsForDept");
            operationBinding.getParamsMap().put("Deptno", Deptno);
            ArrayList result = (ArrayList)operationBinding.execute();

            if (operationBinding.getErrors().isEmpty()) {
                else  setChartData(result);



        return DnDAction.NONE;


    public BindingContainer getBindings() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();

In order to make the Pie Graph use the ArrayList as source, we need to bind this to the tabularData Property of the pie graph. Let us also bind the chart to the variable we created in backing bean and set its dropListener to the method created above. Now, our pieGraph's source in jspx page would look like

                  <dvt:pieGraph id="pieGraph1" subType="PIE_MULTI"
                    <af:dropTarget dropListener="#{pageFlowScope.DnDBean.dropDeptInPie}">
                      <af:dataFlavor flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"

Now, let us run the page, select couple of Departments and Drop them on the Pie chart.


Here, we've dragged the departments 10 & 30, and dropped them on the chart to get the Salary of the employees belonging to those departments as slices.

Thursday Feb 02, 2012

Drag ADF table record & drop on bar graph in Jdeveloper

In Jdeveloper,we can drag any component & drop on graph.It also supports drag/drop between graphs.

Use Case Description

Let us consider a use case wherein we will drag table row & drop that on the bar graph.

Implementation steps

Assume that we have table with Emp records & bar graph that displays Empno & Salary of Employees.

Insert drag component inside table:  <af:dragSource discriminant="tab"/>

Now insert dropTarget inside barGraph:

<af:dropTarget dropListener="#{test.handleTableDrop}">
       flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"    discriminant="tab"/>

public DnDAction handleTableDrop(DropEvent dropEvent)

        // Add event code here...
        System.out.println("Dropped id is"+dropEvent.getDragClientId());
        return DnDAction.NONE;

Run the above page .Drag any table row & drop that on the bar Graph.
Note that handleTableDrop method gets executed & it prints dragged client id(i.e table id).

Wednesday Feb 01, 2012

Custom Upload options in ADF Desktop Integration


     In ADFdi enabled excel workbook while uploading the changes performed in ADF Table,standard upload options dialog appears like below.


    In this article,we will see how to customize these options in a dialog.


     Let us take a simple example of Employee table.


Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee table as below screenshot is available.


Now let's create a new jspx page titled 'UploadOptions.jspx'.
The below code adds two checkboxes and buttons.Let's copy this into the Source view of jspx page.

<!-- Check box for AbortUploadOnFailure -->
<af:selectBooleanCheckbox text="Abort Upload On Failure" id="sbc1"
  value="#{requestScope.abortUploadOnFailure}" autoSubmit="true" selected="true"/>

<!--Check box for DownloadAfterUpload -->
<af:selectBooleanCheckbox text="Download After Upload" id="sbc2" autoSubmit="true"

<!--Command button for Continue action -->
<af:button text="Continue" id="b1" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="continue" to="#{requestScope.action}"/>

<!-- Command button for Abort action -->
<af:button text="Abort" id="b2" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="abort" to="#{requestScope.action}" />

The UploadOptions.jspx page now looks like below:


Below Span elements have to be used to perform custom upload actions in ADFdi Table.

ADFdiCloseWindow - When a web page has to be closed,this span element can be set to
Continue  - to close the web page and invoke next action in actions set or
Abort       - to close the web page and terminate the action set.
ADFdiAbortUploadOnFailure - If this element is set to True, the action set stops uploading if it encounters a failure. If the element references False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload.
ADFdiDownloadAfterUpload - If this element is set to True, the action set downloads data into the ADFdi Table component after the action set uploads modified data.

Let us add the below code at the end of tag Form in source view of jspx,to include the above mentioned span elements.

<!-- Closes the Upload Options dialog and then invokes next action in the action set-->
<f:verbatim rendered="#{requestScope.action eq 'continue'}">
<span id="ADFdiCloseWindow">Continue</span>

<!-- Closes the
Upload Options dialog but aborts next actions in action set-->
<f:verbatim rendered="#{requestScope eq 'abort'}">
<span id="ADFdi

<!-- If the span element is set to
  True, the action set stops uploading if it encounters a failure.
  False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload. -->
<span id="ADFdiAbortUploadOnFailure">${requestScope.abortUploadOnFailure}</span> 

<!--If the span element is set to
True, the action set downloads data from the Fusion web application to the ADF Table after the action set uploads modified data -->
<span id="ADFdi

In ADFdi excel workbook,let us add this new jspx file as dialog before Table.Upload action in Ribbon Commands of Worksheet Properties.


Now we run the excel workbook and download data into ADF Table as shown above.Let's update a row by changing the Sal=4000 of Martin.


On clicking Upload Ribbon command like below:


the custom upload actions dialog gets opened with 'Abort Upload On Failure' check box selected by default.

We can select 'Download After Upload' check box and click on Continue button.We can see data updated and downloaded again with new value like below:


This is a simple example of how upload options can be customized. Using this, we can add more custom options to the dialog, which is not available in the standard upload options dialog. We will see such options sooner in my next blog.

Wednesday Aug 24, 2011

Creating Matrix Report using ADF Pivot Table

Matrix Report can be achieved using ADF Pivot Table, we have to set drill down parameters to have hierarchical view in the report. Data Aggregation option allows us to use mathematical operations like Sum, Average, Count, Maximum, Minimum, Standard Deviation, and Variance on data.
This article shows how to create a Drill down Matrix Report with Department wise Total Salary using ADF Pivot Table.

  • Create Business Components from DEPARTMENTS and EMPLOYEES tables of HR schema.
  • Drop EmployeesView1 from Data Control Palette to jspx page as ADF Pivot Table.

  • In Select display attributes panel first drop DepartmentId from 'Available Attributes' list to 'Row and Columns edges', drop EmployeeId below DepartmentId in 'Row and Columns edges'.
  • From 'Available Attributes' list drop FirstName, LastName, Salary, JobId to 'Data Labels' section.
  • In 'Configure drilling' panel select 'Insert Drilling' option, Set 'Insert Parent Row' as 'After Children'. Check enable Drill Path for Drill Down report. Drilling option gives a Tree structure for the selected Attributes. Drill Paths are derived from the Attributes dropped in 'Row and Column Edges' In Select display attributes panel.

  • Add 'Salary' attribute to configure Data Aggregation.

  • Select 'Category Totals' tab and add Categories Attribute as 'DepartmentId' and 'Insert Total' as 'After'.

  • Configure sorting categories based on DepartmentId attribute in ascending order.

  • Finish the wizard and run the page.
  • Expand DepartmentId to find the Employees, also note the Department wise Salary and Total Salary.


Wednesday Aug 03, 2011

Passing parameter to EJB Method in taskflow

This article describes on passing parameter to EJB Method in taskflow.

Use Case Description

Suppose we have a table displaying Dept records.Table also has column link & clicking on this link displays a region in popup.This region accepts deptno parameter,executes EJB Method & displays respective Emp details in a popup.

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp & Dept table .Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Edit & create a custom named query that returns Emp Object for the respective deptno :

 @NamedQuery(name = "findEmpByDeptno", query = "select o from Emp o
 where o.dept.deptno = :deptNo")
Let us create Stateful Session Bean and expose the Named Query through the Session Facade.Our use case deals with passing parameters to EJB method in taskflow.So we will define simple EJB method that just accepts deptno parameter & stores its value in private variable:
 public void passDeptno(BigDecimal deptNo) 

Now that we have separate method to accept parameter we will modify findEmpByDeptno namedQuery as below:

 public List<Emp> findEmpByDeptno() 
 return em.createNamedQuery("findEmpByDeptno").setParameter
 ("deptNo", this.getDepartNo()).getResultList();

Generate Data control for the above Session Bean & expose the above method through the Session Facade.

Create a ADF taskflow with page Fragments & create an inputParameter 'inputParameter1' in its overview parameters tab:


Drop passDeptno(BigDecimal) as Method Call activity with value as
#{pageFlowScope.inputParameter1}.Drop findEmpByDeptno()->Operations->execute operation as Method Call activity & then drop  emp view activity on the taskflow.Define all control flows.

Create emp.jsff page fragment & drop findEmpByDeptno()->Emp as ADF Table with single row selection


Now we will create main.jspx page & drop deptFindAll as ADF table with single row selection on this.We will also create deptno attribute bindings in page definition file:

Insert a new column inside the table that displays Emp Details.Drop the above created taskflow as region inside the column.Pass '#{sessionScope.deptno}' value for inputParameter1 & set the taskflow's ifNedded property to 'Refresh'.Here we are using setPropertyListener to pass parameter to taskflow method:

<af:column id="c4" headerText="Emp details">
<af:commandLink text="Emp details" id="cl1">
<af:showPopupBehavior popupId="p1"/>
<af:popup id="p1" contentDelivery="lazyUncached">
<af:dialog id="d2" title="Emp Details" type="none">
<af:setPropertyListener type="popupFetch"

Run the page,it shows Dept records.Select any dept record & click on 'Emp Details' link.It shows popup with its respective Emp records


Tips and Tricks from Oracle's JDeveloper & ADF QA


« July 2016