A GridView Control with Subtotals


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();
DataRow row;
row = Table.NewRow();
row["Name"] = “Boss”;
row["Salary"] = 2000000;
Table.Rows.Add(row);

row = Table.NewRow();
row["Name"] = “Clerck”;
row["Salary"] = 200;
Table.Rows.Add(row);
}

private DataTable MakeDataTable()
{
DataTable inner = new DataTable();
DataColumn column = new DataColumn(“Name”, typeof(string));
inner.Columns.Add(column);

column = new DataColumn(“Salary”, typeof(int));
inner.Columns.Add(column);
return inner;
}

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;
GridView1.DataBind();

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:

gridview with subtotals

You may also put a “Sum of Salary” statement just next to the Total value:

row.Cells[0].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.


About these ads

3 thoughts on “A GridView Control with Subtotals

  1. This is very good, thanks!

    Here is how to compute using Dataset as datasource

    DataView dv = (DataView)this.grvUsers.DataSource;
    string total = dv.Table.Compute(“Sum(Mass)”, “”).ToString();

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s