Hi,
I need a bit of help with using formulas in summaries in the UltraWinGrid. I have a string column that can display, depending on the row, one of four things:
1. A decimal number in string form e.g. 1234.56
2. The string "<TO_RECALC>"
3. Another string e.g. "Incl", "Excl", etc
4. Nothing, i.e. empty string
I want to summary to show the following:
1. If all rows have a decimal number in that column then it will display the sum of those numbers. It will ignore empty string rows.
2. If any row has "<TO_RECALC>" in it, then the summary will display the same, no matter what values are in the other columns.
3. If all rows have the same string in them (no 3) then the summary will display that string, otherwise it will display '0'.
4. If all rows are empty string, then the summary will display empty string.
If anyone can giv e me an idea of how to do this using formulas, or possibly anything else, that would be fantastic!
Thanks,
Dane.
Thanks for your reply Mike. The custom calculator works well but now I have another related problem. I have a two or three level hierarchical grid. There is a column on each level called "Cost". The value of this cell in each row on each level should be the sum of the row's child rows' "Cost" values, with the last level being the values typed in.
For example:
A $100 (B+E)
--- B $40 (C+D)
------ C $10
------ D $30
--- E $60 (F+G+H)
------ F $15
------ G $20
------ H $25
I thought I would simply use summaries for these rows, then get the value of the summary and plug it into the parent row. The only problem is that I have to put these values in when the row is initialised, and at that point its children aren't initialised and the summaries don't add up properly. Any ideas on what I can do? Can i simply enter a formula in the parent columns saying =SUM(ChildRows("Cost")) or something like that?
Hi Dane,
I'm not sure I understand the problem. I assume that the last level has values in it already. So all you have to do is handle InitializeRow for the parent rows and get the SummaryValue on it's child rows collection. Are you saying that the SummaryValue doesn't have a value in InitializeRow? If it does not, you may have to force it by calling Summaries.Refresh.
Or, you might need to handle SummaryValueChanged in addition to InitializeRow.
If you want to use a Formula, I think there is a way to refer to the child rows of a particular row, but I don't know the syntax off the top of my head.
dnatoli said:It does work the first time, however when I modify a child row, the parent row doesn't update straight away as it needs to be refreshed to update the value. Its something I'll have to work out myself I think as its to do with the way we have extended the UltraGrid for our project.
It will update automatically if you use a Formula.
Or, you could trap InitiaizlieRow for the child row and then call Refresh on the parent row with an option of FireInitializeRow to force the InitializeRow event to re-fire for the parent row any time a child row changes.
dnatoli said:At the beginning of this thread I mentioned that I wanted to add up the values differently depending on what values I come across. Therefore the normal CalcManager won't evaluate the formula as I require (in fact because it is converting string values it just says #REF). So I was wondering if there was a way I could specify how it should sum up the values when evaluating the formula, e.g. if it comes across the string <TO_RECALC> then it should just return that value, otherwise return a decimal value.
Not sure exactly what you mean by this. But perhaps what you could do is add an unbound column to the child band and populate the cells of this unbound column with another formula. For example, you could set the value on the unbound column to a number from 0 to 10 based on the value in the string column. Then your parent row formula could operate on the unbound column.
It does work the first time, however when I modify a child row, the parent row doesn't update straight away as it needs to be refreshed to update the value. Its something I'll have to work out myself I think as its to do with the way we have extended the UltraGrid for our project.
At the beginning of this thread I mentioned that I wanted to add up the values differently depending on what values I come across. Therefore the normal CalcManager won't evaluate the formula as I require (in fact because it is converting string values it just says #REF). So I was wondering if there was a way I could specify how it should sum up the values when evaluating the formula, e.g. if it comes across the string <TO_RECALC> then it should just return that value, otherwise return a decimal value.
Have you actually tried this out using the InitializeRow event? Or are you just assuming it will not work. I could be wrong, but I'm pretty sure using InitializeRow will work. The reason is that once you attempt to refer to a row, the row has to be Initialized. So if you are in InitializeRow for the parent row and you try to loop through the child rows, those rows will be initialized and fire InitializeRow when you ask for the value of the cell. So the code will look a little weird if you step through it, since the InitializeRow event will appear to fire recursively, but it should work... I think.
The Formula is probably easier and more reliable, anyway, though. :)
dnatoli said:can I use a custom calculator to evaluate that formula?
I'm not sure what this means.
Sorry, I should have explained a bit better. I'm using an object based datasource, not datasets, and all the columns are based on properties of those objects. However there are some columns that need to be displayed that have no equivalent property, like "Cost", which are created as unbounded columns. Therefore during the InitializeRow event these unbounded columns are filled with the value they contain. You are correct in assuming the last level will have values. However when the InitializeRow event is fired for the parent row, the child rows haven't had this event fired yet and thus the unbounded columns aren't filled yet so the cells have no value. This means that I have to initialise the rows in a backwards order almost, children first then parents.
I'll try that formula you suggested, can I use a custom calculator to evaluate that formula?
Okay... to refer to a child band from a cell in the parent band looks something like this:
column.Formula = @"Sum([Band 1/Column 0])";
Band 1 in this case is the child band.
Column 0 is a column in the child band.