• 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
    New Generation Software

    Free NGS-IQ Webinar: A Better Way to Connect IBM i Data and Microsoft 365 Users.

    Business users rely on Excel, SharePoint, Access, and other Microsoft tools to view, analyze, and share data. But what if your data is too sensitive and complex to simply open up to non-technical users via ODBC and Microsoft 365’s query functions?

    Attend our webinar on June 22, 2022, and see how you can make the people you support smile even as you centrally manage and secure IBM i data access.

    www.ngsi.com– 800-824-1220

    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

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.