In last month’s article (November 2007)
we outlined the framework that can be used to export data from a web application to Excel. In this article we
put this framework to use to create an Excel document.
We briefly described the requirements for the Excel document to be created before when we walked
through the Excel Xml framework in the previous article. However, to get a clearer understanding
of the document we are trying to create we have to get a little more detailed.
 |
| Report Sections |
The report consists of two sections – the top part displays the order total for each country and the grand
total of all orders. The lower section of the report displays the order detail information grouped by
country and company. The detail information includes the name of the company that placed the order, the
category for the product that was ordered, the name of the ordered product, the date of the order, and
the order total (see Report Sections image below):.
The Excel report provides three levels of aggregate data. At the lowest level is the order total for
each company which is the simply sum of all line items for a particular company.
 |
Company Order Total |
The detail section groups the order detail information by company and country and the next higher
level of aggregation sums the order total of all customers for each country. The country order total
is the sum of all order totals for the various companies listed under each country. This means that
the order total calculated for each customer becomes an input for the formula that calculates the
total for each country:
 |
Country Order Total |
At the top of the page we have the summary information which lists the order totals for each country
and the grand total of all orders. We use the country totals that we calculated earlier as the source
for each respective country total by referencing the appropriate cell. For example, in the sample
below the country total for the US references the total displayed in cell E40 and the country total
for the UK references the number displayed in cell E86:
 |
Order Grand Total |
The data for the report comes from the Northwind database and we use a simple query to retrieve the
data we need to create the sample Excel document:
SELECT CU.CompanyName, CU.Country, ISNULL
(CA.CategoryName, 'No Category') AS CategoryName, P.ProductName,
O.OrderDate, OD.UnitPrice, OD.Quantity
FROM [Order Details] OD JOIN (Orders O
JOIN Customers CU
ON CU.CustomerID = O.CustomerID)
ON O.OrderID = OD.OrderID JOIN
(Products P JOIN Categories CA
ON CA.CategoryID = P.ProductID)
ON P.ProductID = OD.ProductID
WHERE CU.Country IN ('USA', 'Germany', 'UK')
AND CA.CategoryName IN ('Produce', 'Seafood', 'Beverages')
There are a two items worth pointing out. One, for the sake of brevity we limit the report to customers
that reside in the US, Germany, or the UK and to products of the type listed in the CategoryName list.
And two, we retrieve the UnitPrice and the Quantity from the [Order Details] table as separate columns
and add a calculated column to the data set to calculate the order total. This allows us to reuse the same
query if we ever decide to list both the order quantity and the unit price instead of just listing the
order total.