15 July, 2011

Summary Functions in ADF Business Component

I will present how to create summary function (sum, count, min, max and avg)   in ADF BC and clarify how these can be used at Entity level and View level. That is done using Groovy syntax.
I will use scott schema (EMP and DEPT Tables)

Entity Level
We will create example to get employees count in every departments.
First, we need to have association between entities representing master-detail relationship and the destination accessor name is what we are going to use in our groovy
Syntax: <Accessor>.aggregate_function(Groovyexpression)

1-Note down the destination accessor name(EMP) in the association or Accessor Attribute name in source entity
2- Add a transient attribute in source entity with persistent property set to false and provide the groovy expression in the syntax provided above
we created attribute "EmpCount" that refer to count of employees in every department

View Level
First, we need to have a view link between viewobjects representing master-detail relationship and the destination accessor name is what we are going to use in our groovy
Syntax: <ViewLinkAccessor>.aggregate_function(Groovyexpression)

1- Note down the destination accessor name(EmpView) in the view link or viewLinkAccessor name in source view

2- Add a transient attribute in view object and provide a groovy aggregate function count as a value to it in the syntax provided above

Let's now run application module tester and execute DeptView / ViewLink, you should see employee count in EmpCount field
In similar way, one can use other groovy aggregate functions sum, avg, min and max.

Mahmoud A. El-Sayed


  1. Hi Sir ,
    I go through this , but i am using Jdeveloper and when i open view attribute ,I cant find the property set and value type column in View attribute panel can u please tell why this is so ?
    or there is difference between OAF and ADF?

    1. Ooooops, In this tutorial I use Jdeveloper 11g which is different in GUI than Jdeveloper 10g.
      unfortunately, This solution is not supported in OAF

    2. To do the same task in OAF, you can do the below steps
      1- Add new attribute to view object
      2- check "mapped to column or sql"
      3- Write in "Expression" the sql query to get your summary data for example
      select count(1) from Detail_table

  2. Hi,
    I am able to count the number of rows in the child table. However i want to add a validation something like number of employees for a department should not be more than 3. Please let me know how to perform this validation using Groovy.


    1. You can add business rule in master entity object and validate your requirement.
      The best location to write your business logic is database level, therefore I prefer you write your code in database


ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...