15 July, 2011

Summary Functions in ADF Business Component

SA,
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.

Thanks
Mahmoud A. El-Sayed

4 comments:

  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

    ReplyDelete
  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

    ReplyDelete
  3. 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.

    Thanks,
    Asha

    ReplyDelete
    Replies
    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

      Delete

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...