Build Pivot Tables over DB2 Data

Published: April 30, 2008

Hey, Ted:

If you already know about these, then just hit the ol' delete key on the message. I learned how to do this today. SQL is great for going "down the page." It's when they want data summed across that it gets to be a real kludge! Pivot tables are the answer.

It started with your article Load a Spreadsheet from a DB2/400 Database. I got it working! Sweet! Miracles never cease! Thanks a bunch!

Once the data is loaded into the spreadsheet via the SQL statement, make sure the column headings have decent labels. Open the Data menu and select Pivot Tables. Select the rows/columns to include. Click on Next, then Layout. Here's where you design how you want the data totaled. This is the magic!

I fumbled around and found how to make the report look different. I think it's via a right click in the pivot table's sheet, then choose Format Report. Just try them until you find one that you like.

--WEC

Thanks for sharing your experience, Bill. I'm glad you got it to work for you, and I like it when readers take the things we run in this newsletter and improve on them. I thought about breaking this pivot-table technique into more detailed steps for the readers, but the pivot table wizard is easy enough to use, so I'll add a few comments and leave the matter in the readers' capable hands.

First, if you have an appropriate ODBC connection, you don't have to use the Visual Basic technique I described in my article. Excel's pivot table wizard can import the data directly from DB2 for i, or whatever I'm supposed to call this database nowadays. When you define the ODBC source, include the library (or libraries) you'll need and select SQL naming format (library.object) rather than system format (library/object).

Second, if you do use the Visual Basic routine, don't use my code. Instead, use the source code Michael Sansoterra published in the article Load a Spreadsheet from a DB2/400 Database, Part 2. Mike's code is superior to the code I was using.

Last, we've published a couple of i-based utilities that generate pivot tables. See the related stories below.

--Ted

RELATED STORIES

Creating Pivot Tables on the iSeries

Pivot Data with SQL/400

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