Can you use the SUM (aggregator) feature on an expression field in a report? | Community
Skip to main content
H
HeatherTe
Level 2
October 7, 2020
Question

Can you use the SUM (aggregator) feature on an expression field in a report?

  • October 7, 2020
  • 2 replies
  • 999 views

I have a field that pulls either the budget, planned cost, or actual cost based on the status. I am wanting that column to sum based on groupings but it doesn't appear to show anything. I'm not sure if this can be done since it is an expression.

Here is what I have:

aggregator.displayformat=currencyStringCurrencyRounded

aggregator.function=SUM

aggregator.valueformat=doubleAsDouble

displayname=Forecasted

textmode=true

valueexpression=IF({status}='CPL',{actualCost},IF({status}='RSD',{actualCost},IF({status}='CUR',{plannedCost},IF({status}='APR',{budget}))))

valueformat=currencyStringCurrencyRounded

Each of those fields will sum in their own columns but not in my expression column

Thanks!

Heather

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    2 replies

    H
    HaroldBa1
    Level 2
    October 7, 2020

    Hi Heather.

    It seems to me that all you're missing is the "aggregator.valueexpression=..." piece. Try adding the code below to your text mode code. It worked for me.

    aggregator.valueexpression=IF({status}='CPL',{actualCost},IF({status}='RSD',{actualCost},IF({status}='CUR',{plannedCost},IF({status}='APR',{budget}))))

    Thanks,

    Harold

    H
    HeatherTeAuthor
    Level 2
    October 7, 2020

    Perfect!! That worked great. Thanks so much.

    Heather

      Doug_Den_Hoed_AtAppStore
      Community Advisor
      Doug_Den_Hoed_AtAppStoreCommunity Advisor
      Community Advisor
      October 7, 2020

      Hi Heather,

      Glad you got it going; I was just taking a look at this, too.

      In addition to Harold's tip to get the SUM working at the grouping level, I'd also suggest you add a final "else" case of zero as a default on both the aggregator and the data itself to avoid having nulls from incomplete data on projects with some status not considered in your logic:

      aggregator.valueexpression=IF({status}='CPL',{actualCost},IF({status}='RSD',{actualCost},IF({status}='CUR',{plannedCost},IF({status}='APR',{budget},0))))

      valueexpression=IF({status}='CPL',{actualCost},IF({status}='RSD',{actualCost},IF({status}='CUR',{plannedCost},IF({status}='APR',{budget},0))))

      Regards,

      Doug

        H
        HeatherTeAuthor
        Level 2
        October 7, 2020

        Hi Doug,

        Thank you. That is a great suggestion and I will add that in to the syntax too.

        Thanks for the suggestion!

        Heather

          Terms & ConditionsAccessibility statement

          Loading footer...