Need to add "rollup" of entire dataset to bottom of DataGrid that is paged

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 开发者_C百科

...                 ...

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?

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

SO from what I understand here you want to get a subtotal for each grouping based on product name.

If you know what the column names are, you can use LINQ to group the results to get the aggregate, then if you wanted them inserted as the last record, simply insert them to the DS after you do the query.

How about write another SQL Query using a group by, then you can get the totals for each subgroup your tring to isolate pretty easily. Then parse through each row of the resultset of the query and build a string. With the string you build, it would be easily inserted into whatever column in the footer you want.

I was able to fix the problem. I realized since the list of products can be dynamic, i don't have BoundColumns in the HTML/ASPX code. I had to create them programmatically. When creating them, I check the Caption for the column. If the caption existed in my dictonary as a key, i returned the value (which was the sum) and set it as the column FooterText value.

Thanks for all the suggestions though!