Load a Spreadsheet from a DB2/400 Database: Part 3

Published: January 28, 2009

by Michael Sansoterra

Note: The code accompanying this article is available for download here.

Part 1 and Part 2 of this little series on generating an Excel spreadsheet demonstrated how to use Visual Basic for Applications (VBA) code to retrieve data from DB2 on an AS/400 and place it in a spreadsheet. Additional refinements were offered on doing some fancier things to the spreadsheet, such as auto-sizing all the columns based on the loaded data, formatting date columns properly, etc. All of this was done in the name of programmatically creating a nice spreadsheet with current "DB2 for i" data. This tip is going to add one little tidbit to the mix. I want to address how to populate multiple worksheets within a single workbook.

For example, say the purchasing department requests an Excel based report with the following information:

1. The top 10 vendors who have the highest percentage of rejected received items
2. The top 10 vendors who had the largest orders (in terms of quantity purchased)
3. The top 10 products (in terms of quantity purchased)

Following are three SQL queries to go with the above requests. (For the record, I exported Microsoft's Adventure Works data to DB2 for this demo.)

Query 1--Top Rejects

Select PO.VendorId,AccountNumber,Name,OrderQty,RejectQty,
       AvgRejectRate
  From (
Select VendorId,Sum(OrderQty) As OrderQty,
                Sum(RejectedQty) As RejectQty,
                Count(*)As Lines,
                Sum(RejectedQty)/Count(*)As AvgRejectRate
  From PurchaseOrderDetail D
  Join PurchaseOrderHeader H On H.PurchaseOrderId=D.PurchaseOrderId
Group By VendorId 
) PO
  Join Vendor PV On  PV.VendorId=PO.VendorId
  Order By AvgRejectRate Desc
Fetch First 10 Rows Only

Query 2--Top Vendor Purchases (in Qty terms)

Select PO.VendorId,AccountNumber,Name,OrderQty
  From (
Select VendorId,Sum(OrderQty) As OrderQty,
                Count(*)As Lines
  From PurchaseOrderDetail D
  Join PurchaseOrderHeader H On H.PurchaseOrderId=D.PurchaseOrderId
Group By VendorId 
) PO
  Join Vendor PV On PV.VendorId=PO.VendorId
Order By OrderQty Desc
Fetch First 10 Rows Only

Query 3--Top Product Purchases

Select PO.ProductId,Name,OrderQty,No_Orders
  From (
Select ProductId,Sum(OrderQty) As OrderQty,
                 Count(*)As No_Orders
  From PurchaseOrderDetail D
Group By ProductId 
) PO
  Join Product P On P.ProductId=PO.ProductId
Order By OrderQty Desc
Fetch First 10 Rows Only

After doing a little code (expanding on the previous example) to allow multiple queries to be submitted, the figure below shows a snapshot of the results in Excel 2000 (2000 or higher will work). As evidenced by the worksheet tab, each query has its own worksheet:

This multiple worksheet method allows us to compile the results into one convenient workbook to be delivered to the requestor.

The linked VBA code demonstrates how to fetch data for multiple queries and place each result in its own worksheet. A reference to the ActiveX Data Object (ADO) library is required in your proejct. For the ADO plug-in, the code itself uses the IBMDA400 OLE DB provider that comes with System i Access (formerly known as Client Access and iSeries Access). The IBMDASQL or ODBC data providers could be used as well.

The entry point subroutine to do the work is called CreateWorkbook and has the following parameter signature:

Public Sub CreateWorkbook(SQLQueries() As String, SheetNames() As String)

The concept is simple: Pass an array of SQL query definitions (which can be non-parameterized stored procedures and SELECT statements) and an array of worksheet names (assuming each array has the same number of elements) and you are done. Within the code an ADODB.Command object is used to process each query request and then dump the results to a worksheet within a single workbook. Of course, many improvements can be made, including allowing queries from multiple data sources, or parameterized stored procedure calls. But at least enough is there to get you started.

An alternative approach to the method demonstrated here would be to call a single stored procedure that returns multiple result sets. (The NextRecordset method of the ADODB.Recordset object can be used to fetch each succesive result set from a stored procedure that returns more than one result set.) In this case, the CreateWorkbook subroutine would receive one stored procedure name and an array of worksheet names to match the number of expected stored procedure result sets.

Finally, note that this code (as with the prior examples) is assumed to be running within an Excel environment. The code can be easily modified to work with other VBA environments (Word, Visio, Access, etc.) In order to do this, simply reference the Microsoft Excel library in your project and then create an instance of the Excel.Application object. The workbook and worksheet objects should be referenced from your top level Excel.Application object. In VB Script, likewise use the CreateObject function to create an instance of Excel.Application.

I recently implemented this technique for a SQL Server project with very promising results. I needed to combine seven related result sets into a single workbook instead of creating individual workbooks. A little extra code can even do more for the user by creating links between the various worksheets if they exist or by creating charts and pivot tables. Building Excel spreadsheets programmatically is a powerful reporting tool that users love. Almost any shop can find a useful implementation for these techniques.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.

RELATED STORIES

Load a Spreadsheet from a DB2/400 Database

Load a Spreadsheet from a DB2/400 Database: Part 2

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot