My bound grid columns are: CITY, SALESPERSON, TOTALSALES
I added a group summary by CITY so I now have SUM(TOTALSALES) for each city.
I am trying to calculate each salesperson's percentage of sales in their own city, so I want to divide their totalsales by the sum(totalsales) for that city group. is that possible in a formula?
I added a calculated unbound column, I want its formula to be [TOTALSALES] / SUM(TOTALSALES) PER CITY. not for the whole dataset.
I saw in some samples the use of double slashes // and (*) but i couldn't find any documentation that explained the different special characters that can be used in formulas or how to reference different groups, bands, or summary rows.
Any help is appreciated.
When you added your Summary to the grid, did you specify a Key for it? If so, you can simply use that Key value in the formula for the unbound column and it should return the total for that particular island of rows.
So the formula would be:
([TotalSales] / [key of summary]) * 100
Mike,
That was what I thought initially but I got a #REF! in the rows
I was wondering if this is because I am grouping, so I removed the line that adds a sorted column for grouping and I still get the #REF!
Here's the simplified code from the grid's InitializeLayout:
For Each c As Infragistics.Win.UltraWinGrid.UltraGridColumn In e.Layout.Bands(0).Columns c.CellActivation = Activation.NoEdit If (Microsoft.VisualBasic.Right(c.Key, 2) = "ID") Then c.Hidden = True 'Hide ID Columns ElseIf (c.Key = "TOTALSALES") Then c.CellActivation = Activation.AllowEdit e.Layout.Bands(0).Summaries.Add(c.Key & "Sum", SummaryType.Sum, c) End IfNext c
e.Layout.Bands(0).SummaryFooterCaption = "TOTALS"'e.Layout.Bands(0).SortedColumns.Add("CITY", False, True)
Dim cccol As Infragistics.Win.UltraWinGrid.UltraGridColumncccol = e.Layout.Bands(0).Columns.Add("CalcCol")cccol.Formula = "[TOTALSALES] / [TOTALSALESSum]" 'Percentage of TOTALSALES
e.Layout.Bands(0).Summaries.Add("CalcColSum", SummaryType.Sum, cccol)
'
In the FOR EACH statement I handle other formatting issues for other columns that i didn't list here.
this is why I am assigning the summary key as (c.Key & "Sum") for each column I add a summary to.
But I thought that would be ok, is it?
Hi,
Sorry, I forgot that summary references have a slightly different syntax. Since you could have a column and a Summary in the same band with the same key, the CalcManager needs to be able to distinguish between the two. So you can't just use the name of the summary, you have to put parens after it to indicate it's a summary and not a column.
cccol.Formula = "[TOTALSALES] / [TOTALSALESSum()]"
Thank you the parenthesis fixed the problem.
Any idea where I can find documentation on these special characters used in formulas?
I don't think this is currently documented in detail. This was an oversight in the docs that will be corrected in NetAdvantage 10.3.