DB2 9 and Microsoft Excel 2007: Part 4: Creating a Pivot Table... - Page 2
August 13, 2007
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 steps:
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.)
1. Select Salary from the Pivot Table Field List. Instantly, the pivot table chart is updated with this data and looks similar to the following chart:
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 ().
2. 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 department:
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 department.
3. Further 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.)
4. 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:
5. 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:
6. 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:
7. Pivot 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 to this:
8. Clear all the data from your pivot table by selecting the chart and clicking Analyze>Clear>Clear All:
9. 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:
10. 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.
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: firstname.lastname@example.org.
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.