Apologies if this has been previously answered.
I'm attempting to do something in the grid that is somewhat uncharted waters for me.
I've implemented a way for our users to access the formula builder dialog and use that formula in a custom summary (using SummaryType.Formula).
One particular user desired to group by a column (that produced exactly two rows for each column), then add a summary value who's value is the difference between cell A and B for the two different rows *in the same column*.
So for example if I had columns Sales, Cost, and Profit and rows 1& 2, the summary value would be Sales(1) - Sales (2) for each group.
They managed to figure out they could write such a formula using the following notation
Formula = [Sales(0)] - [Sales(1)]
I had never, nor did I know it was possible to, include sub values in the formula. However this actually produced almost exactly the result they wanted. First my question:
1) Is it problematic to even allow this? I'm comfortable with using formulas in columns where the referenced column names don't include subvalues (ie a column using a formula [Sales] - [Cost] implies using the row's current value, there's no need to say [Sales(currentRow)]. But this opens up a whole host of possibilities that I'm not sure is even intended.
If the answer is yes, my two problems:
1) When the summary was printed in the grand total line it printed the Sales value of the first group's cell 0 - the Sales value of the first group's cell 1 when it was expected (by the user anyway) that it would print the Sum of all cell 0's - Sum of all cell 1's. Is there some way to write this formula so it works for all cases PLUS the grand total?
2) I seem to be having issues saving these summary settings (using our own code, not infragistics) and then reapplying them. They look fine when first applied, but on reapplication and re-display every summary value prints as "Value = #,##0.00". Any ideas (I can include code if it would help).
Thanks for your time,
Chris Rowland
Hi Chris,
puckstopper_37 said:1) Is it problematic to even allow this? I'm comfortable with using formulas in columns where the referenced column names don't include subvalues (ie a column using a formula [Sales] - [Cost] implies using the row's current value, there's no need to say [Sales(currentRow)]. But this opens up a whole host of possibilities that I'm not sure is even intended.
What kinds of problems are you concerned about?
The worst-case scenario here is that the formula calculation will fail for some reason and the Summary will display an error code. For example, if the grid is ungrouped or one of the GroupByRows only contains a single row, the Summary will display a #REF error, since the formula will be referencing a row that does not exist.
This will never crash the application or raise an exception if that is what you are concerned about.
puckstopper_37 said:1) When the summary was printed in the grand total line it printed the Sales value of the first group's cell 0 - the Sales value of the first group's cell 1 when it was expected (by the user anyway) that it would print the Sum of all cell 0's - Sum of all cell 1's. Is there some way to write this formula so it works for all cases PLUS the grand total?
No, the summary will apply to the entire band, so the Grand Total for a summary like this probably won't work at all. Or, best case, it will end up summing the first two GroupByRows, which is, of course, not good.
puckstopper_37 said:2) I seem to be having issues saving these summary settings (using our own code, not infragistics) and then reapplying them. They look fine when first applied, but on reapplication and re-display every summary value prints as "Value = #,##0.00". Any ideas (I can include code if it would help).
I can't think of any reason this would happen off the top of my head. I assume you are applying the exact same formula string.
When and how are you re-applying the formulas? Are you talking about stopping the application and running it again, or are you just re-binding the grid? Is the grid using the same Groupings that it had before?