Thursday, November 1, 2012

ADF - Groovy for Total Sum of a Column in a Table

We will be looking in this article as how we can add Total of a column in a table. Very often we require to have total of a column in a table. For example , we might need total sum of Salary column in the table.
We will be leveraging power of Groovy in accomplishing this task.

So lets see how we can do this.

Use Case :- Add a Total Salary Attribute below Salary column in Employees Table.


Model Project

Considering that we have EmployeesView VO object which is based on Employees EO, go to attributes of VO and click add new attribute. Select "Add new Attribute". It will by default be transient. Give this attribute  name as TotalSal.











Now go to View Accessors tab and click on green + sign to create new View Accessor.

Select EmployeesView from the first window and shuttle it so that you can see like below.











Do ok and it should be like below screenshot.











Now go to attributes tab and select the new transient attribute (TotalSal).
Give its Default value as Expression and give Groovy expression as EmployeesView1.sum("Salary") .










View Project
Create a new page. Drag and Drop EmployeesVO from data control and create a new read only table "without" TotalSal Attribute.

Now go to Salary Column in Structure and do a right click and select footer from facet as shown below.










Now from EmployeesView1 in DataControl, drag and drop TotalSal column in this new footer facet.




Now go to bindings and create a new binding for this variable.














 And change the value of footer output text with this binding.






Save all and run this page.



Hope this was useful.

Happy Learning !!

Rohan Walia










16 comments:

  1. Thanks Rohan for this example. Have you tried it also with a filtered table? Applying a filter shows always the TotalSalary for all Employees or? Thx Gregor

    ReplyDelete
  2. Thanks for feedback Gregor,

    It does not work if the table is filtered. Can you provide the solution for the same?

    Thanks alot

    Rohan

    ReplyDelete
  3. I had no idea, but saw the thread in the Oracle ADF Forum:

    https://forums.oracle.com/forums/thread.jspa?threadID=2466118&tstart=0

    and the (Solution)Link posted by A.P.:

    http://adfpractice-fedor.blogspot.de/2012/05/working-with-vos-built-in-aggregation.html

    is working...Thx Greg.

    ReplyDelete
  4. Hi Greg,
    Can you please explain how to do this.

    How to publish aggregate function.

    Thanks

    ReplyDelete
  5. Very Helpful Information. Thanks

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thanks for your efforts... It worked for me..

    ReplyDelete
  8. Hi I have been trying to get a column sum to work, and running into issues. I found your post and basically did the exact same thing as you have here. But the problem I am running into is with my accessor when I put the groovy script in and do the accesssor.sum("mySum") I am getting a variable not defined error? Even though the accessor is set up. Everything has been saved and built several times??? Here is my code:
    @TransientValueExpression(attributeName="TotalBonus")
    def TotalBonus_ExpressionScript_Expression()
    {
    XxWfRatingView1.sum("BonusValue")
    }

    and this is the error: Error(64): [Static type checking] - The varaiable [XxWfRatingView1] is undeclared. @ line 64, column 1.

    Any suggestions? Appreciate any help you can offer.

    ReplyDelete
    Replies
    1. Hi KewlRunner. Did you find a solution, me too am getting the same error in 12c version.

      Delete
    2. I've found you need to not only add the transient attribute to the VO but also to the EO.

      Delete
  9. Hi,
    Iam facing issue is unexpected ? Expression in jws

    ReplyDelete