Documentation/OOo3 User Guides/Calc Guide/DataPilot example 2

OpenOffice.org 3 Calc Guide

Chapter 8 Using the DataPilot

< Previous Section

Next Section >

• Example: Sales volume overview
• Example: Timekeeping
• Example: Frequency distribution
• DataPilot functions
• Data source
• DataPilot dialog
• Working with the results of the DataPilot
• Sorting, drilling, filtering, refreshing
• Multiple data fields
• GETPIVOTDATA

< Previous Page

Next Page >

This example is often used by consultants and in several variations in user support. The task is to provide a means for one or more users to keep track of working hours.

A typical way of doing this is to create a spreadsheet per month and a sum sheet with all the results of one year. For each employee, there is one file.

One month of timekeeping for one employee

Yearly sums for one employee

Practical problems and questions

1. It is very difficult and time-consuming to create the timekeeping table: 12 sheets that have to be copied from a raw template and adjusted for each month, and a sheet with all the yearly sums with references to all the other sheets. Users often search for a macro to make the creation easier.
2. The file shown contains only the data for one employee. How can you get all the data for all the employees, so that you can have a summary of all the work hours from all employees of a department or the whole company?
3. How can you compare employees of a department or the whole company?
4. The file shown contains data for one year. How can you compare it with the data of the previous years?

Solution

To use the Pivot Table for this task, collect all data into one single table. This can be done either manually or by importing a file from an (electronic) timekeeping machine.

In very simple cases, each employee takes care of their own working hours. If you need calculations that cover several employees, departments, or the whole company, just copy everything into one huge table.

Data basis in Calc

Using the Pivot Table just needs 12 mouse clicks and gives you within a few seconds a nice overview:

1. Select the cell A1 (or any other single cell within the list).
2. Choose Data → Pivot Table → Create and click  OK .
3. On the Pivot Table dialog:
   • Drag date into the Row fields area.
   • Drag hours into the Data fields area. Notice that it becomes Sum – hours.
   • Drag name into the Column fields area.
4. Click  More  to show more options in the lower part of the dialog.
5. In the Results to field, select – new sheet – from the drop-down list.
6. Click  OK .

Pivot Table dialog

The result appears on a new sheet.

The evaluation, done within seconds with Pivot Table

The result is much more powerful than is possible with the classic formula-based calculation. For example, you can summarize the daily results to a monthly result very easily.

1. To group together the rows, select the cell A5 (or any other cell that contains a date).
2. Choose Data → Group and Outline → Group and click  OK . The result is now grouped into months.

Monthly sums

If you need a result with a percentage, start the Pivot Table from this page.

1. Select the cell A3 (or any other cell that contains a result of the Pivot Table).
2. Choose Data → Pivot Table → Create.
3. Double-click on the field Sum – hours to open the Data Field dialog.
4. Click on  More , to see more options.
5. Switch Displayed value > Type to % of column.

Properties of the data field
6. Click  OK  twice.

The result shows percentages as a decimal number. If you prefer to have a percent format, select the cells and click on the % icon in the menu bar.

Result with percentages

To get a comparison between employees, start the Pivot Table again:

1. Select the cell A3 (or any other cell that contains a result of the Pivot Table).
2. Choose Data → Pivot Table → Create.
3. Double-click on the field Sum – hours to open the Data Field dialog.
4. Click  More , to see more options.
   • Switch the type of the Displayed value to Difference from.
   • Switch the Base field to name.
   • Switch the Base item to Brigitte.
5. Click  OK  twice.

Absolute comparison with Brigitte

As a final example, we switch to an accumulated view; that is, continuing sums of all values:

1. Choose Data → Pivot Table → Create.
2. Double-click on the field Sums – hours to open the Data Field dialog.
3. Click {[button|More}}, to see more options.
   • Switch the type of the displayed value to Running total in.
   • Switch the Base field to Date.
4. Click  OK  twice.

The Pivot Table now shows accumulated values

Differences and advantages

These examples show an important aspect of the DataPilot. Normally you have to collect your data according to the way you want the result to be represented. This means you have to use a specific structure and you’re stuck with it.

The DataPilot works more like a real database. The source data are collected in a simple spreadsheet that contains all data. Only when you want to look at it, you select which part of the data you want to use.