UltraWebGrid in OutlookGroupBy Mode: How to display custom aggregates (SUM, AVG, MIN, MAX) in GroupBy row

Last year I described a way to display accurate row count when grid in OutlookGroupBy mode has multiple sub-groups.

That solution requires custom counting function. Fortunately to display aggregates such as SUM, AVG, MIN and MAX UltraWebGrid has a built-in functionality in form of GroupByRowDescriptionMaskDefault property of DisplayLayout. It specifies the string to be displayed in every GroupBy row and can include following substitutes:

  • [caption] – will display GroupBy column header text
  • [value] – will display common to the group cell value
  • [count] – will display row count (does not work correctly with sub-groups)
  • [avg], [sum], [min], [max] – will display the aggregate for the column it’s grouped by
  • [avg:ColumnKey], [sum:ColumnKey], [min:ColumnKey], [max:ColumnKey] – will display the aggregate for any other column, where ColumnKey is the key of the column

With this in mind in just a few simple steps we can make UltraWebGrid to display something like this:

First Lets create an Enum to hold different types of aggregate functions (it’s always a good idea to use strongly typed elements instead of string literals like “Sum”):

Public Enum CustomAggregate As Integer
    Sum = 1
    Avg = 2
    Min = 3
    Max = 4
End Enum

Second Add following code to grids InitializeLayout event:

Protected Sub xuwgMyGrid_InitializeLayout(ByVal sender As Object, ByVal e As Infragistics.WebUI.UltraWebGrid.LayoutEventArgs) Handles xuwgMyGrid.InitializeLayout

        '{ ***** Begin Implementing Custom Aggregate 

        Dim eAggrType As CustomAggregate
        Dim sAgrrColumnKey As String

        'setting Aggregate function type and key of the column to perform aggregate on
        eAggrType = CustomAggregate.Min
        sAgrrColumnKey = "FA912"

        If eAggrType <> 0 Then 'if custom aggregare value is set - use it in Group By Row Mask
            e.Layout.GroupByRowDescriptionMaskDefault = "[caption]: [value]. <span style='color:green'>" & eAggrType.ToString & " of " & xuwgSearchResult.Columns.FromKey(sAgrrColumnKey).Header.Caption & " ([" & eAggrType.ToString.ToLower & ":" & sAgrrColumnKey & "])</span>"
        Else 'otherwise set it to blank string and event InitializeGroupByRow will take care of custom count
            e.Layout.GroupByRowDescriptionMaskDefault = ""
        End If

        ' ***** End Implementing Custom Aggregate

End Sub

It sets the type of aggregate function and the key of the column to perform aggregate on (Lines 9-10) (in your case those of course would have the different values, most likely retrieved programmatically from somewhere and in some cases even not set at all). Then it checks whether the aggregate function is set and if so, assigns value to GroupByRowDescriptionMaskDefault property, otherwise sets it to blank string).

Third add following code to grid’s InitializeGroupByRow event:

Protected Sub xuwgSearchResult_InitializeGroupByRow(ByVal sender As Object, ByVal e As Infragistics.WebUI.UltraWebGrid.RowEventArgs) Handles xuwgSearchResult.InitializeGroupByRow
        Dim grRow As GroupByRow = e.Row

        If grRow.Band.Grid.DisplayLayout.GroupByRowDescriptionMaskDefault = "" Then 'If no Custom Aggregate Mask is specified - use count
            grRow.Text = grRow.Column.Header.Caption & ": " & grRow.Value & ". <span style='color:green'>Count (" & GroupRowCount(grRow) & ")</span>"
        End If
End Sub

and following custom counting function:

Function GroupRowCount(ByVal i_oRow As UltraGridRow) As Integer
        Dim iRowCount As Integer = 0

        If i_oRow.Rows(0).HasChildRows Then
            For Each oRow As UltraGridRow In i_oRow.Rows
                iRowCount += GroupRowCount(oRow)
            iRowCount = i_oRow.Rows.Count
        End If

        Return iRowCount
End Function

The code in the event check whether GroupByRowDescriptionMaskDefault property is set and if so – doesn’t do anything, otherwise (default action) calls GroupRowCount function to display accurate count of rows. This is a slightly updated version of the original version. The result of the default count action:

Leave a Reply

Your email address will not be published. Required fields are marked *