Summarizing Data with
By Dave Long
ROLLUP, Part 2
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:
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.
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.
Page_Load event handler appears as follows:
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
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
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:
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.
In this article we examined the
ROLLUPsyntax in SQL.
ROLLUPcan be used to nicely summarize aggregate query results. If you are interested in learning more about
ROLLUPbe 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.