DataGrid with SubHeadings and Sub Totals 

This demo illustrates displaying a ROLLUP SQL query in an ASP.NET DataGrid.


Product NameUnit PriceStock Level
Beverages
Chai18.000039
Chang19.000017
Chartreuse verte18.000069
Côte de Blaye263.500017
Guaraná Fantástica4.500020
Ipoh Coffee46.000017
Lakkalikööri18.000057
Laughing Lumberjack Lager14.000052
Outback Lager15.000015
Rhönbräu Klosterbier7.7500125
Sasquatch Ale14.0000111
Steeleye Stout18.000020
Sub Totals455.7500559
Condiments
Aniseed Syrup10.000013
Chef Anton's Cajun Seasoning22.000053
Chef Anton's Gumbo Mix21.35000
Genen Shouyu15.500039
Grandma's Boysenberry Spread25.0000120
Gula Malacca19.450027
Louisiana Fiery Hot Pepper Sauce21.050076
Louisiana Hot Spiced Okra17.00004
Northwoods Cranberry Sauce40.00006
Original Frankfurter grüne Soße13.000032
Sirop d'érable28.5000113
Vegie-spread43.900024
Sub Totals276.7500507
Confections
Chocolade12.750015
Gumbär Gummibärchen31.230015
Maxilaku20.000010
NuNuCa Nuß-Nougat-Creme14.000076
Pavlova17.450029
Schoggi Schokolade43.900049
Scottish Longbreads12.50006
Sir Rodney's Marmalade81.000040
Sir Rodney's Scones10.00003
Tarte au sucre49.300017
Teatime Chocolate Biscuits9.200025
Valkoinen suklaa16.250065
Zaanse koeken9.500036
Sub Totals327.0800386
Dairy Products
Camembert Pierrot34.000019
Flotemysost21.500026
Geitost2.5000112
Gorgonzola Telino12.50000
Gudbrandsdalsost36.000026
Mascarpone Fabioli32.00009
Mozzarella di Giovanni34.800014
Queso Cabrales21.000022
Queso Manchego La Pastora38.000086
Raclette Courdavault55.000079
Sub Totals287.3000393
Grains/Cereals
Filo Mix7.000038
Gnocchi di nonna Alice38.000021
Gustaf's Knäckebröd21.0000104
Ravioli Angelo19.500036
Singaporean Hokkien Fried Mee14.000026
Tunnbröd9.000061
Wimmers gute Semmelknödel33.250022
Sub Totals141.7500308
Meat/Poultry
Alice Mutton39.00000
Mishi Kobe Niku97.000029
Pâté chinois24.0000115
Perth Pasties32.80000
Thüringer Rostbratwurst123.79000
Tourtière7.450021
Sub Totals324.0400165
Produce
Longlife Tofu10.00004
Manjimup Dried Apples53.000020
Rössle Sauerkraut45.600026
Tofu23.250035
Uncle Bob's Organic Dried Pears30.000015
Sub Totals161.8500100
Seafood
Boston Crab Meat18.4000123
Carnarvon Tigers62.500042
Escargots de Bourgogne13.250062
Gravad lax26.000011
Ikura31.000031
Inlagd Sill19.0000112
Jack's New England Clam Chowder9.650085
Konbu6.000024
Nord-Ost Matjeshering25.890010
Röd Kaviar15.0000101
Rogede sild9.50005
Spegesild12.000095
Sub Totals248.1900701
MainTotal
Total2222.71003119


Source Code

<%@ Page Language="VB" autoeventwireup="false" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Drawing" %>
<script runat="server">

    Sub Page_Load(Sender As Object, E As EventArgs) Handles MyBase.Load
    
           ' TODO: Update the ConnectionString and CommandText values for your application
           Dim ConnectionString As String = ConfigurationSettings.AppSettings("connectionStringNW")
           Dim CommandText As String = "Select CASE WHEN (Grouping(CategoryName)=1) THEN 'MainTotal' ELSE categoryname END AS CategoryName, "
           CommandText += " CASE WHEN (Grouping(ProductName)=1) THEN 'SubTotal' ELSE Productname END AS ProductName,"
           CommandText += " Sum(UnitPrice) as unitprice, "
           CommandText += " Sum(UnitsinStock) as UnitsinStock "
           CommandText += " from Products INNER JOIN Categories On Products.categoryID = Categories.CategoryID"
           CommandText += " Group By Categoryname, ProductName WITh ROLLUP "
    
           Dim myConnection As New SqlConnection(ConnectionString)
           Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
    
           Dim ds As New DataSet
           myCommand.Fill(ds)
    
           'Before DataBind - Add New Rows Here for Each Sub Heading
           Dim curCat As String
           Dim prevCat As String
           Dim i As Integer = 0
    
           Do While i <= ds.Tables(0).Rows.Count - 1
               curCat = ds.Tables(0).Rows(i).Item(0)
               If curCat <> prevCat Then
                   prevCat = curCat
                   Dim shRow As DataRow = ds.Tables(0).NewRow
                   shRow(1) = ds.Tables(0).Rows(i).Item(0)
                   'Change ItemDataBound marker to Negative Number
                   shRow(2) = -1
                   ds.Tables(0).Rows.InsertAt(shRow, i)
                   
                   i += 1
               End If
               
               i += 1
            Loop
           'End Insert SubHeadings
    
           'Change the Text in the Last Column so it can be formated in ItemDataBound
           ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1).Item(1) = "Total"
    
           DataGrid1.DataSource = ds
           DataGrid1.DataBind()
    
       End Sub
    
       Private Sub DataGrid1_ItemDataBound(sender As Object,  e As DataGridItemEventArgs)
           Select Case e.Item.ItemType
               Case ListItemType.AlternatingItem, ListItemType.Item
                   If e.Item.Cells(1).Text.Equals("-1") Then
                       'Format the SubHeading Columns
                       e.Item.Cells(0).Attributes.Add("align", "Left")
                       e.Item.Cells(0).ColumnSpan = 3
                       e.Item.Cells(0).Font.Bold = True
                       e.Item.Cells.RemoveAt(2)
                       e.Item.Cells.RemoveAt(1)
                       e.Item.BackColor = Color.FromArgb(204,204,255)
                   End If
                   If e.Item.Cells(0).Text.Equals("Total") Then
                       'Format the Main total column
                       e.Item.Cells(0).Attributes.Add("align", "Left")
                       e.Item.Cells(0).Font.Bold = True
                       e.Item.Cells(1).Font.Bold = True
                       e.Item.Cells(2).Font.Bold = True
                       e.Item.BackColor = Color.FromArgb(204,153,255)
                   End If
                   If e.Item.Cells(0).Text.Equals("SubTotal") Then
                       'Format the subtotal columns.
                       e.Item.Cells(0).Attributes.Add("align", "Left")
                       e.Item.Cells(0).Text = "Sub Totals"
                       e.Item.Cells(0).Font.Bold = True
                       e.Item.Cells(1).Font.Bold = True
                       e.Item.Cells(2).Font.Bold = True
                       e.Item.Cells(0).Font.Italic = True
                       e.Item.Cells(1).Font.Italic = True
                       e.Item.Cells(2).Font.Italic = True
                    End If
           End Select
       End Sub

</script>

<form runat="server">
    <asp:datagrid id="DataGrid1" runat="server" OnItemDataBound="DataGrid1_ItemDataBound" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False" AutoGenerateColumns="False">
        <HeaderStyle font-bold="True" forecolor="White" backcolor="#4A3C8C"></HeaderStyle>
        <ItemStyle backcolor="#DEDFDE"></ItemStyle>
        <Columns>
            <asp:BoundColumn DataField="ProductName" HeaderText="Product Name"></asp:BoundColumn>
            <asp:BoundColumn DataField="UnitPrice" HeaderText="Unit Price"></asp:BoundColumn>
            <asp:BoundColumn DataField="UnitsInStock" HeaderText="Stock Level"></asp:BoundColumn>
        </Columns>
    </asp:datagrid>
</form>


[Return to the article...]