Page Fields in DataPilot

OpenOffice.org 2.0

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

Abstract

The DataPilot will be extended to allow the use of page fields.

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	28.01.2004
Development	Niklas Nebel	2004-02-03
Quality Assurance	Oliver Crämer	2004-02-02
Documentation	Uwe Fischer	2004-02-03
String Review	Elizabeth Matthis	2004-02-27

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial revision	NN	2003-11-05
1.1	Falko's comments	NN	2004-01-23
1.2	Addition to Future Tasks section	FT	2004-01-28

Contents

Glossary	1

1	Motivation	1

2	User Scenarios	1

3	Goals	1

4	Requirements and Dependencies	2

4.1	Requirements	2

4.2	Technical Dependencies	2

5	Competitive Analyses	2

6	Detailed Specification	2

6.1	Error Conditions	4

7	Future Tasks	4

8	Notes	4

9	References	4

Glossary

Term	Description
DataPilot	The DataPilot allows you to combine, compare and analyze large amounts of data. You can view different summaries of the source data, and you can display the details for areas of interest and create reports.
Page Field	Page fields are laid out at the top of the table and allow quick selection of individual items.
Column/Row Field	Each item of a column field is shown in a column in the resulting table. Each item of a row field is shown in a row in the resulting table.

Motivation

Our DataPilot feature currently doesn't allow the use of page fields. To some extent they can be simulated using filter conditions, but this is not as easy to use as directly selecting from a page field item list, and it's limited to only 3 conditions.

Imported Excel documents that use page fields look different after refreshing the DataPilot tables, and it's not quite obvious to a user how to get the same results as with the page fields.

User Scenarios

Scenario 1:

A user is in the need to present company figures that are cumulated in a DataPilot table for better understanding. To give more specific drill-down results on certain fields the user must re-create the DataPilot table from scratch with different fields selected or he must insert filter conditions which are very limited in usage.

When introducing page fields in DataPilot tables the user will be able to use these fields to change the company figures outlook by simply choosing a different value from trese fields with the DataPilot table.

Scenario 2:

A user imports an Excel spreadsheet with an extensive PivotTable (MS Excel's equivalent to OpenOffice.org's DataPilot tables) that contains page fields.

He expects to see, use and edit these fields after importing the spreadshhet.

This scenario will be given after this feature is implemented.

Goals

Make page fields available in the DataPilot and to be MS Excel (filter) compliant.

Requirements and Dependencies

Requirements

It must be possible to use page fields in the DataPilot, and import/export them from Microsoft Excel files.

Technical Dependencies

none

Competitive Analyses

Microsoft Excel allows page fields as part of pivot tables. They are laid out at the top of the table and allow quick selection of individual items.

Detailed Specification

Fields can be used as page fields the same way they could already be used as column or row fields. A field that is used as page field in a DataPilot table can also be used as data item in the same table, but not as column or row field.

The DataPilot dialog's “Layout” section will have a new orientation area “Page” in addition to the existing “Column”, “Row” and “Data” areas, to which fields can be dragged. This new area will house 2 more entries (10) opposed to the existing fields that can only house 8.

See also Future Tasks in this matter.

Figure 1 PageFields in new DataPilot MockUp (shown with the current cursor layout)

Page fields in a DataPilot table are laid out below the "Filter" button cell if there is one, or in its place otherwise. For each field, the first column contains the field name (label), and the second column contains the selected value for the field. The field label is displayed in "button" look (see figure 2) and can be dragged to rearrange the field. The field value has a drop-down arrow, like the one used for AutoFilter.

Figure 2 DataPilot table with new "PageField" button active DataPilot fields can be dragged to the area below the “Filter” button cell to use them as page fields, the same way as fields are dragged to the column or row field area in the table. If there is no “Filter” button cell and no existing page fields, dragging a field to the cells directly above the DataPilot table will use it as a page field.

The same mouse cursor as for row fields is used for dragging into the "Page fields" area (see dialog screenshot).

If the number of page fields in an existing DataPilot table is changed, the table's start position on the sheet is moved up or down, so the area with the column/row fields and data (with the thick border) starts at the same position. If this would move the table before the first row, the table starts at the first row.

If the drop-down button for a page field is pressed, a list box is shown with all items from the field, and an extra entry “- all -” at the top. If an item is selected, its name is shown next to the field label, and the DataPilot calculations include only that item. If “- all -” is selected, that text is shown next to the field label, and all items are included in the calculations.

If a filter condition was specified for the DataPilot, it is evaluated before each field's items are determined, so the list box will only show the items that match the filter criteria. The selection of an item for one page field doesn't affect the items that are shown in the list box for another page field.

API

The enum com.sun.star.sheet.DataPilotFieldOrientation, which is used for the Orientation member of the com.sun.star.sheet.DataPilotField service, already has a “PAGE” value, so the API definition doesn't have to be changed. A new property for the selected value of a page field has to be added to the DataPilotField service.

File Format

For the table:orientation attribute in the table:data-pilot-field element, “page” is already a possible value in the current file format specification. The page field label cells will be included in the table:buttons attribute of the table:data-pilot-table element, in accordance with the current specification. The selected value of a page field has to be stored as a new attribute in the table:data-pilot-field element.

When a file with page fields in a DataPilot table is loaded into an older version of OpenOffice.org, the old version will treat the page fields like hidden fields when refreshing the table.

String list

Item	English	German	Comments
Area label	~Page Fields	~Seitenfelder	New entry
Area label	~Row Fields	~Zeilenfelder	Replaces ~Row
Area label	~Column Fields	~Spaltenfelder	Replaces ~Column
Area label	~Data Fields	~Datenfelder	Replaces ~Data
List box entry	- all -	- alle -	

Tab Order

The tab order position for the new “Page” area in the dialog will be before the “Column” area.

Key Board Shortcuts

There are no new keyboard shortcuts.

Error Conditions

No new error conditions are introduced.

Future Tasks

It may be desirable to have new mouse cursors (different for row or page fields).

More enhancements to the DataPilot are planned for “Q”, for example field options (see References).

Since the core of Calc does not limit the amount of field entries to 8 (or 10 for new Page fields area) the UI has to be enhanced to house as many entries as the core can provide.

Notes

References

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

Page Fields in DataPilot

Page 4

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

Page Fields in DataPilot

Niklas Nebel

2003-11-04T20:32:48

Matthias Müller-Prove