Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/DataPilot dialog"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Basic layout)
Line 14: Line 14:
 
The ''Data Fields'' area in the middle must contain at least one field. Advanced users can use more than one field here. For the data field an aggregate function is used. For example, if you move the '''sales''' field into the ''Data fields'' area, it appears there as '''Sum – sales'''.
 
The ''Data Fields'' area in the middle must contain at least one field. Advanced users can use more than one field here. For the data field an aggregate function is used. For example, if you move the '''sales''' field into the ''Data fields'' area, it appears there as '''Sum – sales'''.
  
''Row Fields'' and ''Column Fields'' indicate in which groups the result will be sorted in the columns and rows. If there are no entries in the row or column fields, then there won’t be partial sums for the corresponding rows or columns. There are often more than one field at the same time used to get partial sums for rows or columns. The order of the fields gives you then the order of the sums from overall to specific.
+
''Row Fields'' and ''Column Fields'' indicate in which groups the result will be sorted in the rows and columns. If there are no entries in one of these areas, then partial sums will not be provided for the corresponding rows or columns. Often more than one field is used at the same time to get partial sums for rows or columns. The order of the fields gives you the order of the sums from overall to specific.
  
If you drag for example '''region''' and '''employee''' into the area ''Row Fields'', the result will be the following: in the vertical direction (row wise), the sum will be divided into the branches. Within the branches will be the listing for the different regions.
+
For example, if you drag '''region''' and '''employee''' into the ''Row Fields'' area, the sum will be divided into the employees. Within the employees will be the listing for the different regions.
  
Fields that are placed into the area ''Page Fields ''appear in the result above as a drop down list. The summary in your result takes only that part of your base data into account that you’ve selected. For example, if you use the field '''Employee''' as a page field, you can filter the shown results for each employee.
+
Fields that are placed into the ''Page Fields'' area appear in the result above as a drop-down list. The summary in your result takes only that part of your base data into account that you’ve selected. For example, if you use the field '''Employee''' as a page field, you can filter the result shown for each employee.
  
To remove a field from the white layout area just move it with the mouse to the border and let it drop there. The cursor will change to a crossed symbol. As alternative you can use the button '''Remove'''.
+
To remove a field from the white layout area just drag it to the border and drop it (the cursor will change to a crossed symbol) or click the '''Remove''' button.
  
 
=== More options ===
 
=== More options ===

Revision as of 17:07, 27 December 2010



The function of the DataPilot is mainly managed in two places: first in the DataPilot dialog and second through manipulations of the result in the spreadsheet. This section describes the dialog in detail.

Basic layout

In the DataPilot are four white areas, that show the layout of the result. Beside these fields are buttons with the names of the fields in your data source. To define a layout, drag and drop these field buttons into the white areas.

DataPilot dialog

The Data Fields area in the middle must contain at least one field. Advanced users can use more than one field here. For the data field an aggregate function is used. For example, if you move the sales field into the Data fields area, it appears there as Sum – sales.

Row Fields and Column Fields indicate in which groups the result will be sorted in the rows and columns. If there are no entries in one of these areas, then partial sums will not be provided for the corresponding rows or columns. Often more than one field is used at the same time to get partial sums for rows or columns. The order of the fields gives you the order of the sums from overall to specific.

For example, if you drag region and employee into the Row Fields area, the sum will be divided into the employees. Within the employees will be the listing for the different regions.

Fields that are placed into the Page Fields area appear in the result above as a drop-down list. The summary in your result takes only that part of your base data into account that you’ve selected. For example, if you use the field Employee as a page field, you can filter the result shown for each employee.

To remove a field from the white layout area just drag it to the border and drop it (the cursor will change to a crossed symbol) or click the Remove button.

More options

To expand the DataPilot and show more options, click More.

Expanded dialog of the DataPilot

Results to

Results to defines where your result will be shown. If you do not enter anything, the DataPilot will put your result below the list, that contains your data. If you leave – undefined – and you enter a cell reference, then the DataPilot will show the results at this place. It's useful instead of using the setting – undefined – to use the setting - new sheet –. Calc adds in this case a new sheet. This is useful, because it is much cleaner and makes sure that no areas with data are overwritten. The word - undefined – used in this place is wrong and misleading. The output position is also exactly defined. It is somewhat annoying, that the default setting is not -new sheet -, which is in almost all cases the needed setting.

Ignore empty rows

By using this setting, completely empty rows in the source area will be ignored by the DataPilot. As mentioned in the requirements for the database, empty rows are against the rules for processing list-like data within a spreadsheet. Therefore, this setting is used on the rare occasions when the source data is not in the expected (recommended) form.

Identify categories

This option also has an effect if the basic data does not meet the recommended data structure. It takes effect when there are missing entries in a list, as shown in this example:

Example of Identify categories

The option Identify categories would make sure that in this case the lines 3 and 4 are added to the product Apples and the row 6 is added to Pears. Without category recognition, the DataPilot shows an empty category.

Example without Identify categories

From a purely logical point of view, the behavior without category recognition is more correct. A list with the shown missing entries is also less useful, because you cannot use other functions (like sorting or filtering).

Total columns / total rows

With this option you decide if the DataPilot will show an extra row with the sums of each column or if it adds on the very right a column with the sums of each row. In some cases, an added total sum is useless, for example if your entries are accumulated or in comparisons.

Add filter

This option allows the blending or hiding of the cell labelled Filter above the DataPilot results. This cell is a convenient button for additional filtering options within the DataPilot.

Enable drill to details

In the table area of the DataPilot result, a double-click has a special effect. Normally a double-click on any cell switches to the edit mode, but within the DataPilot a listing of the detailed data that give you the shown result will appear. By disabling this function the double-click will keep its usual function within a spreadsheet.

More settings for the fields

The discussed options are valid for the DataPilot in general. Additionally, you can change settings for every field, that you have added to the DataPilot layout. You can reach the dialog for the field settings either by clicking on the button Options in the DataPilot dialog box or by a double-click on the appropriate field.

There are differences between data fields, row or column fields, and page fields of the DataPilot.

Data fields

In the preferences dialog of a data field you can select the Sum function for accumulating the values from your data source. In many cases you will need the sum function, but other functions (like standard distribution or a counting function) are also available. For example, the counting function can be useful for non-numerical data fields.

On the Data Field dialog, click More to see more options.

Expanded dialog for a data field

In the Displayed value section, you can choose more analyzing possibilities by using the aggregate function. Depending on the setting for Type, you have to choose a definition for Base field and Base element.

Type Base field Base element Analysis
Normal Simple use of the chosen aggregate function (for example, sum)
Difference from Selection of a field from the data source of the DataPilot (for example, employee) Selection of an element from the selected base field (for example, Brigitte) Result as difference to the result of the base element (for example, Sales volume of the employees as difference of the sales volume of Brigitte)
 % of Selection of a field from the data source of the DataPilot (for example, employee) Selection of an element from the selected base field (for example, Brigitte) Result as a ratio based on the result of the base element (for example, Sales result of the employee relative to the sales result of Brigitte)
 % difference from Selection of a field from the data source of the DataPilot (for example, employee) Selection of an element from the selected base field (for example. Brigitte) Result as relative difference to the result of the base element (for example, Sales volume of the employees as relative difference of the sales volume of Brigitte)
Running total in Selection of a field from the data source of the DataPilot (for example, date) Result as a continuing sum (for example, Continuing sum of the sales volume for days or months)
 % of row Result as relative part of the result in the whole row (for example the row sum)
 % of column Result as relative part of the total column (for example, the column sum)
 % of total Result as relative part of the overall result (for example the total sum)
Index Default result x total result / (row result x column result)

Row and column fields

In the preferences dialog for the row or column fields, you can choose if there are partial sums for each category. This is deactivated by default. It is only useful if the values in one row or column field can be divided into partial sums for another (sub)field.

Some examples are shown in the next three figures.

No subdivision with only one row or column field.
Division of the regions for employees without partial sums
Division of the regions for employees with partial sums

Choose the option Automatically to use the aggregate function for the partial results that can also be used for the data fields (see above). To set up the aggregate function for the partial results independently from the overall settings of the DataPilot, choose User-defined.

preferences dialog of a row or column field

Normally, the DataPilot does not show a row or column for categories that have no entries in the underlying database. With the help of the Show items with no data option, you can force this.

For illustration purposes, the data was manipulated in such a way, that the employee Brigitte has no sales values for the category golfing.

Default setting
Setting “Show Items with no data”

Page fields

The preferences dialog for page fields is the same as for row and column fields, even though it appears to be useless to do the same settings as described for the row and column fields. With the flexible use of the DataPilot you can switch often the use of the different fields for pages, columns or rows. The fields keep the settings that you made for them. For this, the page field has the same properties as a row or column field. These settings only take effect when you use the field not as page field but as row or column field.


Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA).
Personal tools