OpenOffice.org Calc: Pivot tables by another name
DataPilots are OpenOffice.org Calc's equivalent of what MS Excel and other spreadsheets call pivot tables. Under any name, they are a tool for extracting and summarizing the information contained in spreadsheet cells in a more convenient form. Using a DataPilot, you can immediately see relationships between different pieces of data that would be difficult -- if not impossible -- to find using formulas, and tedious to extract manually. In effect, a DataPilot gives you something of the power of using a database without actually switching out of a spreadsheet. Small wonder, then, that over half of spreadsheet users are said to use datapilots or pivot tables.
To understand the usefulness of DataPilots, imagine that you are a manufacturer selling Ogg Vorbis music players in the North American market. Your product comes in two colors, beige and black, and in 80, 150 and 300 megabyte sizes, and is sold in sales packs. For each sale, you record the number of display packs sold, and the total price. On a spreadsheet, some of your data might look like this:
Notice that the columns all have labels. These labels are a pre-requisite for working with DataPilots.
When you come to analyze this data, you might want to know such questions as how many units of each color you sold, or how many units in each country. You could find this information by setting up a combination of filters and formulas, but creating a DataPilot is much quicker.
For example, to quickly find out how many sales packs you sold in each country, you could create the following DataPilot:
Sales for all countries are shown by default. However, if you use the Country filter at the top of the DataPilot, you can see the sales only for Canada:
The gray cells in each DataPilot represent filters that you can use to modify it. By clicking the one marked Filter, you can change the information displayed in the DataPilot.
In addition, you can drag the other filters into new positions to change the information display. For example, if you drag the Country filter to the right of the Quantity column in the existing datapilot, it now displays the quantity sold broken down by country, with the first few rows reading:
As you can see, an datapilot is an ideal way of obtaining new perspectives on your data with a minimum of effort.
Creating a DataPilot
To begin creating an datapilot, highlight the range of cells you want to base it upon, then select Data -> DataPilot -> Start to open the DataPilot dialog window. Alternatively, choose the same menu item, then select a data source that you have already registered with OpenOffice.org using File -> New -> Database and a range of cells from it.
The DataPilot window gives you a diagram of the DataPilot that you are creating, and a list of columns from the data source. To create the general layout for the DataPilot, all you have to do is drag the columns to one of the blank spaces on the diagram. If you drag a column name to the Column fields or Row fields space, then it becomes the first cell in a row or column, just as you might expect from the name (in the first DataPilot above, Quantity was selected as the column, and no row was chosen). Similarly, if you drag a column name to the Data fields, it becomes the data in the DataPilot (in the first example above, the Price). The only potentially puzzling choice is the Page Fields, which is actually just the custom filter for changing the contents of the DataPilot on the fly (in the first example, the Country). If you make a mistake, you can drag the column back to the list of column building blocks on the right.
Once you have done the basic setup, you can also choose what function to use in the DataPilot. In the examples above, I simply used the default Sum function, which for many purposes is all that you need. However, you can also use another ten basic functions: Count, Average, Max, Min, Product, Count (Numbers Only), StDev (Sample), StDevP (Population), Var (Sample) and Var (Population). If necessary, you can find details about what these functions do in OpenOffice.org's online help.
By default, the DataPilot is written directly below the range it is based upon. However, if you select the More button in the dialog window, you can set the cells or sheet to which it is inserted. You can also set additional options, such as ignoring empty rows. However, for the most part, you can safely ignore these options, especially when just starting to work with DataPilots, because the defaults are the ones you are likely to want most of the time anyway.
After you create the DataPilot, selecting part of it enables Data ->DataPilot -> Refresh if you need to update it because of a change to the source information. The same sub-menu contains a Delete item that you can use when you no longer need the DataPilot.
Learning to work with DataPilots can take some time. New users need to learn that columns, not rows, should contain labels. They also have to keep track of which columns contain data and which do not: If those that do not are dragged to the Data fields in the dialog window, then the result will be nonsense. However, for most people, only a few trial and errors should be needed to master the basics of DataPilots.
Soon, you'll be reading your data in ways you never considered. After that, what you do with DataPilots is up to your imagination.
Bruce Byfield is a computer journalist who writes for Datamation, Linux.com, and Linux Journal
Bruce Byfield (nanday)
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- Stunnel Security for Oracle
- SourceClear Open
- SUSE LLC's SUSE Manager
- My +1 Sword of Productivity
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- Tech Tip: Really Simple HTTP Server with Python
- Managing Linux Using Puppet
- Non-Linux FOSS: Caffeine!
- Doing for User Space What We Did for Kernel Space
- Google's SwiftShader Released
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide