Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Newbie questions: how to use an aggregate in a footer formula?

  1. #1
    Join Date
    Nov 2013
    Posts
    382

    Default Newbie questions: how to use an aggregate in a footer formula?

    Hi,
    more 'simple' things I cannot find

    I can print an aggregate (sum) of a column in a group footer ... but I find no way to use this sum in a formula.

    Example, I have the sum of field1 .... and I want a foot calculus round(sum_filed1*K;2) ... and nothing I try seems to work. I cannot round every single line because the results can be diferent, I need to round the sum. Let's see an example with K=0.04:

    Code:
    Grup Header       Name      F1    __  K          F1*K     round(F1*K;2)
    Detail            Name1    17.38     0.04    0.6952     0.70
    Detail            Name2    17.38     0.04    0.6952     0.70
    Grup footer                34.76             1.3904     1.40  ====>>>> but I need round(34.76*0.04;2)=round(1.3904;2)=1.39 and NOT 1.40
    I'm trying to create a new calculated field directly on the footer to round up the actual sum, with no success!

    How can I use the aggregate on the footer formula?

    Thks

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Add a named function (ItemSumFunction) to you report. Set both the field to aggregate and the group on which to reset and give it a meaningful name. Then you can reference it in other functions by its name.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Nov 2013
    Posts
    382

    Default

    I already have a named function that calculates my desired value, but there is no way to select the group on which to reset ... ???? I guess there is something that I am missing.

    The previous post was just an example, my actual code looks like


    f Functions
    Open Formula: Base4

    The only attributes available are:

    Required
    Name: Base4
    Formula: =if([field1]=1;Field2;0)
    Optional
    Fail on error:
    Dependency Level: 0

    At footer level I can print the sum of Field2 for rows where field1=1 using

    Field: Base4
    ---
    Aggregation-type: Sum

    And it works.

    But now I want to create an expression using this sum ... and no way. If I create another
    Field: Base4
    Value: =123 .................................... it prints 123 as expected
    Aggregation-type: Sum

    but I need to calculate something like
    Value: = [Base4]*4 ........................... it prints the (LAST ROW Base4 value)*4, and not the sum*4




    Where am I lost?

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Use "Data->Add Expression/Function" and in that dialog, select to add an Sum-function. Then configure that sum function to compute the sum.

    When you set an aggregate on a field directly, this generates a private/hidden aggregate, that cannot be used elsewhere (as it has no publicly visible name). It only serves for printing the computed value and is only useful when printing is all you want from it.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Nov 2013
    Posts
    382

    Default

    Thanks a lot! It took me a while to understand but it's clear like water now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.