Problem solve Get help with specific problems with your technologies, process and projects.

Using the data pilot: Calc's answer to pivot tables

The data pilot, the OpenOffice equivalent to Excel pivot tables, is a useful feature that lets you apply functions and slice and dice data to see different parts of it in different ways. An expert demonstrates the basics of the data pilot, two filtering methods and how to tweak the data display.

With the right statistics, you can prove anything. Whether you want to prove that coffee shops serving your company's...

brand of coffee have 12.5% more attractive customers, that your spouse does only 23.42% of the chores around the house, or that the Flying Spaghetti Monster is the one true deity, you need to be able to manipulate and slice and dice your data to produce the right statistics.

More on OpenOffice

OpenOffice 2.0 Toolbox

Follow the expert: Making an 2.0 presentation

To do this, of course, you'll need to have a good tool. In, one of your choices is the data pilot, the equivalent of pivot tables in Excel.

In my article about functions, I talked about how good software features can turn data into information. The data pilot is another useful feature that lets you not only apply functions like Sum and Average to your data but also slice and dice the data to see different parts of it in different ways.

Here's an example of plain old data in a spreadsheet and the different ways you could arrange it, quite easily, using the data pilot.

In this tip, I'll walk through how to use the data pilot in a few way: just the basics, then with the two filtering options. I'll end with a rousing round of the additional options for tweaking how the data is displayed.

First: The kind of data you want

When I first started out fiddling with data pilots, I used data that was already analyzed and added up. What you want is nice, plain, raw data that you can manipulate in different ways, like your actual invoices and items on each invoice.

I'm using slightly more processed data for this example, to make the examples easier and the data less voluminous, but it still works. We'll use the data for one week in September for book sales for four particular books, at the three different locations for this bookstore. The data are the total amount in sales for each book at each store, as well as the number of units sold of each book.

If you have more numbers to calculate in your data, that's fine too. You can choose the fields that you want to be calculated for each analysis.

Regarding the form of the data, you can analyze a data source or just the data in the spreadsheet. You're prompted to choose this as the first step each time.

A data source is anything you've created using File > New > Database,and that shows up when you choose View > Data Sources. Creating these is outside the scope of what I want to cover today, but everything works the same way whether you use a spreadsheet or a data source.

Sometimes you'll want to use a data source, such as if the data is already in an Access or Oracle database or if it's just in the spreadsheet but it's absolutely enormous and you don't need to open it.

If you want to learn more about creating a database, see this article.

Creating a basic analysis with the data pilot

1. First, open a spreadsheet. It should be:

  • The spreadsheet containing the data you want to analyze. Click in the upper left corner of the data you want to analyze.

  • Or a new empty spreadsheet if you're going to be analyzing data in a data source

2. Choose Data > Data Pilot > Start.

3. Let the program know where your data is.

  • Leave the Current Selection option selected if you've opened a spreadsheet containing the data you want to analyze. Click OK.
  • Select the Data Source Registered in option if you're going to be analyzing data in a data source. Then click OK, and in the window that appears, select the data source, the table or other item such as a query, then finally select the category of information you're using. Click OK.

4. Now you're ready for business. The window is fairly straightforward—just drag the fields you want into the place where you want them.

I'll stick to the basics in this one, so here's what you'd do to view the stores across the top, the book names on the side and the total sales in the middle. The default is for all the information to be totaled for the rows and columns, as well.

Here's the analysis that the above setup would give you; it appears below the data if you're analyzing data in the current spreadsheet. The analysis added up the amount sold for each book, at each store, for the whole week and displays it in the appropriate cell. Then the totals for the rows (books) and columns (stores) are added up as well, so you have subtotals. I'll talk about the Filter button later.

To delete or refresh the analysis, right-click anywhere in the analysis and you'll see these options.

This is a nice way to look at data, and pretty easy. But let's say you want More.

Using the filters and detail view in the data pilot

You'll usually be dealing with far larger data sets than the example here. That's where the filters come in handy. One is the Filter button you've already seen but that I haven't talked about; the other is available when you use the Page area of the analysis layout. There's also a Detail Drilldown that's on by default, which can help you go from the individual data back to the analysis results.

Here's how you would set up an analysis where you want as much filtering and analysis as possible. You'd drag the field you want to restrict on, such as Book here, to the Page area. Then click More and be sure that the Add Filter and Enable Drill to Details options are marked. Click OK.

Here are the results for that setup.

To use the filter created by the field in the Page area of the setup, just click on the dropdown list and select different data.

To use the filter button, click it and enter your restrictions.

To drill down and see detail, just double-click on any of the category values, such as a particular date or the name of a store. You'll get a window asking what data you want to see more detail on. Select the data and click OK.

You'll see the detail for the field. Double-click the same category value to go back to the analysis view.

Tip: You can use more than one page field. Here are two dropdown lists, for data and for store.

Using functions other than Sum

There's more to life than addition, and with the data pilot you have several other choices. To apply something other than Sum, drag the field to calculate into the center as usual. In this example, just to show other options, I'm also including two fields in the center.

To change from Sum to something else, double-click the calculated field or click Options. This window will appear. Select a function in the scrolling list, then click More if you want some really serious statistical options. When you're done, click OK.

Here are the results of the setup you saw earlier, with Mean and Max functions instead of Sum.

Controlling where the analysis shows up

This is easy. Click More, and specify the sheet and the cell where you want the upper left corner of the analysis to appear.

You can also just select New Sheet. A new sheet will be created after the current one and the analysis will be displayed in that sheet.

Charts, data sources and the data pilot

You might find that you prefer to do your number crunching in the data pilot, rather than in your charts. Just generate the analysis, then create the chart as you normally would. You might need to add or delete some labels here and there in the analysis, or copy and paste the data to a different spreadsheet to get it to behave correctly.

Here's the analysis, which I pasted into a separate sheet. I selected only the core data, not the identifier at the top.

And here's the resulting chart.

This is also a benefit since it means you can more easily chart information kept in a database.

For information on charts, see this article.

Converting an analysis to a table

You can paste spreadsheets into Writer and turn them into tables, as well. Just copy what you want, go to a Writer document, and choose Edit > Paste Special.

In the window that appears, choose formatted text.

The data will appear, as a table without borders. To apply borders, select all the data and in the Tables toolbar, click on the borders icon and choose the bottom right icon.

The borders will appear around your data.

Let the data pilot take you up for a spin

I really like the data pilot. It's a decent interface, it seems pretty powerful (remember the Select Function window?), and there don't seem to be any wacky bugs or other craziness. Plus, there's the aspect of getting data out of and into different formats: out of spreadsheets or databases and into charts and tables.

So fiddle around with it, see what you think about what it can do for you and your statistics needs. Whether you use the resulting information for good or evil, you're going to have a pretty powerful tool to create it with.

What other OpenOffice tips would you like to see from Solveig? Email us and let us know.

Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.

Add your voice! Click the following links if you'd like to vote for the features that would make exporting and importing data easier.

Vote for adding a wizard to import data into Base:

Utility to export CSV from Base:


Dig Deeper on Linux servers