Generate Custom Crosstab Reports
Filling elements in a crosstab array solves the missing value problem with SQL Server aggregate queries and executes muy pronto.
by Roger Jennings

Posted April 29, 2002

Microsoft Access' capability to generate crosstab reports automatically has been responsible for much of Access' success in the desktop database market. Microsoft now wants Access users and developers to move from "legacy" Jet to "strategic" SQL Server, but the SQL Server team has steadfastly refused to add Jet SQL's PIVOT and TRANSFORM operators to Transact-SQL. Thus, the Access Upsizing Wizard calls a foul when it encounters a crosstab query and penalizes users by ignoring these objects when moving projects from Jet to SQL Server databases.

Creating crosstab reports with T-SQL is a hot topic in the SQL Server community. A Google search on crosstab "SQL Server" returns 1,500+ hits. The Relational Application Companion (RAC, formerly "Replacement for Access Crosstabs") is a popular $39 shareware app that combines a series of T-SQL stored procedures to generate crosstab reports. Another approach is my free VB6 Crosstab app that translates most Jet SQL crosstab queries to a single T-SQL stored procedure. Neither approach is well suited to designing high-performance XML Web services that generate ad hoc crosstab reports.

Designing XML Web services that return user-defined time-series crosstab reports as XML or XHTML response documents probably will be among the first challenges presented by clients or managers to Visual Studio .NET developers. This article describes what I believe to be a new approach to generating crosstab reports with a combination of T-SQL summary queries and Visual Basic .NET procedural code. Here's a summary of the process with links to the sample documents and Visual Basic .NET code I used for initial performance tests:

1. Design an XML request document with element values that specify the source database, time-series period, starting date, number of data columns, row and value data source, and formatting parameters (see Listing 1).
2. Use an XmlTextReader object to extract the element values from the request document. Store values as class-level variables to define the parameters of the T-SQL aggregate query (see Listing 2).
3. Prepare the dynamic T-SQL statement for the aggregate query based on the class-level variables (see Listings 3 and 4, and Table 1).
4. Generate a two-dimensional array to represent the equivalent of a Jet crosstab query Recordset. The first row of the array contains fixed column headers, including a row total column. The remaining data rows contain row headers, values, and totals. An additional row holds column totals. The column and row header names for aggregate values must match exactly the values in the corresponding columns returned by the T-SQL aggregate query (see Listing 5, starting at the comment, "Generate the time-series column headers").
5. Fill the data elements of the array with zeros to represent missing values; all values are missing at this point (see Listing 5, starting at "Fill array with 0s").
6. Execute the query with SqlClient.SqlConnection, SqlCommand, and SqlReader objects (see Listing 5, "Connect and create a SqlDataReader").
7. Invoke the SqlReader.GetString(n) method to return row and column header names, and traverse the array to find the intersection having matching names. n represents the column's zero-based ordinal value (see Listing 5, "Iterate the rowset and plug values into the proper cell").
8. Apply the SqlReader.GetDecimal(n) method to replace the 0 value at the array intersection with the aggregate value (see Listing 5, "Found the cell; add the value without decimals").

   Figure 1. Visual Basic .NET Test Harness

9. Traverse the data value elements to generate row and column totals (see Listing 5, "Get row totals"). Jet crosstab queries support row but not column totals.
10. Format the data value elements for your locale's currency (see Listing 5, "Format rows and columns"). U.S. accountants usually prefer currency symbols for the first row of values and totals only (see Listing 6).
11. Generate the XML response document with string manipulation code (the easy way, Listing 5, "Generate the response document") or an XmlTextWriter object (the hard way). Element attributes for DHTML drill-down navigation and worksheet cell locations (in RnCn format) are optional (see Listing 7).
12. Optionally, add code to the XML Web service to transform the XML to XHTML for presentation. Alternatively, reduce server resource consumption and minimize response document size by executing the XSLT code on the ASP.NET client.

¡Muy complicado! ¿Es lento, no?

Figure 2. ASP.NET Crosstab Report Client

No way, José. A Visual Basic .NET test harness executes the entire process in slightly more than a second with a test database that has about 14,000 orders and 35,000 line items (see Figure 1). The XML Web service overhead adds slightly more than 0.1 second. Timing values are for a bargain-basement (866-MHz Pentium III) server with 512 MB of RAM and a fast IDE drive (see Figure 1). You can give the ASP.NET client version a test drive and verify response times at the OakLeaf Web site (see Figure 2).

I'm convinced that the "fill in the array" method is la mejor cosa después del pan rebanado for delivering high-performance crosstab reports by XML Web services. Let me know if you have a better or faster approach.

About the Author
Roger Jennings is a principal of OakLeaf Systems, columnist for FTP's family of Web sites, and contributing editor for Visual Studio Magazine. His primary computer-related interests are XML Web services, and client/server and XML/XSLT database applications. Roger's latest books include Visual Basic .NET XML Web Services Developer's Guide (Osborne/McGraw-Hill), Special Edition Using Access 2002 (Que), Special Edition Using Windows 2000 Server (Que), Admin911: Windows 2000 Group Policy (Osborne/McGraw-Hill), and Database Developer's Guide with Visual Basic 6 (Sams). OakLeaf's U.S. Code of Federal Regulations XML Web services recently won a charter Microsoft .NET Best Award. Reach Roger at .

This article's examples are from Chapter 8 of the forthcoming Visual Basic .NET XML Web Services Developer's Guide, "Delivering Reports with XML Web Services," with permission of the publisher. Read a preview version of Chapter 6, "Converting XML Web Service Test Clients to ASP.NET," at http://www.oakleaf.ws/.