Group header and Summary Row

[Marco]

Hello,

 

I have a gridview which display workitemdata. The columns are "The client", "The service contract", "The Employee", "The Date", "Hours", "Description", "price hour" and "Value (price hour * hours). The client, service contract and employee are ID value not string.

 

I have made a three level grouping on Client, service contract and Employee.

 

I have added a summary row for displaying the total of Value by group and an Handler on GroupSummaryEvaluate for displaying an human readable string instead of ID.

 

But I'm stuck on these problems:

1. I want only to display the subtotal (the summaryRow) on the client group level (last level). The showParentGroupSummaries properties work for each level (true) or the first level (false).
2. The GroupSummaryEvaluate handler change the display string of my group header AND my summaryRow.
3. What are the options for formatting my summaryRow (like text alignment, font, ...)
4. The group sort my values on ID (column value) before grouping. Is there a way to sort on the readable string after the grouping. I could use a numeric enumeration based on my string instead of an ID for grouping. Just asking if there is a kick tip about this :-)

I join two picture (GroupSummaryEvaluate active/not active) and some code.

Imports System.Data.Objects
Imports Telerik.WinControls.UI
Imports Telerik.WinControls.Data

Public Class View_ValorisationTravail

    Private _datactx As IconEntities
    Public ReadOnly Property InnerDataContext As IconEntities
        Get
            Return _datactx
        End Get
    End Property

    Private _dataquery As ObjectQuery(Of Travail)
    Public Property DataQuery As ObjectQuery(Of Travail)
        Get
            Return _dataquery
        End Get
        Set(value As ObjectQuery(Of Travail))
            If _dataquery Is Nothing AndAlso value IsNot Nothing Then
                _dataquery = value
                OnDataqueryChanged()
            End If
            If Not _dataquery.Equals(value) Then
                _dataquery = value
                OnDataqueryChanged()
            End If
        End Set
    End Property

    Private Sub View_ValorisationTravail_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Me.DesignMode Then
            _datactx = New IconEntities(GetConnection())

            'Temporaire en attendant les filtres
            Me.DataQuery = GenerateDataQuery()
        End If

        IntitialiseGroupValorisationTravaux(Me.RadGridView1)

        Dim summaryItem As New GridViewSummaryItem("Valeur", "{0:N2} CHF", GridAggregateFunction.Sum)
        Dim summaryItemLabel As New GridViewSummaryItem("CHFparheure", "TOTAL", GridAggregateFunction.Sum)
        Dim summaryRowItem As New GridViewSummaryRowItem()
        summaryRowItem.Add(summaryItem)
        summaryRowItem.Add(summaryItemLabel)
        Me.RadGridView1.SummaryRowsBottom.Add(summaryRowItem)
        Me.RadGridView1.MasterTemplate.ShowParentGroupSummaries = True
    End Sub

    Private Sub OnDataqueryChanged()
        Me.RadGridView1.DataSource = DataQuery.Execute(MergeOption.OverwriteChanges).ToList
    End Sub

    ''' <summary>
    ''' Generating an ObjectQuery for test purpose
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GenerateDataQuery() As ObjectQuery(Of Travail)
        Dim query = From c In _datactx.Travail
                    Select c

        Return query
    End Function

    Private GroupClientDescriptor As GroupDescriptor
    Private GroupPrestationDescriptor As GroupDescriptor
    Private GroupCollaborateurDescriptor As GroupDescriptor

    ''' <summary>
    ''' Build the group
    ''' </summary>
    ''' <param name="aGridView"></param>
    ''' <remarks></remarks>
    Private Sub IntitialiseGroupValorisationTravaux(aGridView As RadGridView)
        aGridView.EnableGrouping = True
        aGridView.AutoExpandGroups = True
        aGridView.GroupDescriptors.Clear()

        Dim SortClientDescriptor As New SortDescriptor("Client", System.ComponentModel.ListSortDirection.Ascending)
        GroupClientDescriptor = New GroupDescriptor()
        GroupClientDescriptor.GroupNames.AddRange(New Telerik.WinControls.Data.SortDescriptor() {SortClientDescriptor})

        Dim SortPrestationDescriptor As New SortDescriptor("Prestation", System.ComponentModel.ListSortDirection.Ascending)
        GroupPrestationDescriptor = New GroupDescriptor()
        GroupPrestationDescriptor.GroupNames.AddRange(New Telerik.WinControls.Data.SortDescriptor() {SortPrestationDescriptor})

        Dim SortCollaborateurDescriptor As New SortDescriptor("Collaborateur", System.ComponentModel.ListSortDirection.Ascending)
        GroupCollaborateurDescriptor = New GroupDescriptor()
        GroupCollaborateurDescriptor.GroupNames.AddRange(New Telerik.WinControls.Data.SortDescriptor() {SortCollaborateurDescriptor})

        aGridView.GroupDescriptors.AddRange(New Telerik.WinControls.Data.GroupDescriptor() {GroupClientDescriptor, GroupPrestationDescriptor, GroupCollaborateurDescriptor})
    End Sub

    Private Sub Travaux_GroupSummaryEvaluate(ByVal sender As Object, ByVal e As Telerik.WinControls.UI.GroupSummaryEvaluationEventArgs) Handles RadGridView1.GroupSummaryEvaluate
        If e.Group.GroupDescriptor.Equals(GroupClientDescriptor) Then
            If e.Group.ItemCount > 0 AndAlso e.Group.Item(0).DataBoundItem IsNot Nothing Then
                e.FormatString = CType(e.Group.Item(0).DataBoundItem.Client, Client).Display
            End If
        ElseIf e.Group.GroupDescriptor.Equals(GroupPrestationDescriptor) Then
            If e.Group.ItemCount > 0 AndAlso e.Group.Item(0).DataBoundItem IsNot Nothing Then
                e.FormatString = CType(e.Group.Item(0).DataBoundItem.Prestation, Prestation).Display
            End If
        ElseIf e.Group.GroupDescriptor.Equals(GroupCollaborateurDescriptor) Then
            If e.Group.ItemCount > 0 AndAlso e.Group.Item(0).DataBoundItem IsNot Nothing Then
                e.FormatString = CType(e.Group.Item(0).DataBoundItem.ContratEngagement.Collaborateur, Collaborateur).DisplayName
            End If
        End If
    End Sub

End Class

[Julian Benkov]

Hello Marco,

1) I have tested this scenario and I did not find any issues to display summary items on every group subtotals. Please view the attached screenshot.

2) To change the formatting in situation you can use the Context property of the event args:

Private Sub gridView_GroupSummaryEvaluate(sender As Object, e As GroupSummaryEvaluationEventArgs)
    If TypeOf e.Context Is GridViewGroupRowInfo Then
        e.FormatString = "{0}"
    ElseIf TypeOf e.Context Is GridViewSummaryRowInfo Then
        e.FormatString = "Total:{0}"
    End If
End Sub

3) You can use the ViewCellFormatting event to implement this behavior:

Private Sub gridView_ViewCellFormatting(sender As Object, e As CellFormattingEventArgs)
    If TypeOf e.CellElement Is GridSummaryCellElement Then
        e.CellElement.Font = New System.Drawing.Font(e.CellElement.Font, System.Drawing.FontStyle.Bold Or System.Drawing.FontStyle.Italic)
    End If
End Sub

4) You can use the custom group sorting for this case. Here is a example:

Imports System.Collections.Generic
Imports System.Data
Imports System.Security.Claims
Imports System.Windows.Forms
Imports Telerik.WinControls.Data
Imports Telerik.WinControls.UI

Namespace Lab.Grid
    Public Partial Class GridSortedGroups
        Inherits MainForm
        Private gridView As New RadGridView()

        Public Sub New()
            InitializeComponent()

            gridView.Dock = DockStyle.Fill
            gridView.Parent = Me
            gridView.BringToFront()
        End Sub

        Protected Overrides Sub OnLoad(e As EventArgs)
            MyBase.OnLoad(e)

            Dim table As New DataTable()
            table.Columns.Add("Id", GetType(Integer))
            table.Columns.Add("Name")
            table.Columns.Add("Sum", GetType(Double))

            table.Rows.Add(1, "Ivan", 123.56)
            table.Rows.Add(2, "George", 100.0)
            table.Rows.Add(3, "Peter", 500.0)
            table.Rows.Add(4, "George", 600.0)
            table.Rows.Add(5, "Enzo", 78.0)
            table.Rows.Add(6, "Ivan", 372.9)
            table.Rows.Add(7, "Enzo", 100.0)
            table.Rows.Add(8, "George", 300.0)
            table.Rows.Add(9, "Enzo", 200.0)
            table.Rows.Add(10, "Enzo", 300.0)

            gridView.DataSource = table
            gridView.MasterTemplate.GroupComparer = New GroupAggregateComparer()


        End Sub

        Public Class GroupAggregateComparer
            Implements IComparer(Of Group(Of GridViewRowInfo))
            Public Function Compare(x As Group(Of GridViewRowInfo), y As Group(Of GridViewRowInfo)) As Integer
                Dim group As DataGroup = TryCast(x, DataGroup)
                If group Is Nothing Then
                    group = TryCast(y, DataGroup)
                End If

                Dim result As Integer = 1
                If group IsNot Nothing AndAlso group.GroupDescriptor IsNot Nothing AndAlso group.GroupDescriptor.GroupNames(0).Direction = System.ComponentModel.ListSortDirection.Descending Then
                    result = -1
                End If

                Dim xKey As Object = x.Key
                ' x.Evaluate("Sum(Sum)");
                Dim yKey As Object = y.Key
                ' y.Evaluate("Sum(Sum)");
                If x.ItemCount > 0 Then
                    xKey = x(0).Cells(0).Value
                End If

                If y.ItemCount > 0 Then
                    yKey = y(0).Cells(0).Value
                End If

                If xKey Is yKey Then
                    Return DirectCast(x.Key, IComparable).CompareTo(y.Key) * result
                End If

                If xKey Is Nothing OrElse xKey Is DBNull.Value Then
                    Return -1 * result
                End If

                If yKey Is Nothing OrElse yKey Is DBNull.Value Then
                    Return 1 * result
                End If

                If xKey.[GetType]() IsNot yKey.[GetType]() Then
                    Return result
                End If

                Return DirectCast(xKey, IComparable).CompareTo(yKey) * result
            End Function
        End Class
    End Class
End Namespace

I hope this helps. Let me know if you need further assistance.

Greetings,
Julian Benkov
the Telerik team

[Marco]

Hello Julian,

Thanks for your writing.

For the first point, I think we have missunderstood. I have three group level (Customer, TypeOfWork, Employee). I wish to have a summary row only for each Customer (and not for TypeOfWork and Employee). I have only find a way to add a summary row at the employe level or for each group level (Customer, TypeOfWork And Employe) by setting the showParentGroupSummaries properties to true.

- Customer 1
- - TypeOfWork 1
- - - Employe 1
- - - - WorkDatarow (20 h)
- - - - WorkDatarow (10 h)
- - - Employe 2
- - - - WorkDataRow (20 h)
- - TypeOfWork 2
- - - Employe 2
- - - - WorkDatarow (20 h)
- - - Employe 3
- - - - WorkDatarow (30 h)
- SummaryRow Customer 1 - 100h
- Customer 2
- - TypeOfWork 1
- - - Employe 1
- - - - WorkDatarow (20 h)
- - - Employe 2
- - - - WorkDataRow (20 h)
- - - - WorkDatarow (10 h)
- - TypeOfWork 2
- - - Employe 2
- - - - WorkDatarow (10 h)
- - - Employe 3
- - - - WorkDatarow (20 h)
- SummaryRow Customer 2 - 80h
etc...

I agree, it's juste a presentation probleme, because I could handle the GroupSummaryEvaluate event for setting the subtotal only where I want. But in this case I will have a lot of summary row with empty string. I could also put the subtotal on the groupheader with this way. I have juste found an idee while I was writting: Maybe I can hidde these row on RowFormatting event !

For the second point, it's exactly what I was looking for. Big Thanks !

For the third point, I will try to play with this event for my format.

For the last point. Your comparer is looking great :-)

[Marco]

I come back again :-)

All my initial question are closed thanks to your explanation !

For the first point, I have tried to collapse the sumaryRow in the rowformatting event. Doesn't work because this event is not firing for summaryrows. But it's possible to do this on the cellformatting event.

I have found another problem about my grid but I think I will open a support ticket about it (it's more looking like an issue than a question).

Thank you for your help Julian

[Julian Benkov]

Hello Marco,

The RowFormatting event is raised only for GridViewDataRowInfo type of rows. As you found out, you can use the ViewCellFormatting event to implement this logic. Here is an example:

Private Sub gridView_ViewCellFormatting(sender As Object, e As CellFormattingEventArgs)
    If TypeOf e.CellElement Is GridSummaryCellElement Then
        If e.CellElement.Value.ToString().Contains("1") Then
            e.Row.IsVisible = False
            Return
        End If
        e.CellElement.Font = New System.Drawing.Font(e.CellElement.Font, System.Drawing.FontStyle.Bold Or System.Drawing.FontStyle.Italic)
    End If
End Sub

Please view the result in the attached screenshot.

I hope this helps.

Greetings,
Julian Benkov
the Telerik team