I have a datagrid getting bound to a dataset, and I want to display the average result in the footer for a column populated with integers.

The way I figure, there's 2 ways I can think of:

1."Use the Source, Luke"
In the code where I'm calling DataGrid.DataBind(), use the DataTable.Compute() method (or in my case DataSet.DataTable(0).Compute()). For example:

Dim strAverage = DataTable.Compute("Avg(ColumnName)", "")

But once I have this, how can I insert it into the footer?

2."Bound for Glory"
Using the DataGrid.ItemDataBound event, and calculating a running total from every ListItemType.Item and ListItemType.AlternatingItem, finally displaying in ListItemType.Footer. For example:

Select Case e.Item.ItemType
    Case ListItemType.Item, ListItemType.AlternatingItem
        runningTotal += CInt(e.Item.Cells(2).Text)
    Case ListItemType.Footer
        e.Item.Cells(2).Text = runningTotal/DataGrid.Items.Count
End Select

This just feels wrong, plus I would have to make sure the runningTotal is reset on every DataBind.

Is there a better way?


I don't know if either are necessarily better, but two alternate ways would be:

  1. Manually run through the table once you hit the footer and calculate from the on-screen text
  2. Manually retrieve the data and do the calculation separately from the bind

Of course, #2 sort of offsets the advantages of data binding (assuming that's what you're doing).


Thanks DannySmurf, your first answer made me see sense. (Why do we always look for that magic solution?).

For reference, here's what I ended up doing: (Warning: VB below, may not contain enough semicolons)

Case ListItemType.Footer
    e.Item.Cells(0).Text = "Average"
    For i As Integer = 3 To 8
        Dim runningTotal As Integer = 0
        For Each row As DataGridItem In DataGrid.Items
            If IsNumeric(row.Cells(i).Text) Then
                runningTotal += CInt(row.Cells(i).Text)
            End If
    e.Item.Cells(i).Text = Math.Round(runningTotal / DataGrid.Items.Count, 0)
End Select

I needed to do it for several columns (hence 3 to 8), ultimately why I was looking for the magical solution.

Mark Glorie