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)
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
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
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
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
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
Ooooops, In this tutorial I use Jdeveloper 11g which is different in GUI than Jdeveloper 10g.
ReplyDeleteunfortunately, This solution is not supported in OAF
To do the same task in OAF, you can do the below steps
ReplyDelete1- 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
Hi,
ReplyDeleteI 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
You can add business rule in master entity object and validate your requirement.
DeleteThe best location to write your business logic is database level, therefore I prefer you write your code in database