Edit this page

Summary Rows

The purpose of this article is to demonstrate a sample approach how you can implement a scenario in which RadVirtualGrid displays summary rows.

Figure 1: Summary Rows

virtualgrid-how-to-summary-rows 001

We will use a sample DataTable to populate the RadVirtualGrid with data by using the CellValueNeeded event. In a List of integer values, we will store the row indices at which the summary rows will be placed. Thus, in the CellValueNeeded event for the specific row index that is related to a summary row, we will calculate the summary value. The CellFormatting event will be used to apply different style for the summary cells. Finally, subscribe to the CellValuePushed event where you should modify the DataTable value for the associated cell. In order to update the summary values, call the RadVirtualGrid.TableElement.SynchronizeRows method.

You can find below a complete code snippet which result is illustrated on the above figure:


private DataTable table = new DataTable();
private List<int> summaryRowIndices = new List<int>();

public VirtualGridSummaryRows()
{
    InitializeComponent();

    this.table = this.GenerateData();

    this.summaryRowIndices.AddRange(new int[] { 5, 11, 14, 20, 25, 35 });

    this.radVirtualGrid1.RowCount = table.Rows.Count + summaryRowIndices.Count;
    this.radVirtualGrid1.ColumnCount = table.Columns.Count;
    this.radVirtualGrid1.CellValueNeeded += VirtualGridElement_CellValueNeeded;
    this.radVirtualGrid1.CellValuePushed += VirtualGridElement_CellValuePushed;
    this.radVirtualGrid1.CellFormatting += radVirtualGrid1_CellFormatting;
}

private DataTable GenerateData()
{
    DataTable table = new DataTable();

    for (int i = 0; i < 4; i++)
    {
        table.Columns.Add("Column" + i, typeof(decimal));
    }

    for (int i = 0; i < 30; i++)
    {
        table.Rows.Add(i, i, i, i);
    }

    return table;
}

private void VirtualGridElement_CellValueNeeded(object sender, VirtualGridCellValueNeededEventArgs e)
{
    if (e.RowIndex < 0 && e.ColumnIndex >= 0)
    {
        if (e.RowIndex == RadVirtualGrid.HeaderRowIndex)
        {
            e.Value = table.Columns[e.ColumnIndex].ColumnName;
        }
    }
    else if (e.RowIndex >= 0 && e.ColumnIndex >= 0)
    {
        if (this.IsSummaryRow(e.RowIndex))
        {
            e.Value = this.GetSummaryValue(e.RowIndex, e.ColumnIndex);
        }
        else
        {
            e.Value = this.table.Rows[this.GetDataSourceRowIndex(e.RowIndex)][e.ColumnIndex];
        }
    }
}

private void radVirtualGrid1_CellFormatting(object sender, VirtualGridCellElementEventArgs e)
{
    if (this.IsSummaryRow(e.CellElement.RowIndex))
    {
        e.CellElement.BackColor = Color.LightBlue;
        e.CellElement.DrawFill = true;
        e.CellElement.GradientStyle = GradientStyles.Solid;
    }
    else
    {
        e.CellElement.ResetValue(LightVisualElement.BackColorProperty, ValueResetFlags.Local);
        e.CellElement.ResetValue(LightVisualElement.DrawFillProperty, ValueResetFlags.Local);
        e.CellElement.ResetValue(LightVisualElement.GradientStyleProperty, ValueResetFlags.Local);
    }
}

private void VirtualGridElement_CellValuePushed(object sender, VirtualGridCellValuePushedEventArgs e)
{
    this.table.Rows[this.GetDataSourceRowIndex(e.RowIndex)][e.ColumnIndex] = e.Value;
    this.radVirtualGrid1.TableElement.SynchronizeRows();
}

private bool IsSummaryRow(int virtualGridRowIndex)
{
    return this.summaryRowIndices.BinarySearch(virtualGridRowIndex) >= 0;
}

private int GetDataSourceRowIndex(int virtualGridRowIndex)
{
    int result = virtualGridRowIndex;

    for (int i = 0; i < this.summaryRowIndices.Count; i++)
    {
        if (virtualGridRowIndex >= this.summaryRowIndices[i])
        {
            result--;
        }
        else
        {
            break;
        }
    }

    return result;
}

private object GetSummaryValue(int virtualGridRowIndex, int virtualGridcolumnIndex)
{
    int index = this.summaryRowIndices.BinarySearch(virtualGridRowIndex);

    if (index < 0)
    {
        return null;
    }

    int startIndex = 0;
    int endIndex = 0;

    if (index == 0)
    {
        startIndex = 0;
        endIndex = this.summaryRowIndices[index] - 1;
    }
    else
    {
        startIndex = this.summaryRowIndices[index - 1] + 1;
        endIndex = this.summaryRowIndices[index] - 1;
    }

    startIndex = this.GetDataSourceRowIndex(startIndex);
    endIndex = this.GetDataSourceRowIndex(endIndex);

    return this.ExecuteSummaryFunction(startIndex, endIndex, virtualGridcolumnIndex);
}

private object ExecuteSummaryFunction(int startRowIndex, int endRowIndex, int columnIndex)
{
    switch (columnIndex)
    {
        case 0:
            decimal sum = 0;

            for (int i = startRowIndex; i <= endRowIndex; i++)
            {
                sum += (decimal)this.table.Rows[i][columnIndex];
            }

            return "Sum = " + sum;
        case 1:
            decimal avgSum = 0;

            for (int i = startRowIndex; i <= endRowIndex; i++)
            {
                avgSum += (decimal)this.table.Rows[i][columnIndex];
            }

            return "Avg = " + (avgSum / (endRowIndex - startRowIndex + 1));
        case 2:
            decimal min = int.MaxValue;

            for (int i = startRowIndex; i <= endRowIndex; i++)
            {
                min = Math.Min(min, (decimal)this.table.Rows[i][columnIndex]);
            }

            return "Min = " + min;
        case 3:
            decimal max = int.MinValue;

            for (int i = startRowIndex; i <= endRowIndex; i++)
            {
                max = Math.Max(max, (decimal)this.table.Rows[i][columnIndex]);
            }

            return "Max = " + max;
        default:
            return null;
    }
}

Private table As New DataTable()
Private summaryRowIndices As New List(Of Integer)()
Public Sub New()
    InitializeComponent()
    Me.table = Me.GenerateData()
    Me.summaryRowIndices.AddRange(New Integer() {5, 11, 14, 20, 25, 35})
    Me.RadVirtualGrid1.RowCount = table.Rows.Count + summaryRowIndices.Count
    Me.RadVirtualGrid1.ColumnCount = table.Columns.Count
    AddHandler Me.RadVirtualGrid1.CellValueNeeded, AddressOf VirtualGridElement_CellValueNeeded
    AddHandler Me.RadVirtualGrid1.CellValuePushed, AddressOf VirtualGridElement_CellValuePushed
    AddHandler Me.RadVirtualGrid1.CellFormatting, AddressOf radVirtualGrid1_CellFormatting
End Sub
Private Function GenerateData() As DataTable
    Dim table As New DataTable()
    For i As Integer = 0 To 3
        table.Columns.Add("Column" & i, GetType(Decimal))
    Next
    For i As Integer = 0 To 29
        table.Rows.Add(i, i, i, i)
    Next
    Return table
End Function
Private Sub VirtualGridElement_CellValueNeeded(sender As Object, e As VirtualGridCellValueNeededEventArgs)
    If e.RowIndex < 0 AndAlso e.ColumnIndex >= 0 Then
        If e.RowIndex = RadVirtualGrid.HeaderRowIndex Then
            e.Value = table.Columns(e.ColumnIndex).ColumnName
        End If
    ElseIf e.RowIndex >= 0 AndAlso e.ColumnIndex >= 0 Then
        If Me.IsSummaryRow(e.RowIndex) Then
            e.Value = Me.GetSummaryValue(e.RowIndex, e.ColumnIndex)
        Else
            e.Value = Me.table.Rows(Me.GetDataSourceRowIndex(e.RowIndex))(e.ColumnIndex)
        End If
    End If
End Sub
Private Sub radVirtualGrid1_CellFormatting(sender As Object, e As VirtualGridCellElementEventArgs)
    If Me.IsSummaryRow(e.CellElement.RowIndex) Then
        e.CellElement.BackColor = Color.LightBlue
        e.CellElement.DrawFill = True
        e.CellElement.GradientStyle = GradientStyles.Solid
    Else
        e.CellElement.ResetValue(LightVisualElement.BackColorProperty, ValueResetFlags.Local)
        e.CellElement.ResetValue(LightVisualElement.DrawFillProperty, ValueResetFlags.Local)
        e.CellElement.ResetValue(LightVisualElement.GradientStyleProperty, ValueResetFlags.Local)
    End If
End Sub
Private Sub VirtualGridElement_CellValuePushed(sender As Object, e As VirtualGridCellValuePushedEventArgs)
    Me.table.Rows(Me.GetDataSourceRowIndex(e.RowIndex))(e.ColumnIndex) = e.Value
    Me.RadVirtualGrid1.TableElement.SynchronizeRows()
End Sub
Private Function IsSummaryRow(virtualGridRowIndex As Integer) As Boolean
    Return Me.summaryRowIndices.BinarySearch(virtualGridRowIndex) >= 0
End Function
Private Function GetDataSourceRowIndex(virtualGridRowIndex As Integer) As Integer
    Dim result As Integer = virtualGridRowIndex
    For i As Integer = 0 To Me.summaryRowIndices.Count - 1
        If virtualGridRowIndex >= Me.summaryRowIndices(i) Then
            result -= 1
        Else
            Exit For
        End If
    Next
    Return result
End Function
Private Function GetSummaryValue(virtualGridRowIndex As Integer, virtualGridcolumnIndex As Integer) As Object
    Dim index As Integer = Me.summaryRowIndices.BinarySearch(virtualGridRowIndex)
    If index < 0 Then
        Return Nothing
    End If
    Dim startIndex As Integer = 0
    Dim endIndex As Integer = 0
    If index = 0 Then
        startIndex = 0
        endIndex = Me.summaryRowIndices(index) - 1
    Else
        startIndex = Me.summaryRowIndices(index - 1) + 1
        endIndex = Me.summaryRowIndices(index) - 1
    End If
    startIndex = Me.GetDataSourceRowIndex(startIndex)
    endIndex = Me.GetDataSourceRowIndex(endIndex)
    Return Me.ExecuteSummaryFunction(startIndex, endIndex, virtualGridcolumnIndex)
End Function
Private Function ExecuteSummaryFunction(startRowIndex As Integer, endRowIndex As Integer, columnIndex As Integer) As Object
    Select Case columnIndex
        Case 0
            Dim sum As Decimal = 0
            For i As Integer = startRowIndex To endRowIndex
                sum += CDec(Me.table.Rows(i)(columnIndex))
            Next
            Return "Sum = " & sum
        Case 1
            Dim avgSum As Decimal = 0
            For i As Integer = startRowIndex To endRowIndex
                avgSum += CDec(Me.table.Rows(i)(columnIndex))
            Next
            Return "Avg = " & (avgSum / (endRowIndex - startRowIndex + 1))
        Case 2
            Dim min As Decimal = Integer.MaxValue
            For i As Integer = startRowIndex To endRowIndex
                min = Math.Min(min, CDec(Me.table.Rows(i)(columnIndex)))
            Next
            Return "Min = " & min
        Case 3
            Dim max As Decimal = Integer.MinValue
            For i As Integer = startRowIndex To endRowIndex
                max = Math.Max(max, CDec(Me.table.Rows(i)(columnIndex)))
            Next
            Return "Max = " & max
        Case Else
            Return Nothing
    End Select
End Function

See Also