I have a problem with my summary row. I wanted to calculate an average of column on my own with a formula and round it with 0 decimals. Therefore I use the following piece of code:
band.Summaries.Add(GUI_Res.SummaryKeyIntervalAvg, SummaryType.Formula, band.Columns[14], SummaryPosition.UseSummaryPositionColumn); UltraGridColumn col = band.Columns[14]; string formula = "round(average(if(isblank( [" + col.ToString() + "]) , 0 , [" + col.ToString() + "] )),0)"; band.Summaries[GUI_Res.SummaryKeyIntervalAvg].Formula = formula; band.Summaries[GUI_Res.SummaryKeyIntervalAvg].DisplayFormat = "Avg = {0}";
If I change the SummaryType to Average, a value is shown. With the code above, I only see the Caption "Avg =".
Am I doing anything wrong here or what could be the problem? Is it possible to use SummaryType.Average and format the output (e.g. no. of decimals)?
I am using v.7.2
Hi,
The formula you have here doesn't make sense. the Average function takes a list of values - a range reference. You are passing in the results of the if function which returns a boolean (a single value). So you are not getting the average of anything useful.
It looks to me like you want to take the average of the column but you want to ignore blank values. There's no way to do that with a formula or using the Average summary. The only way to ignore certain rows in the calculation would be to create your own custom SummaryCalculator (using the SummaryType.Custom.
Hi Mike,
Thanks for the answer. I solved the problem by implementing the ICustomSummaryCalculator like you suggested. But anyway, the reason I tried the other thing before was because I found it like that in your knowledgebase: http://devcenter.infragistics.com/Support/KnowledgeBaseArticle.aspx?ArticleID=10019
Seems I got on the wrong track with this.
Thanks for your quick answer, great work.
Wow, that's pretty amazing. That article is wrong and I will have it removed or modified.
Here's what happens when this formula gets evaluated:
"average(if(isblank( [Column 1]) , 0 , [Column 1] ))"
1) isblank([Column 1]) will ALWAYS return false. because [Column 1] is a range of cells. It's not a string. And it does not get evaluated one cell at a time.
2) Therefore, the 'if' statement here will always return [Column 1]
So you could replace this function with "average([Column 1])" and it would be the same thing.
But the sample provided with this KB article actually works and ignores empty cells. The reason for this is that I was wrong above - the average function actually ignores empty cells, anyway. So you probably don't need an ICustomSummaryEvaluator after all.