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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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!