To read the article online, visit http://www.4GuysFromRolla.com/articles/073003-1.2.aspx

Summarizing Data with ROLLUP, Part 2

By Dave Long


  • Read Part 1

  • In Part 1 we examined how to return summary information from a SQL query using the ROLLUP syntax. In this final part, we'll look at how to display the results of a SQL ROLLUP query in a nicely formatted ASP.NET DataGrid.

    Displaying the Results of a ROLLUP Query in an ASP.NET DataGrid


    In an earlier article of mine, Including Subheadings in a DataGrid, I showed how to use the Northwinds database to display the list of products with subheadings for each unique category. In this article we will modify some of the code from the previous article used to create the subheadings, and then add some new code in order to display the summary information. The below screenshot shows what our final ASP.NET DataGrid will look like:

    Screenshot of a DataGrid displaying a ROLLUP query.

    In Including Subheadings in a DataGrid we discussed using the Page_Load event and the DataGrid's ItemDataBound to create the subheadings. For creating a DataGrid for the ROLLUP query results, we will again be concentrating on these two events, adding some slight modifications. Specifically, we'll start with these two events' handlers from the Including Subheadings in a DataGrid article, and modify them as needed.

    First, the Page_Load event handler needs its SQL query modified to incorporate the ROLLUP syntax. We'll use precisely the SQL query we examined in Part 1 of this article. Our complete Page_Load event handler appears as follows:

    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 = "connection string"
        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
    

    The Page_Load event handler starts by filling a DataSet (ds) with the results from the ROLLUP query. Next, it uses the same technique discussed in Including Subheadings in a DataGrid to insert a new row into the DataSet to mark where category boundaries exist.

    While the Page_Load event handler marks what rows are summary rows, it is the responsibility of the DataGrid's ItemDataBound event handler to format these rows so that they stand out from the other rows. The first modification to the ItemDataBound event handler from the previous article is related to the second change in the Page_Load event handler, where we set the new subheading column's UnitPrice value to a negative number. This is done so we can identify where to display the subheading.

    Since we now have SubTotal and MainTotal columns in our DataGrid, we need to ensure these columns stand out from the non-total columns. We accomplish this by setting the text to bold and italic for the subtotals, and bold for the main total, as well as changing the background color for the main total column. These aesthetic changes are handled in the ItemDataBound event handler, which is shown below:

    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
    
    [View a Live Demo!]

    And that�s all there is to it. Be sure to check out the live demo to see a working example and the complete source code.

    Conclusion


    In this article we examined the ROLLUP syntax in SQL. ROLLUP can be used to nicely summarize aggregate query results. If you are interested in learning more about ROLLUP be sure to check out some of these articles:

    We also saw how to display the results of a ROLLUP query in an ASP.NET DataGrid.

    Happy Programming!

  • By Dave Long

  • Article Information
    Article Title: ASP.NET.Summarizing Data with ROLLUP, Part 2
    Article Author: Dave Long
    Published Date: July 30, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/073003-1.2.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers