When it comes to subtotals in Gridviews, I could never find a great writeup to help me out so I used to complete it using Crystal Reports which made it easy. I finally had some time to do some more research (I’m not a programmer by trade, I just whip up some reports via asp.net) so this may not be the smartest way to do this. I found great instructions in C HERE, so I converted it to VB and modified to fit my needs. If you program in C it might be easier to look at that one, but i have tried to add as many comments as I could here. There are 2 things we do here:
1.) Figure out when to add the subtotal. In my example, when it becomes a new year. I need to insert a subtotal row before it creates a new row. I do this in the Gridview’s RowDataBound. This is where you would normally add totals to the footer.
2.) Use a Function to return a row, and insert that row into the gridview before it writes the next year.
Here’s the layout of my table, as you can see, the way that I have the query(table) ordered is by year, then month. As the gridview reads the data and writes the rows, it needs to determine when the year changes so it can write the totals
Year,month,sales,cost
2011,12,10000,9000
2011,11,11000,5000
2011,10,4000,3000
2010,12,3000,2000
2010,11,3000,1500
2009,12,6000,5000
2009,11,2000,1000
Here’s how to accomplish this:
First I start off by figuring out what year it is: I create 2 global variables (top of the page) like so:
Dim strLastYear, strCurYear As String
Then in my page Load I set them both to the current year. This will make sure that it does not add a row at the beginning of the gridview.
strCurYear = Year(Date.Now).ToString strLastYear = Year(Date.Now).ToString
Then I needed some global variables to hold my totals and subtotals.
Dim decTotalCost, decTotalSales, decSubCost, decSubSales As Decimal
Now in The Gridview’s rowdatabound, as it’s reading data from the database, I need to first make sure it’s a data row, then I grab the year from the current row that it is reading from my table, and find out what year it is.
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound If e.Row.RowType = DataControlRowType.DataRow Then 'determine if we need to subtotal, so if the year hasn't changed, then we just need to add the subtotals, not create the subtotal row strCurYear = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Year")) If strCurYear = strLastYear Then decsubSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Sales")) decsubCost += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"))
Now we need to account for if it’s a new year. If it’s a new year we want to do a few things:
- 1. Create a temporary table and set it equal to the gridview
- 2. Get the index (row) that we are on.
- 3. Create the new row using a function called CreateGridviewRow which will match the color, and use the cost, num of columns (see below)
- 4. Add the new row to the current Gridview at the correct location
- 5. Clear the subtotals so we can start summing up next years totals, then start adding the new years totals
Else
'if it's a new year then we create a temporary table and set it to the gridview, after that we grab the row, then we use the function to create a new row
Dim tbltemp As Table = Me.GridView1.Controls(0)
Dim intindex = tbltemp.Rows.GetRowIndex(e.Row)
Dim gvrSubTotal As GridViewRow = CreateGridviewRow(4, intindex, "#F7F6F3", 2, "Total for " + strLastYear, 20, decsubSales.ToString("c"), decsubCost.ToString("c"))
'add the row to the gridview
tbltemp.Controls.AddAt(intindex, gvrSubTotal)
'zero out the subtotals from last year since we are done
decsubSales = 0
decsubCost = 0
'since the year has changed and we haven't yet added the subtotals we need to add them after the previous years totals have been zeroed out (above)
decsubSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Sa-Sales"))
decsubCost += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"))
End If
Now add the running Totals that will be used in the footer
'Add TotalSales to the running Total Variables decSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Sa-Sales")) decCost += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost")) strLastYear = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "sqlyear"))
We’re not out of the water yet, we not only have to write the subtotal above the footer, and then put our running totals in the footer as well.
So when we get to the footer, the first thing we want to do is write the subtotals for the last year. So we basically use the same code as above where we use the function to create the last subtotal row before the gridview writes the footer
ElseIf e.Row.RowType = DataControlRowType.Footer Then
'For the Footer Display the Running Totals
Dim tbltemp As Table = Me.GridView1.Controls(0)
Dim intindex = tbltemp.Rows.GetRowIndex(e.Row)
'this is for the last subtotal row
Dim gvrSubTotal As GridViewRow = CreateGridviewRow(4, intindex, "#F7F6F3", 2, "Total for " + strLastYear, 20, decsubSales.ToString("c"), decsubCost.ToString("c"))
tbltemp.Controls.AddAt(intindex, gvrSubTotal)
'zero out the subtotals
decsubSales = 0
decsubCost = 0
'write the running totals and a description to the footer
e.Row.Cells(2).Text = decSales.ToString("c")
e.Row.Cells(2).HorizontalAlign = HorizontalAlign.Right
e.Row.Cells(3).Text = decCost.ToString("c")
e.Row.Cells(3).HorizontalAlign = HorizontalAlign.Right
'zero out the running totals
decSales = 0
decCost = 0
'reset the curyear and last year
strCurYear = Year(Date.Now).ToString
strLastYear = Year(Date.Now).ToString
End If
End Sub
Now we can look at the Function Called CreateGridviewRow . This will take parameters like subtotal, Color, num of columns and create a table row to be returned to the gridview. We need to take into account the following:
- 1. IntNumCells = how many cells are in the gridview – I don’t use this here, but it would be used to figure out how many cells are in the gridview, so you can ignore it
- 2. Icurrentindex = what row are we on in the gridview
- 3. Strtablebackcolor = match the color of the gridvew
- 4. Incolumnspan = this is used for the text in the footer, so for instance, if our first column in the gridview is year (column 0), and our second column is month (column 1) we would set the column span to 2 so the text (strtabletext) spans across 2 rows
- 5. Strtabletext = this is what is in the footer, so for instance “Totals for 2011”
- 6. inTableHeight = what is the height of the table
- 7. strSales and StrCost = The subtotal values that we will write to the row
Private Function CreateGridviewRow(ByVal intNumCells As Integer, ByVal icurrentindex As Integer, ByVal strtablebackcolor As String, ByVal intColumnspan As Integer, ByVal strTableText As String, ByVal intTableHeight As Integer, ByVal strSales As String, ByVal strCost As String) As GridViewRow 'this function creates a subtotal row for the gridview 'create the gridview row using the parameters we passed to this function Dim gvrTemp As New GridViewRow(icurrentindex, icurrentindex, DataControlRowType.Separator, DataControlRowState.Normal) 'now we create the cells to be added to the gvrTemp above 'Create The title of the row spanning 2 columns, using the parameters above Dim celltemp As TableCell = New TableCell 'title of row celltemp.BackColor = System.Drawing.ColorTranslator.FromHtml(strtablebackcolor) celltemp.Font.Bold = True celltemp.ColumnSpan = intColumnspan celltemp.HorizontalAlign = HorizontalAlign.Left celltemp.Text = strTableText celltemp.Height = Unit.Pixel(intTableHeight) gvrTemp.Cells.Add(celltemp) 'Create the sales cell Dim celltemp2 As TableCell = New TableCell 'title of row celltemp2.BackColor = System.Drawing.ColorTranslator.FromHtml(strtablebackcolor) celltemp2.Font.Bold = True celltemp2.ColumnSpan = 1 celltemp2.HorizontalAlign = HorizontalAlign.Right celltemp2.Text = strSales.ToString celltemp2.Height = Unit.Pixel(intTableHeight) gvrTemp.Cells.Add(celltemp2) 'Create the Cost cell Dim celltemp3 As TableCell = New TableCell 'title of row celltemp3.BackColor = System.Drawing.ColorTranslator.FromHtml(strtablebackcolor) celltemp3.Font.Bold = True celltemp3.ColumnSpan = 1 celltemp3.HorizontalAlign = HorizontalAlign.Right celltemp3.Text = strCost celltemp3.Height = Unit.Pixel(intTableHeight) gvrTemp.Cells.Add(celltemp3) 'now that we have all cells, added to the gridview row return the row Return gvrTemp End Function
That should be it. You should get something that looks like this (created this in excel but it’s what the gridview should look like). Hope this helps
Vin's Mostly Admin Blog 