i have a grid with two columns - FeeActive and FeeAmount. FeeActive is a boolean/checkbox. FeeAmount a decimal/currency. I want to sum only the FeeAmounts that are Active. Why will the following formula not work?
grid.DisplayLayout.Bands(0).Summaries(
"FeeAmount").Formula = "sum(if([FeeActive]=0,0,[feeAmount] ))"
I have used the fallback plan of creating a separate unbound calc column to sum on but this seems more than is necessary.
Any help making this work or understanding why it will not would be much appreciated.
Formulas are context-sensitive. So if you put a formula in a grid cell, then [FeeActive] has a context of the row the formula is in and it will interpret this as the value of the [FeeActive] cell in the same row.
But the same token in a summary, doesn't have a context of a cell. The summary belongs to the entire band. So [FeeActive] in this case refers to an entire column. Therefore, the IF function won't work, because an entire column is not a boolean value.
What you have to do in a case like this is use two formulas. What I would do is create an unbound column and put a formula on it which is very much like the one you have here. The column formula will result in either the [FeeAmount] or 0 depending on the value of [FeeActive]. Then you can sum the unbound column for your summary.