Expand my Community achievements bar.

SOLVED

Summarize column of calculated fields

Avatar

Level 3

I have a report that is pulling a column for a calculated field that we use on a custom form and am trying to figure out how to summarize it (by SUM) so that it shows the total at the grouping level. 

 

Is there text mode that can accomplish this?

 

Monthly_Projects___Retail___Brand_-_past_12_months.png

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi Kasey,

 

The better approach is definitely to get the data format right. This will enable you to great graphs/charts from the data in the future should you need to.

 

If however, you can't change the format (since you can't amend once the field is created and making a new field might be problematic), you could use the following text mode for the column in your report:

 

aggregator.displayformat=HTML
aggregator.function=SUM
aggregator.valueexpression=NUMBER({DE:FIELD NAME})
aggregator.valueformat=HTML
displayname=
linkedname=direct
namekey=FIELD NAME
querysort=DE:FIELD NAME
textmode=true
valueexpression=NUMBER({DE:FIELD NAME})
valuefield=FIELD NAME
valueformat=customDataLabelsAsString

This should SUM all of the values into your group header.

 

Best Regards,

Rich. 

View solution in original post

4 Replies

Avatar

Community Advisor

Hi Kasey,

 

As long as your calculated field is in a number format, you should have the ability to summarise that column by SUM from within the report builder, without any need for text mode. Simply click on that column when configuring your view, and there is a drop-down menu where you can choose how you want to summarise that column 

 

RichardLe1_0-1670365654744.png

 

Best Regards,

Rich

 

Avatar

Level 3

Thank you!! I don't have that option at the moment - but when looking into my calculated field on the form it's set to "text" format rather than "number" but does not allow me to change it.. which must be the issue... I will look into how I can fix this! I appreciate your help  

 

 

Monthly_Projects___Retail___Brand_-_past_12_months-2.png

 

 

 

 

Setup-6.png

Avatar

Correct answer by
Community Advisor

Hi Kasey,

 

The better approach is definitely to get the data format right. This will enable you to great graphs/charts from the data in the future should you need to.

 

If however, you can't change the format (since you can't amend once the field is created and making a new field might be problematic), you could use the following text mode for the column in your report:

 

aggregator.displayformat=HTML
aggregator.function=SUM
aggregator.valueexpression=NUMBER({DE:FIELD NAME})
aggregator.valueformat=HTML
displayname=
linkedname=direct
namekey=FIELD NAME
querysort=DE:FIELD NAME
textmode=true
valueexpression=NUMBER({DE:FIELD NAME})
valuefield=FIELD NAME
valueformat=customDataLabelsAsString

This should SUM all of the values into your group header.

 

Best Regards,

Rich. 

Avatar

Level 3

You are a life saver! That worked perfectly. Thanks so much!