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

From Apache OpenOffice Wiki
Jump to: navigation, search
(Basic layout)
 
(9 intermediate revisions by one other user not shown)
Line 29: Line 29:
 
'''Results to'''
 
'''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.
+
''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. This could result in overwriting any data that is already in that location. To avoid overwriting data, you can leave ''Results to'' as - '''undefined''' -; and enter a cell reference to tell the DataPilot where to show the results. However, a generally better approach is to use - '''new sheet''' -  to add a new sheet to the spreadsheet file and place the results there.
 +
 
 +
{{Note|In this case the word - undefined - is misleading because the output position is in fact defined.}}
  
 
'''Ignore empty rows'''
 
'''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.
+
If the source data is not in the recommended form, this option tells the DataPilot to ignore empty rows.
  
 
'''Identify categories'''
 
'''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:
+
If the source data has missing entries in a list and does not meet the recommended data structure, the DataPilot adds it to the listed category above it. If this option is not chosen, then the DataPilot inserts (empty).
  
[[Image:CG3Ch6F32a.png|thumb|none|500px|''Example of Identify categories'']]
+
[[Image:CG3Ch6F32a.png|thumb|none|500px|''Example of data with missing entries in column A'']]
  
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.
+
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.
 +
 
 +
[[Image:CG3Ch8F37A.png|thumb|none|500px|''Example with Identify categories'']]
 +
 
 +
Without category recognition, the DataPilot shows an empty category.
  
 
[[Image:CG3Ch6F32b.png|thumb|none|500px|''Example without Identify categories'']]
 
[[Image:CG3Ch6F32b.png|thumb|none|500px|''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).
+
Logically, the behavior without category recognition is better. 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'''
 
'''Total columns / total rows'''
Line 53: Line 59:
 
'''Add filter'''
 
'''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.
+
Use this option to add or hide 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'''
 
'''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.
+
If you double-click on a single cell in the DataPilot result, this function gives a more detailed listing of an individual entry. If this
 +
function is disabled, the double-click will keep its usual edit function within a spreadsheet.
  
 
=== More settings for the fields ===
 
=== 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.
+
The options discussed in the previous section are valid for the DataPilot in general. Additionally, you can change settings for every field that you have added to the DataPilot layout. Do this either by clicking on the '''Options''' button in the DataPilot dialog or by double-clicking on the appropriate field.
  
 
There are differences between data fields, row or column fields, and page fields of the DataPilot.
 
There are differences between data fields, row or column fields, and page fields of the DataPilot.
 +
 
=== Data fields ===
 
=== 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.
 
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.
Line 70: Line 78:
 
[[Image:CG3Ch6F33.png|thumb|none|500px|''Expanded dialog for a data field'']]
 
[[Image:CG3Ch6F33.png|thumb|none|500px|''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'''.
+
In the ''Displayed value'' section, you can choose more possibilities for analysis by using the aggregate function. Depending on the setting for '''Type''', you have to choose a definition for '''Base field''' and '''Base element'''.
  
 
{| class="prettytable"
 
{| class="prettytable"
Line 135: Line 143:
  
 
=== Row and column fields ===
 
=== 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.
+
In the preferences dialog for the row or column fields, you can display the partial sums for each category. Partial sums are deactivated by default. They are 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.
 
Some examples are shown in the next three figures.
Line 145: Line 153:
 
[[Image:CG3Ch6F36.png|thumb|none|500px|''Division of the regions for employees with partial sums '']]
 
[[Image:CG3Ch6F36.png|thumb|none|500px|''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'''.
+
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, select '''User-defined'''.
  
 
[[Image:CG3Ch6F37.png|thumb|none|500px|''preferences dialog of a row or column field'']]
 
[[Image:CG3Ch6F37.png|thumb|none|500px|''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.
+
Normally, the DataPilot does not show a row or column for categories that have no entries in the underlying database. By choosing 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.
+
For illustration purposes, the data was manipulated in such a way, that the employee Brigitte has no sales values for the golfing category.
  
 
[[Image:CG3Ch6F38.png|thumb|none|500px|''Default setting'']]
 
[[Image:CG3Ch6F38.png|thumb|none|500px|''Default setting'']]

Latest revision as of 05:59, 7 July 2018



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. This could result in overwriting any data that is already in that location. To avoid overwriting data, you can leave Results to as - undefined -; and enter a cell reference to tell the DataPilot where to show the results. However, a generally better approach is to use - new sheet - to add a new sheet to the spreadsheet file and place the results there.

Documentation note.png In this case the word - undefined - is misleading because the output position is in fact defined.

Ignore empty rows

If the source data is not in the recommended form, this option tells the DataPilot to ignore empty rows.

Identify categories

If the source data has missing entries in a list and does not meet the recommended data structure, the DataPilot adds it to the listed category above it. If this option is not chosen, then the DataPilot inserts (empty).

Example of data with missing entries in column A

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.

Example with Identify categories

Without category recognition, the DataPilot shows an empty category.

Example without Identify categories

Logically, the behavior without category recognition is better. 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

Use this option to add or hide 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

If you double-click on a single cell in the DataPilot result, this function gives a more detailed listing of an individual entry. If this function is disabled, the double-click will keep its usual edit function within a spreadsheet.

More settings for the fields

The options discussed in the previous section are valid for the DataPilot in general. Additionally, you can change settings for every field that you have added to the DataPilot layout. Do this either by clicking on the Options button in the DataPilot dialog or by double-clicking 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 possibilities for analysis 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 display the partial sums for each category. Partial sums are deactivated by default. They are 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, select 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. By choosing 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 golfing category.

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