Building your pivot table
To build a pivot table and a report that investigates the
salary of each employee as detailed in the STAFF table, perform the following
Note: The STAFF table
is located in the SAMPLE database. You can create this database, populated with
data, by entering the db2sampl command from your operating systems
command prompt. I inserted new data into the STAFF table to create salary
distortions for this example. You can insert any data that you want. (Your worksheet
will look different, but you should still be able to follow along.)
Select Salary from the Pivot Table Field List. Instantly, the
pivot table chart is updated with this data and looks similar to the following
Also, note that on the left side of
the worksheet, Excel 2007 keeps a numeric table of all the analysis you
perform. I will refer to this table when it dynamically changes in response to
the operations we perform on the pivot table chart since they are linked:
For example, weve only included
the sum of all salaries in the STAFF table, so that is the only data shown in column
A row 4.
This option gives you the
flexibility to work with your data numerically, or visually using a chart. Also,
note that the Values field in the Pivot Table Field List is
automatically updated with the Salary column after you click the
corresponding check box:
As you can see in the previous figure,
you can perform a number of options on any column in any field by clicking a
columns corresponding properties drop-down arrow ().
Since we want to look at the distribution of salaries in this company by
department, drag the DEPT column from the Choose fields to add to
report list and put it in the Axis Fields (Categories) list so that
your Pivot Table Field List looks like this:
You will also see that instantly
the salary distribution changed from a simple summary to a summary by
Note that the details that underpin
the previous chart changed as well:
Now the data is a little more
interesting because we are able to investigate the breakdown of salary by
break down the granularity of the salary information by dragging NAME below
the DEPT column in the Axis Fields (Categories) list. Your report
should now look similar to the following example:
With this report across two
dimensions, I can learn a little more about my data. While the initial report
in Step 2 suggested that departments 20 and 66 have relatively high salary
costs, department 20s Melnyk is certainly above all other employees and
warrants further investigation is he that good? -->(The answer is yes.)
From the previous step, we know that employee Melnyks salary seems
high, but we should likely investigate the kind of job he performs as well.
Drag JOB to Legend Field (Series). Your pivot table should now
look like this:
Again, as you drag and drop data into the pivot filter
fields, the values and the corresponding chart are dynamically updated.
Note: If you dont
want your charts and reports to change every time you move a data column, you
can select Defer Layout Update; nothing will change in the pivot
table (or its report) until you click Update:
Now that we have a pivot table, we can explore some of the browsing
features that Excel 2007 offers you for your DB2 9 data. For example, in the
pivot table itself, you can collapse columns of data by clicking the
corresponding toggle ().
In addition, when you collapse columns in the pivot table, they are automatically
collapsed in the corresponding chart:
Pivot the table such that salaries are shown by name then department as
opposed to department then name (as is the case in the previous figure). Drag DEPT
column in the Axis Fields (Categories) list below the NAME column.
Your worksheet should now look similar to this:
the table again such you can look at the salary distribution of the employees
by clerk by ensuring that your Pivot Table Field List looks like this:
Your pivot table chart should now
look similar to the following chart:
And the data should look similar
all the data from your pivot table by selecting the chart and clicking Analyze>Clear>Clear All:
Drag DEPT to the Axis Fields (Categories), SALARY to
Values, and JOB to Legends Fields (Series) to create a new
pivot table and report that displays the total salary broken down by department
and then subdivided by job type. Your pivot table should now look like this:
And the corresponding data should look like this:
Perform a final pivot of this table such that the report now breaks down
the total salary costs by department as a function of job category by changing
the location of the JOB and DEPT columns in the Drag fields
between areas below list. Your worksheet should now look like this:
And the data should look like this:
Wrapping it up...
In this article, I showed you how to leverage the DB2 9 data
server connection that you created in a previous article to create a pivot
table and a corresponding chart. I also took you through the steps to populate
this table, look at slices of data, drill down into this data, and pivot the
data around salary. The point is that once you get your pivot table to Excel
2007 its just Excel functions but its all DB2 data. In my next article, Ill
show you some more things you can do with a pivot table.
See All Articles by Columnist Paul C. Zikopoulos
About the Author
Paul C. Zikopoulos, BA, MBA, is
an award-winning writer and speaker with the IBM Database Competitive
Technology team. He has more than thirteen years of experience with DB2 and has
written more than one hundred-fifty magazine articles and is currently working
on book number twelve. Paul has authored the books Information on Demand:
Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification
Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand:
Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2
Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals
Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on
Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE)
and a DB2 Certified Solutions Expert (Business Intelligence and Database
Administration). In his spare time, he enjoys all sorts of sporting activities,
including running with his dog Chachi, avoiding punches in his MMA class, and
trying to figure out the world according to Chloë his daughter. You can reach
him at: email@example.com.
IBM and DB2 are registered trademarks of International
Business Machines Corporation in the United States, other countries, or both.
Microsoft is a trademark of Microsoft Corporation in the United
States, other countries, or both.
Linux is a registered trademark of Linus Torvalds in the United
States, other countries, or both.
Other company, product, or service names may be trademarks
or service marks of others.
Copyright International Business Machines Corporation, 2007.
All rights reserved.
The opinions, solutions, and advice in this article are from
the authors experiences and are not intended to represent official
communication from IBM or an endorsement of any products listed within. Neither
the author nor IBM is liable for any of the contents in this article. The
accuracy of the information in this article is based on the authors knowledge
at the time of writing.