Mike,

I tried what you suggested above but the aggregate value column is null and when I expand the rows, I get #REF as column value for individual row

Here is my code for InitiaizeLayout method of the grid.  What am I doing wrong here?

SummarySettings summary;

            UltraGridColumn columnToSummarize = e.Layout.Bands[0].Columns["Req"];
           
            if (!(e.Layout.Bands[0].Summaries.Exists(columnToSummarize.Key)))
            {
                summary = e.Layout.Bands[0].Summaries.Add("ReqSum", SummaryType.Sum, columnToSummarize);
                //summary.Key = "ReqSum";
                summary.DisplayFormat = "Sum = {0}";
                summary.Appearance.TextHAlign = HAlign.Right;
            }

            columnToSummarize = e.Layout.Bands[0].Columns["Inventory"];
            if (!(e.Layout.Bands[0].Summaries.Exists(columnToSummarize.Key)))
            {
                summary = e.Layout.Bands[0].Summaries.Add("Max", SummaryType.Maximum, columnToSummarize);
                //summary.Key = "Max";
                summary.DisplayFormat = "Maximum = {0}";
                summary.Appearance.TextHAlign = HAlign.Right;
            }

            columnToSummarize = e.Layout.Bands[0].Columns["DeltaAgg"];           
            if (!(e.Layout.Bands[0].Summaries.Exists(columnToSummarize.Key)))
            {
                columnToSummarize.Formula = "[ReqSum] - [Max]";
                summary = e.Layout.Bands[0].Summaries.Add("DeltaDiff", SummaryType.Formula,columnToSummarize);
                summary.DisplayFormat = "{0}";
                summary.Appearance.TextHAlign = HAlign.Right;
            }

Hi Pete,

It's a but difficult to tell, since I can't run your code.

But it looks like you are summarizing each individual row. I thought the idea was that you didn't want to do that? You only wanted to display a summary with the difference between the two summaries without displaying the difference between each individual row value.Maybe I misunderstood. Of course, the summary needs to display somewhere, so I assume you were going to align it to the left, right, or center. Or else just put the summary under some other arbitrary column.

The way you have it set up here, you are applying a formula to the column, not to the summary. And since the column formula is referencing the summary value, it doesn't make sense, which is why I suspect you are getting the #Ref error.

You need to set the formula on the SummarySettings, not the column.

I already know how to show the difference between individual row. My question here is to display the difference between two summaries.

Based on your latest suggestion, I changed my code to following

            columnToSummarize = e.Layout.Bands[0].Columns["DeltaAgg"];           
            if (!(e.Layout.Bands[0].Summaries.Exists(columnToSummarize.Key)))
            {

                summary = e.Layout.Bands[0].Summaries.Add("DeltaDiff", SummaryType.Formula,columnToSummarize);
               columnToSummarize.Formula  = "[ReqSum] - [Max]";
                summary.Formula = "[ReqSum] - [Max]";
                summary.DisplayFormat = "{0}";
                summary.Appearance.TextHAlign = HAlign.Right;
            }

ReqSum and Max are summary keys but for some reason, I still get #Ref error. However, if I change my formula to following, it works.

summary.Formula = "Sum([Req]) - Max([Inventory])";

I wonder why grid is not able to detect the summary keys as you described above? The problem with above approach of hard coding Sum and Max is that if user decides to change the summary type to min or avg, summary in delta column will not change.

Can you help or you need a sample project?

Hi Pete,

Sorry, it looks like I was mixed up, too. To refer to a summary, you have to distinguish it from a column. You do that with parens. So it should be like so:

summary.Formula = "[ReqSum()] - [Max()]";

I tested this out and it works for me.

Thanks Mike, the solution you suggested works :)

Now, two follow-up questions

- Is there a way to provide the key code the formula without hardcoding the key. Something like
summary.Formula = Key1-Key2;  where
string Key1 = "Max()";

I tried that but I got the obvious error incorrect datatype for this operation. (minus)

Second question - is there way to filter out Aggregate value. When I click on filter (funnel thing) in a column, all the options I get are for individual row value in that column. I want to apply the filter to aggregate value, in my case, I want to give the user the option to filter values where Delta < 0, Delta < 1 ..custom filter.

Not sure if should start a new thread for these follow-ups questions or continue here. Let me know.

Thanks for your help,

pete said:

- Is there a way to provide the key code the formula without hardcoding the key. Something like
summary.Formula = Key1-Key2;  where
string Key1 = "Max()";

I tried that but I got the obvious error incorrect datatype for this operation. (minus)

I'm afraid I do not understand your question. You could get the Key from the SummarySettings on the band and build an appropriate formula string, of course.

pete said:

Second question - is there way to filter out Aggregate value. When I click on filter (funnel thing) in a column, all the options I get are for individual row value in that column. I want to apply the filter to aggregate value, in my case, I want to give the user the option to filter values where Delta < 0, Delta < 1 ..custom filter.

Not sure if should start a new thread for these follow-ups questions or continue here. Let me know.

You can filter the rows in the grid using AllowRowFiltering. I'm not actually sure if the summaries are calculated from the filtered data or all of the data. You'd have to try it out and see.

If not, then you could either create your own custom functions for CalcManager, or... use more than one formula. For example, you could add a hidden, unbound column to the grid and apply a formula to this column using an IF function. The result of this formula could be the value of the visible column or 0 depending on the value. Then you sum this hidden column.