DB2 9 and Microsoft Excel 2007: Part 4: Creating a Pivot Table... - Page 2

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 system’s 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, we’ve 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 column’s 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 20’s 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 Melnyk’s 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:

Note: If you don’t 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: