• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Build Pivot Tables over DB2 Data

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions.

    Upgrading to Power10, Power11, or cloud hosted system, Maxava has you covered!

    Book A Consultation Today

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ARCAD Software:  Register now for May 21 Practical Test Automation Webinar
    LANSA:  It's Time for 4 days of education at the LANSA User Conference, May 4 – 7, in Orlando
    Vision Solutions:  A Rewind Button for i5 Data? Read the Whitepaper

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Decline In Vulnerabilities Belies Threat Increase, Microsoft Says in New Security Report IBM Previews “Blue Business” SMB System Sales Approach

    Leave a Reply Cancel reply

Volume 8, Number 17 -- April 30, 2008
THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
Guild Companies

Table of Contents

  • Multiformat SQL Data Sets
  • Build Pivot Tables over DB2 Data
  • Solve a Client Access Mystery, Win a No Prize

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle