• 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
    Rocket Software

    Introducing Rocket Software’s Solutions for IBM®i: Ignite Your Business Potential!

    Unlock the true power of your IBM® i platform with Rocket Software’s cutting-edge solutions. Embrace innovation and streamline operations with our comprehensive tools to propel your business forward.

    Explore Rocket Software Application Modernization solutions to learn how you can optimize your IBM i infrastructure to deliver enhanced user and developer experiences from a modern, agile environment:

    • Rocket DevOps: Simplify compliance reporting and user access to critical systems.
    • Rocket Process Insights: Build a data-informed, smart modernization plan with bottom-line results.
    • Rocket API: Unlock critical data and deliver fast ROI with robotic process Automation (RPA) for IBM i.
    • Rocket Modern Experience: Modernize UI/UX application for productive outcomes.
    • Rocket iCluster: Ensure uninterrupted operation for your IBM i applications.
    • Rocket Terminal Emulator: Access host-based systems from browsers or mobile devices.
    • Rocket MFA: Build a layered defense against password vulnerabilities.

    Modernization. Without Disruption.
    Rocket Software

    Learn more

    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

  • Shield Builds on Success with Nagios for IBM i
  • Why You Should Be Concerned About the MGM ‘Vishing’ Attack
  • IBM Bolsters Database Security with Guardium 12.0
  • Four Hundred Monitor, September 27
  • The IBM i Marketplace Survey Needs Your Input
  • Rocket DevOps Now Supports VS Code
  • DR Testing As A Service: One More Thing That You Don’t Have To Do
  • The First Step In DevOps Is Not Tools, But Culture Change
  • As I See It: IT Come Home
  • IBM i PTF Guide, Volume 25, Number 39

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