Recently, I needed a Gridview control which could support subtotals. I probed many ASP.NET forums and surprisingly I found that many guys had said it was impossible or at least hard to implement! Therefore, I gave it a try and finally l managed to show subtotals in a gridview control.
In writing that Gridview control I assumed that it was bound to a DataTable so I used DataTable.Compute method (described in previous posts) in order to calculate summary of numeric fields. However you may bind your own GridView control to a SqlDataSource or even ObjectDataSource, but the approach is the same and I am sure you can handle it :-)
Anyway, first of all we need a DataTable object. You may create one and fill it with real data (coming from a DBMS) or fill it by code :
protected void Page_Load(object sender, EventArgs e)
DataTable Table = MakeDataTable();
row = Table.NewRow();
row["Name"] = “Boss”;
row["Salary"] = 2000000;
row = Table.NewRow();
row["Name"] = “Clerck”;
row["Salary"] = 200;
private DataTable MakeDataTable()
DataTable inner = new DataTable();
DataColumn column = new DataColumn(“Name”, typeof(string));
column = new DataColumn(“Salary”, typeof(int));
Afterwards, we need a GridView whose ShowFooter property is set to True. If you are developing a custom control you may set this property value inside the constructor of it’s class. We need the footer line to show the subtotals. Next, we just bind the GridView control to our DataTable object.
GridView1.DataSource = Table;
Now the magic is started. Write an event handler for DataBound event and inside it write the following code:
1: const int SalaryIndex=1;
2: GridViewRow row = (sender as GridView).FooterRow;
3: DataTable Table = (sender as GridView).DataSource as DataTable;
4: row.Cells[SalaryIndex].Text = ((long)Table.Compute(“Sum(Salary)”, “”)).ToString(“###,###”);
The first line defines a constant pointing to “Salary” column. You may find this column by it’s name too.
Line 2 gets the Footer row of our gridview. Please note that if ShowFooter property is False, the FooterRow will be null.
Line 3 gets the DataTable to which our GridView is bound.
Line 4 computes the total of Salary field, converts the resulting number to string and assigns it to the .Text property of 2nd cell in Footer row.
That’s it. Now our GridView shows the total of Salary, as shown in the following photo:
You may also put a “Sum of Salary” statement just next to the Total value:
row.Cells.Text = “Sum of Salary:”;
Note : You may define a Template field and put whatever kind of controls inside it, then assign the Total value to controls, instead of the cell’s .Text property.
Note: Some guys like scanning all rows and summing the values of GridView cells directly. I do not approve this method because the numbers might be formatted (i.e. $200,100) , values might be combined with texts or even the column text might be empty.
You may find a complete sample code here.