Hi,
I Have a table with 2 columns A, B
A contains Amounts. B must contain the percentage of those amount (100*B/Sum[A]). This is my snippet:
DataTable dt = new DataTable();
dt.Columns.Add(
"Amount", typeof(Double));
"PercentageAmount", typeof(Double));
dt.Rows.Add(2);
dt.Rows.Add(8);
ultraGrid1.DataSource = dt;
ultraGrid1.DisplayLayout.Bands[0].Columns[
"PercentageAmount"].Formula = "100*[Amount]/Sum([Amount])";
ultraGrid1.CalcManager =
new UltraCalcManager();
I expect to get the following result:
Amount PercentageAmount (%)
2 20
8 80
I am getting
2 100
8 100
The Sum is operating locally (Row context) Is there a way to handle this?
Thanks for any help
Formulas are sensitive to context. So when you apply a formula to a cell, the cell assumes that any reference to a column is a reference to a cell in the same row. This makes it easy to make a column whose values is based on the other cells in the same row.
So in your example, your SUM function is operating on a single cell and not the entire column.
You can tell it to use the whole column by using the following syntax:
"100 * [Amount] / Sum([Amount(*)])";
Notice the (*) - this tells the CalcManager to treat the reference as the entire column instead of just the related cell.
However, this calculation is very inefficient. For every cell in the PercentageAmount column, the sum will be re-calculated, and you really only need to calculate it once.
So to be most efficient, I would add a summary to the grid and sum the Amount column. Then you can use the key of the summary to refer to the sum of the entire column in your formulas and it will only be calculated once. The summary need not be visible.
Thanks a lot Mike! The (*) trick works fine!! Do you know where can i find the formula syntax references?
Now i am trying to enhance efficiency as you adviced, but what i did, did not succeed:
ultraGrid1.DisplayLayout.Bands[0].Summaries.Add(
);
;
I also tried "100*[Amount]/[SumAmount]" without success.
Rafik said:Do you know where can i find the formula syntax references?
I'm not sure. I searched the docs and didn't find any general overview like this, but that doesn't mean there isn't one. I will do a little more digging and let you know if I find one. If you have any other questions about the formula syntax, though, please feel free to ask. :)
Not sure why the summary is not working for you. What happens with the formula you have here? Does the summary work?
I'm a little confused by your last sentence there, too. You say you "also tried" and then you listed exactly the same formula as the one above. They are both right, so it probably doesn't matter.
oh sorry Mike, I meant: I also tried "100*[Amount]/[SumAmount(*)]" without success.
here i put SumAmount(*) instead of SumAmount
I got the same result for both:
Yes!!!!!!!!! It works !
thanks a lot life (job) saver J
Okay, I see the problem. Summaries have a special syntax, too.
Try this:
"100*[Amount]/[SumAmount()]"
Just empty parens after the key of the summary. This is to distinguish a summary from a column, since they can have duplicate keys.