Sql – Need to add “rollup” of entire dataset to bottom of DataGrid that is paged

asp.net-3.5datagriddatatablerollupsql

In ASP.NET 3.5 I have a datagrid that is bound to a somwehat dynamic datatable. The datatable is created by taking three different tables returned from a dataset and combining them to create one table.

It goes like this. The first datatable returns a list of the columns that will be in the final datatable. The second datatable returns a list of people, their id number, status info and a grand total that is listed at the end of the row in the final table. The third table returns a list of values for a given type that matches the person id and the column from the first.

Example

Table1

ProdID    ProdName

1             Widgets

2             Stuff

…            …

Table 2

PersonID    PersonName

103             John Smith

105             Tim Doe

…                 …

Table 3

PersonID    ProdID    Amount

103             1             205.00

103             2             234.00

105             1             150.00

105             2             189.00

The Resulting Table becomes

PersonName    ProdName    Amount

John Smith       Widgets        205.00

John Smith       Stuff             234.00

Tim Doe           Widgets        150.00

Tim Doe           Stuff             189.00

I was able to write a Dictionary that sums each column by name, but I want to show the sum in the footer of the datagrid the end table is bound to.

So, the sum below Widgets should be 355 and the sum below Stuff should be 423. The problem is, I can't figure out how to put these values in the footer. I tried OnDataBinding for the grid, but since the footer isn't bound, then it never stops there. I don't know if i can "rollup" the created table.

Any ideas?

Best Answer

Something like this in the code-behind will work. You'll need to change the cell indexes, DataTable name, etc, but this is essentially how you add a sum in the footer.

GridView1.FooterRow.Cells[4].Text = string.Format("{0:C}", myDataTable.Compute( "sum(Net_Price)", ""));

You also have to set the DataGrid's "ShowFooter" property to "True".

Full article here: http://programming.top54u.com/post/ASP-Net-DataTable-Compute-Column-Sum-using-C-sharp.aspx

Related Topic