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

[HeatherTe]

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

[HaroldBa1]

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

[HeatherTe]

Perfect!! That worked great. Thanks so much.

Heather

[Doug_Den_Hoed__AtAppStore]

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

[HeatherTe]

Hi Doug,

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

Thanks for the suggestion!

Heather

[HeatherTe]

One other quesiton on this. It works great on the details tab but I noticed in summary view, it just says SUM: but no amount. Is there something I need to add to get the sum to show in that view?