It's time for more data pilot adventures.
I blogged here about the data pilot. The example I used there was for analyzing data in your spreadsheet. You also, of course, have the option of analyzing, using the data pilot, any of your data sources. You'd want to analyze a data source rather than a spreadsheet for many reasons-- perhaps the data is in Access and not in a spreadsheet. Or the spreadsheet is just realllllly big and you don't want to have to open it every time you want to run data pilot on it.
Here's my sample data for this blog.
Just a nice basic set of data for a week. It's similar but different to what I used in the previous blog and more suitable for demoing the different options. Click to see it bigger.
The first step, of course, is to make sure you have a data source. (Or database—it's all kind of the same thing.) If you already have your data in Access or another database, you'd skip this, of course. However, let's say that this spreadsheet is extremely large and I need to make a database out of it to save time.
Here's what you do to create a database from a spreadsheet; using a different type of data is pretty similar.
1. Choose File > New > Database.
3. In the next window, just specify where the spreadsheet is that you want to turn into a database. Click Next.
4. Unmark the Open for Editing option and click Finish.
5. Save the database with an obvious name. This is the name you'll be selecting from dropdown lists in OpenOffice.org.
Now you've got a database.
To analyze a database, start the same way as usual.
1. Open a new empty spreadsheet and choose Data > Data Pilot > Start.
2. Choose to use a data source already registered with OpenOffice.org. Click OK.
3. Select the data source name, then the sheet name (typically Sheet1) where your data is, and specify Sheet as the type of data. Click OK.
4. Now you're ready to drag fields into the layout area and set options as you have with other analyses. This time, I'm using the Date fields as the quick filter field, so I put it in the Page area. The rest is as pictured.
- I double-clicked the Units Sold field and selected Average. In the results, we'll see the average for each book and store, not the total.
- For options, I'm choosing to identify categories, and to sum rows and columns.
Here are the results of the setup shown above.