Field Options in DataPilot

OpenOffice.org 2.0

Document - ID	Specification Owner	Last Change	Status
	Niklas Nebel	2004-05-03	Final
Conforms to	StarOffice Specification Workflow v0.1, Aug 2003
Applies to	Calc
Task ID(s)	i23447
Category	Feature

Abstract

The DataPilot feature will be extended to allow more settings for each field, for better flexibility and compatibility.

i-Team Members (The specification owner is part of the i-Team)

	Name	E-mail Address
User Experience	Falko Tesch (ft)	falko@sun.com
Development	Niklas Nebel (nn)	niklas.nebel@sun.com
Quality Assurance	Oliver Crämer (oc)	oliver.craemer@sun.com
Documentation	Frank Peters (fpe)	fpe@sun.com

Approved for Implementation

	Approved by	Date
User Experience	Falko Tesch	31.03.2004
Development	Niklas Nebel	2004-04-01
Quality Assurance	Oliver Crämer	2004-04-16
Documentation	Uwe Fischer	2004-04-16
String Review	Elizabeth Matthis	2004-04-30

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial revision	NN	2003-12-10
1.1	Added details on "Displayed Value"	NN	2004-01-23
1.2	Added various comments and mockups	FT	2004-02-09
1.3	Niklas?		
1.4	Added missing text parts plus all strings	FT	31.03.2004
1.5	Split up string list	FT	26.04.2004

Contents

Glossary	1

1	Motivation	1

2	User Scenarios	1

3	Goals	1

4	Requirements and Dependencies	1

4.1	Requirements	1

4.2	Technical Dependencies	1

5	Competitive Analyses	2

5.1	Excel	2

5.2	OpenOffice.org 1.1	2

6	Detailed Specification	3

6.1	Data Fields	4

6.1.1	Displayed Value	5

Normal	5

Difference From	5

% Of	6

% Difference From	6

Running Total In	6

% of row	6

% of column	6

% of total	6

Index	6

6.2	Column, Row or Page Fields	6

6.2.1	Sort Order	8

Sorting by data fields	8

6.2.2	Show / Hide	8

6.2.3	Display Top X / Bottom X	9

6.2.4	Layout	9

6.2.5	Used Hierarchy	9

6.3	G11n	10

6.4	Error Conditions	10

7	Future Tasks	10

8	Notes	10

9	References	10

Glossary

Term	Description
DataPilot	A tool to present complex data structures in a easy-to-understand overview

Motivation

The DataPilot feature is lacking some field options that would increase the flexibility of the DataPilot. The most important ones are:

Fields are always sorted by their names, there is no way for the user to change this.

Result values are always simple aggregates of the source values. There's no easy way to show percentages within a column or row, or values relative to a reference item.

In addition to the usefulness of the features themselves, imported documents that make use of them will look different after refreshing the DataPilot tables.

User Scenarios

Scenario 1:

A user doesn't want to see absolute result values in a DataPilot table, but instead each item's proportion within a column or row.

Scenario 2:

A user wants to sort the items in a DataPilot table according to their result values, instead of the default alphabetic sorting.

Scenario 3:

A user imports a Microsoft Excel file that contains a pivot table with one or more of the mentioned settings applied, and wants to continue editing it.

Goals

Enhance the current DataPilot to become a competitive tools to MS Excels Pivot Tables.

Requirements and Dependencies

Requirements

none

Technical Dependencies

There is some technical overlap with the “Page Fields” feature, as a page field's selected value is also a setting of the field, but the UI for the two features is independent of each other.

Competitive Analyses

Excel

Excel has most of these settings for fields in PivotTables, too. The field options dialog in Excel is small and has sub-dialogs for the settings that are not in the dialog itself.

Figure 1 MS Excels initial Field dialog

Figure 2 MS Excel sub-dialog for Field Settings

OpenOffice.org 1.1

In current versions, the same dialog is used for data fields and other field types, with some dynamic modifications (see screenshots below).

Old dialog for data fields:

Old dialog for column or row fields:

Figure 3 Calc 1.1 Fields setting dialog Figure 4 Calc 1.1 Field setting dialog #2

Detailed Specification

The following settings can be applied to a DataPilot field, in the dialog that is shown when a field is double-clicked in the DataPilot layout dialog, or possibly a sub-dialog for it. Most of the settings are available only for some field orientations. With the different additions, it will be better to have different dialogs for data fields and other field types, instead of modifying the same dialog, as described above.

Data Fields

For data fields, settings for the displayed value are added.

Figure 5 New Data Field dialog w/ added options (Smaller and “More>>” button)

Figure 6 New Data Field #2 dialog w/ added options activated

String List of Figure 6 on page 4:

Button	~More >>	~Zusätze >>	
Section name	Displayed value	Angezeigter Wert	
Drop down name	~Type	~Typ	
Drop down entry	Normal	Standard	
Drop down entry	Difference from	Differenz von	
Drop down entry	% of	% von	
Drop down entry	% difference from	% Differenz von	
Drop down entry	Running total in	Ergebnis in	
Drop down entry	% of row	% der Zeile	
Drop down entry	% of column	% der Spalte	
Drop down entry	% of total	% des Ergebnisses	
Drop down entry	Index	Index	
Drop down name	~Base field	~Basisfeld	
Drop down name	Ba~se item	Ba~siselement	
Drop down entry	-previous item-	-vorhergehendes Element-	
Drop down entry	-next item-	-nächstes Element-	

Displayed Value

For each data field, the type of display can be selected from the choices listed below. For the types that have an “X” in the “Base Field” column, a base field has to be selected from a list of all fields of the DataPilot table. For the types that have an “X” in the “Base Item” column, a base item has to be selected from a list that contains all items of the selected base field, and the special entries “- previous -” and “- next -”.

Display type	Base Field	Base Item
Normal		
Difference From	X	X
% Of	X	X
% Difference From	X	X
Running Total In	X	
% of row		
% of column		
% of total		
Index		

The following describes how results are shown for each of the selections. All these calculations are done with the results of the selected summary function for a result cell in the DataPilot table.

Normal

Results are shown as-is (as in OpenOffice.org 1.1).

Difference From

From each result, its reference value (see below) is subtracted, and the difference is shown. Totals outside of the base field are shown as empty results.

Named Item

If a base item name is specified, the reference value for a combination of field items is the result where the item in the base field is replaced by the specified base item.

If the reference value isn't shown in the DataPilot table because of hidden details for a parent field, the difference isn't calculated and an error value is shown.

If the result for an item combination is empty, the value 0 is used for the difference, even if the summary function is undefined without values, like average or variance. The difference is shown in the result table even if the original result was empty.

The difference for item combinations containing the base item is shown as empty result.

Previous or Next

If “previous” or “next” is specified as the base item, the reference value is the result for the next visible member of the base field, in the base field's sort order. If details for one item in the base field are hidden, that item is skipped. The difference for the item with hidden details isn't calculated, not even for the item's summary, to have a consistent order of previous and next items.

Empty results are handled as for named items (see above).

The difference for the first (for “previous”) or last (for “next”) item of the base field is shown as empty result.

% Of

Each result is divided by its reference value. The reference value is determined in the same way as for “Difference from”. Totals outside of the base field are shown as empty results.

Division by zero results in an error. Otherwise, empty results are shown as 0. Results for the base item, first (for “previous”) or last (for “next”) item of the base field are shown as 1 if not empty.

% Difference From

From each result, its reference value is subtracted, and the difference divided by the reference value. The reference value is determined in the same way as for “Difference from”. Totals outside of the base field are shown as empty results.

Division by zero results in an error. Otherwise, the rules for “Difference from” apply.

Running Total In

Each result is added to the sum of the results for preceding items in the base field, in the base field's sort order, and the total sum is shown.

If details for one item in the base field are hidden, that item isn't included in calculating the sum, and results for that item are shown as error, to ensure consistency between details and subtotals for the following items.

Results are always summed, even if a different summary function was used to get each result.

Totals outside of the base field are shown as empty results.

% of row

Each result is divided by the total result for its row in the DataPilot table. If there are several data fields, the total for the result's data field is used. If there are subtotals with manually selected summary functions, still the total with the data field's summary function is used. Division by zero results in an error. Otherwise, empty results remain empty.

% of column

Same as “% of row”, but the total for the result's column is used.

% of total

Same as “% of row”, but the grand total for the result's data field is used.

Index

The row and column totals and the grand total, following the same rules as above, are used to calculate the following expression.

( original result * grand total ) / ( row total * column total )

Division by zero results in an error. Otherwise, empty results remain empty.

Column, Row or Page Fields

For column, row or page fields, more new settings are available. Some of them will be disabled or hidden for some orientations (see the descriptions of the individual settings below).

Figure 7 Enhance Data Field dialogue (smaller than predecessor and added "Options... button")

Figure 8 Data Field Options Dialogue (appears when "Options..." is pressed)

String list of Figure 7 and Figure 8 on page 7

Check box	Show it~ems without data	~Elemente ohne Daten anzeigen	Was „show elements without data“
Button	~Options...	~Optionen...	
Dialog title	Data Field Options	Datenfeld-Optionen	
Section name	Sort ~by	~Sortieren nach	
Radio box	~Ascending	~Aufsteigend	
Radio box	~Descending	A~bsteigend	
Radio box	~Manual	~Manuell	
Section name	Display options	Anzeigeoptionen	
Drop down name	~Layout	Layout	
Drop down entry	Tabular layout	Tabellarisches Layout	
Drop down entry	Outline layout with subtotals at the top	Gliederungsansicht mit Teilergebnissen oben	
Drop down entry	Outline layout with subtotals at the bottom	Gliederungsansicht mit Teilergebnissen unten	
Check box	~Empty line after each item	L~eerzeile nach jedem Element	
Section name	Show automatically	Automatisch anzeigen	
Check box	~Show	An~zeigen	
Spin box name	items	Elemente	
Drop down name	~From	~Von	
Drop down entry	Bottom	Unten	
Drop down entry	Top	Oben	
Drop down name	Us~ing field	~Feld verwenden	
Section name	Hide i~tems	Vers~teckte Elemente	
Drop down name	Hierarch~y	H~ierarchie	

Sort Order

For each column or row field, the sorting order can be selected from “manual”, “ascending” and “descending”. If “ascending” or “descending” is selected, a field with the values for sorting also has to be selected, from a list that contains the field for which the field options dialog was opened, and all data fields.

If “manual” is selected, the initial sort order is alphabetic. In later versions, it will be possible to arrange the items in the output table for “manual” sorting.

If “ascending” or “descending” is selected, and the selected field is the field for which the dialog was opened, items are sorted by their names. If a data field was selected, the items are sorted by their result values for that data field.

Sorting by data fields

When sorting by results from a data field, the sort order is determined independently for children of each item of parent fields. The sort order is based on the original results without the “Displayed Value” settings applied.

Empty results are sorted as 0, regardless of the summary function.

Errors are always sorted at the end, for ascending as well as descending.

If two results are equal, they can be sorted in any order.

If data for several data fields is shown separately (data layout field is not the innermost one), still the selected data field is always used for sorting.

Show / Hide

For each column, row or page field it is possible to select items (from the list of all contained items) which will be hidden and not included in the calculations. The default is no selection (all items shown), multiple selection is possible.

Display Top X / Bottom X

For each column or row field, if “Auto show” is enabled, the number of items to be shown has to be entered (default is 10), and a direction (from “Top” or “Bottom”) has to be selected, as well as a field with the values for sorting, from a list that contains all data fields.

If “Auto show” is active, only the selected number of items with the highest/lowest aggregated values in the selected data field will be shown and included in the calculation. This also means that “% of row” and related calculations are done in relation to the included values only. The sorting of the aggregated values to find the highest/lowest ones follows the same rules as when sorting by a data field.

The selection of included items for a field can be different for the children of each item of parent fields.

If several items have the same value, more than the selected number will be used.

If items were hidden manually (see above), the top/bottom items will be selected from the remaining items.

If top or bottom items are shown for several fields, they are determined independently for each field, using the complete results (not limited to top/bottom items from other fields).

If details for an item are hidden, the total value for that item is the complete result. Top or bottom items from the hidden children are not determined. This means that hiding details can also change the total result.

If fields are sorted by data fields (see above), the sort order will use the results from the top/bottom items only.

Layout

The layout options can only be changed for outer row fields (all row fields except the last, innermost one). For other fields, they are disabled.

The field's layout mode can be chosen from the following list:

Tabular layout

Outline layout with subtotals at the top

Outline layout with subtotals at the bottom

Tabular layout mode is the existing layout, where each item's name is on the same row as the first item from the following field. Subtotals are always shown below an item's data in this mode.

In outline layout mode, the items from the following field start in the row below an item's name, like in traditional database reports. Subtotals can be shown at the top (on the same row as the item's name), or at the bottom (below the item's data, as in tabular layout mode). When the subtotals take up more than one row (manually selected, or because there are several data fields), they are always shown below the item's data, regardless of the setting.

If “Empty line after each item” is active, an empty row is inserted in the DataPilot table after the data (including the subtotals) for each item of the field.

Used Hierarchy

If the source data dimension for a field offers several data hierarchies, it's possible to select the hierarchy that is used, from the list of available hierarchies. Currently only external implementations of the DataPilotSource service (source type “External source/interface”) can offer this. If there isn't more than one available hierarchy, the selection is disabled.

Tab Order

Tab order follow standard conventions and will not change for existing dialogues.

Key Board Shortcuts

none

G11n

none

Error Conditions

none

Future Tasks

More enhancements to the DataPilot are planned for “Q”.

Notes

<Questions will arise during the writing of the specification. State these in here.>

References

Page Fields in DataPilot: http://specs.openoffice.org/calc/compatibility/pagefields.sxw

Field Options in DataPilot

Page 10

http://specs.openoffice.org/calc/compatibility/fieldoptions.sxw

Field Options in DataPilot

Niklas Nebel

2003-12-08T15:53:35

Matthias Müller-Prove

Niklas Nebel