In order to access useful business intelligence from enterprise data for comparison or trend analysis, data in the form of a list is less useful.  Cross-tab reports provide the support to extract data for decision making.  Cross-tab reports are based on cross-tab objects; objects which are represented by a two dimensional grid that has data values based on a logic that you specify. Figure 1 shows an example of a cross-tab report from an expense report of an employee.  This report shows at a glance how much the employee spent on what date in addition to the total the employee spent for the trip.  This is more useful than just a day-to-day listing of expenses.

Figure 1

Query used in the present tutorial

While the original data comes from tabulated values, the reports are usually based on queries run against the database.  This tutorial uses a query run against the Northwind database.  The Northwind database has data related to products of various categories sold and shipped for several years by a number of different shippers.  Listing 1 shows the query run against the sample database for obtaining sales made in 1997 shown in Figure 2.  In order to obtain this data a number of tables are joined.

Listing 1

SELECT Categories. CategoryName,
 Products.ProductName, Sum (CCur ([Order
 Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductSales,
 "Qtr " & DatePart("q",[ShippedDate]) AS ShippedQuarter
FROM (Categories INNER JOIN Products ON
 Categories. CategoryID=Products.CategoryID) INNER JOIN (Orders INNER JOIN
 [Order Details] ON Orders.OrderID=[Order Details].OrderID) ON
 Products.ProductID=[Order Details].ProductID
WHERE (((Orders.ShippedDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Categories.CategoryName,
 Products.ProductName, "Qtr " &
 DatePart("q",[ShippedDate]);

Figure 2

<img border=0 width=528 height=470 src="/ArticleFiles/1041/image002.jpg">

While this data in the above format is correct and valid, it is not very useful if you want to get a summarized view, such as the one we saw in the expense report.  In this tutorial the data will be used to create a cross-tab report that will answer questions regarding quarterly sales, total sales of 'Boston Crab Meat', etc.