Datagrid: Calculate Avg or Sum for column in Footer

Question!

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?



Answers

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
        Next
    e.Item.Cells(i).Text = Math.Round(runningTotal / DataGrid.Items.Count, 0)
    Next
End Select

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



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).

By : TheSmurf


We had the same problem with combining WPF dependencies .... ILMerge doesn't appear to deal with these. Costura.Fody worked perfectly for us however and took about 5 minutes to get going... a very good experience.

Just install with Nuget (selecting the correct default project in the Package Manager Console). It introduces itself into the target project and the default settings worked immediately for us.

It merges the all DLLs marked "Copy Local" = true and produces a merged .EXE (alongside the standard output), which is nicely compressed in size (much less than the total output size).

The license is MIT as so you can modify/distribute as required.

https://github.com/Fody/Costura/

By : rexall


This video can help you solving your question :)
By: admin