Grouping in DataPilot

OpenOffice.org 2.0

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

Abstract

The DataPilot will be extended to allow grouping of items. This allows to further structure the source data, leading to a more organized view on the results.

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	2004-06-30
Development	Niklas Nebel	2004-07-01
Quality Assurance	Oliver Crämer	2004-07-01
Documentation	Uwe Fischer	2004-07-02
String Review	Elizabeth Matthis	2004-07-06

Document Change History

Rev. Level	Change	Initials	Date
1.0	initial version	NN	2004-06-01
	MMP-1	MMP	2004-06-24
1.1	Added Scenario, screenshots and mockups, revised spec	FT	2004-06-29
1.2	Some corrections, added string list	NN	2004-06-29
1.3	Re-positioned string list table(s), approved spec	FT	2004-06-30
1.4	String review	EM	2004-07-06

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

6	Detailed Specification	3

6.1	Grouping of Named Items	4

6.2	Grouping of Values	4

6.3	Grouping of Date Values	5

6.3.1	Grouping by a Number of Days	6

6.3.2	Grouping by Parts of Dates	6

6.4	Renaming of Fields and Groups	7

6.5	G11n	8

6.6	Error Conditions	8

6.7	Migration	8

7	Future Tasks	9

8	Notes	9

9	References	9

Glossary

Term	Description
DataPilot	An interactive table that allows you to perform ad hoc analysis of report data in Calc
PivotTable	An interactive table that allows you to perform ad hoc analysis of report data in Excel

Motivation

If a data column that is used as a column or row field in a DataPilot table contains many different entries, the resulting table can be large and confusing, because it shows an unstructured list of all the different entries. Grouping allows to further structure the data, leading to a more organized view on the results.

When importing Excel files with PivotTables that make use of grouping, it should be possible to continue editing the DataPilot tables in Calc without losing the information about grouping.

User Scenarios

A user who receives a MS Excel spreadsheet that contains a Pivot Table that makes use of Grouping will loose formatting and layout when importing this file to Calc 1.x.

Users migrating from Excel are used to very powerful option to re-format nad re-group their information stored in Pivot Tables. In Calc some of those feature are plain non-existent and will raise the bar for easy migration.

Goals

The implementation of Grouping in DataPilot must live up to the same feature and usability set of Grouping in MS Excel Pivot Tables.

The UI should be familiar with MS Excels Grouping but is allowed to be changed in case of better usability.

Requirements and Dependencies

Requirements

none

Technical Dependencies

none

Competitive Analyses

Excel

Excel has the ability to group/un-group data series based on various parameters within a Pivot Table.

In a Pivot Table, one can group the items in a Row or Column field. For example, items in a date field can be grouped by month, and items in a number field can be grouped by tens (see also tables below)

To group the items in a Date field
Right-click the Date field button	
Choose “Data - Group and Show Detail Group”	
In the Grouping dialog box, select one or more options from the 'By' list. To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes Click OK	

Grouping Dates by Week
To group the items in a Date field by week: Right-click the Date field button. Choose “Group and Show Detail Group” In the Grouping dialog box, select Days from the 'By' list. For 'Number of days', select 7 The week range is determined by the date in the 'Starting at' box, so adjust this if necessary. In the example at right, December 29, 2003 (a Monday), was entered as the starting date. Click OK	

Problems when Grouping Data
If you try to group a date or field, you may see an error message that says, "Cannot group that selection." This problem occurs when the field contains records with a blank date/number field, or text in a date/number field. Fill in the date/number, and the grouping should work correctly.	

Detailed Specification

The already existing slots "Insert Group" (called “Group” in the main menu) and "Ungroup" will be re-used for this new grouping feature.

Executing the new functions requires that the cell cursor is within a DataPilot table. When “Group” is executed, the result depends on the selected cells:

The “Grouping of Values” dialog is shown if

a single cell is selected that contains a number (not formatted as date), and is part of a normal, non-grouped field, - or -

the selection is part of a field that is grouped by values.

The “Grouping of Date Values” dialog is shown if

a single cell is selected that contains a date/time (number formatted as date or time), and is part of a normal, non-grouped field, - or -

the selection is part of a field that is grouped by date values.

Otherwise, the selected items are grouped as named items, if possible (see below).

Executing “Ungroup” only has an effect if the selection is part of a field that is grouped in any way. The result depends on the type of grouping and is described in the sections below.

Grouping of Named Items

When the “Group” menu function is executed for a selection of items from a field that isn't used for any groups, a new field (group field) is added to the DataPilot table in addition to the existing one.

The initial name of the group field is the name of the original field, followed by a number (starting with 2), so the name doesn't conflict with an existing field. The group field is initially shown at the same orientation (column or row) as the original field, and before the original field.

The group field initially contains a group with the name “Group1”, that contains the selected items from the original field. This means all data for the selected items is shown for the “Group1” item of the group field. If the original field is shown next to the group field, details for a group item are only the grouped items from the original field, even if “show items without data” is active. In addition to the groups that were explicitly added, a group field always contains an automatic group for each item from the original field that is not in any group. Each of these automatic groups has the name of the corresponding item from the original field and contains only that one item.

When the “Group” menu function is executed for a selection of items from a field for which a group field already exists, a group for the selected items is added to the group field. If the items were already part of groups, they are first removed from those groups. Groups that become empty this way are removed. The name of the new group is “Group” followed by a number, which is chosen so the name doesn't already exist.

When the “Ungroup” menu function is executed for a selection of items (groups) in a group field, the selected groups are removed. The items from the original field that were contained in the groups are shown as automatic groups again. If all visible non-automatic groups in the group field were removed by the “Ungroup” function, the group field itself is completely removed.

Grouping can be nested, that is, a group field can be used as the original field for another group field.

A group field can also be used alone (without the original field), or in a different orientation from the original field, in the DataPilot table.

Grouping of Values

When the “Group” menu function is executed for values, the following dialog is shown:

Figure 1 New Grouping of values Dialogue in Calc 2.0

String list

Item	English	German	Comments
Dialog title	Grouping	Gruppierung	same for both dialogs
Section name	Start	Anfang	same for both dialogs
Section name	End	Ende	same for both dialogs
Radio button	~Automatically	~Automatisch	same for both dialogs
Radio button	~Manually at	~Manuell bei	same for both dialogs; Translation! This preposition is followed by a number in a spin box.
Radio button	A~utomatically	A~utomatisch	same for both dialogs
Radio button	Ma~nually at	Ma~nuell bei	same for both dialogs; Translation! This preposition is followed by a number in a spin box.
Section name	~Group by	~Gruppieren nach	Different than other products because the solely prep “by” is vague and difficult to translate w/o context. DR: Fixed line with a mnemonic, because followed by an unlabeled numeric field

If the selection is part of a field that is already grouped by values, the existing settings are shown in the dialog.

If the start or end value is selected as “automatic”, the smallest or largest value from the source data is used.

The field to which the selection belongs is then shown as groups of values, according to the settings from the dialog. The individual values are no longer shown, the number of available fields doesn't change.

The grouped field will contain the following items:

An item named “<x”, where x is the selected start value. This contains the data for all values smaller than the start value (in the case of an automatic start value, it will always be empty).

A number of value groups. The first group contains data for all values from the start value (including) up to the start value plus the step value (not including). For each following group, the range is shifted by the step value. The name of these groups depends on the type of source data:

If the source data contains only integer numbers, and the start value, the end value, and the step value are also integer numbers, the group name is “a-b”, where a is the group's start value, and b is one less than the group's end value (start value plus step value for the first group).

Otherwise, the group name is “a-b”, where a is the group's start value, and b is the group's end value itself.

The last of the value groups is different from the description above: Its upper bound is always the selected end value, and that value is included. The last group's name is always “a-b”, where a is the group's start value and b is the selected end value. If the difference between the selected start value and the selected end value is a multiple of the step value, the last group, which would normally not include the end value, is modified to include the end value. Especially in the case of integer numbers, this means that the last group may contain more values than the other groups.

An item named “>x”, where x is the selected end value. This contains the data for all values greater than the end value (in the case of an automatic end value, it will always be empty).

All items from the source data which aren't numbers.

When the “Ungroup” menu function is executed for a field that is grouped by values, the grouping is removed and the field's original values are shown again.

Grouping of Date Values

When the “Group” menu function is executed for dates, the following dialog is shown:

Figure 2 New Grouping of Units Dialogue in Calc 2.0

String list

Item	English	German	Comments
Section name	Group by	Gruppieren nach	DR: In difference to the first dialog this entry does not have a mnemonic
Radio button	Number of ~days	Anzahl von ~Tagen	DR: Context is “Group by... Number of days” (“Gruppieren nach... Anzahl von Tagen”)
Radio button	~Intervals	~Intervallen	DR: Context is “Group by... intervals” (“Gruppieren nach... Intervallen”)
List box entry (also default field name)	Years	Jahre	
List box entry (also default field name)	Quarters	Quartale	
List box entry (also default field name)	Months	Monate	
List box entry (also default field name)	Days	Tage	
List box entry (also default field name)	Hours	Stunden	
List box entry (also default field name)	Minutes	Minuten	
List box entry (also default field name)	Seconds	Sekunden	
Default item name	Group	Gruppe	always followed by a number, i.e. Group1, Group2, ...
Error message	You cannot change this part of the DataPilot table.	Sie können diesen Teil der Datenpilot-Tabelle nicht ändern.	

If “Number of days” is selected, a number can be entered (at least 1).

Otherwise, one or several of the date parts (units) can be selected. The following date parts are available (their names are translated as UI strings): “Years”, “Quarters”, “Months”, “Days”, “Hours”, “Minutes” and “Seconds”.

If the selection is part of a field that is already grouped by dates, the existing settings are shown in the dialog.

If the start or end date is selected as “automatic”, the smallest date, or the date following the largest date from the source data is used.

Grouping by a Number of Days

If a “Number of days” is selected, the field is shown as groups of dates, similar to the groups of values above, but with the following differences:

The values are formatted as dates (using the configured “Locale setting” language) instead of numbers to get the item names.

The name of a value group is always “a - b” (with extra spaces), where b is one less than the group's end value.

If the difference between the start value and the end value is a multiple of the step value (number of days), the end value is included in the last item (named “>x”), not the last value group. This is the only case where a value within a date (with a time different from 0:00) is grouped differently from the start of that date.

Figure 3 New Grouping by Number of Days Dialogue in Calc 2.0

Grouping by Parts of Dates

When not grouping by a number of days, the date field is replaced by a number of fields each containing one part of the date values. The lowest-value of the selected parts is shown in the original field, for the other selected parts, new fields are added. The initial name of each new field is the name of the date part followed by a number if necessary to make it unique.

Each part contains the following items (groups). All item names depend on the configured “Locale setting” language.

“Years” contains the years of the dates from the minimum to the maximum value. The names are the full (4-digit) numbers.

“Quarters” contains four items for the 4 quarters of a year. The names are taken from the short representation of quarters.

“Months” contains 12 items for the different months in a year. The names are the abbreviated month names.

“Days” contains 366 items for the possible days in a year. The names are determined using the date format for “Day and Month” display (DATE_SYS_DDMMM).

“Hours” contains 24 items for the hours in a day. The names are the hours (0 to 23) formatted as two-digit numbers.

“Minutes” contains 60 items for the minutes in an hour. Each item's name is the time separator character, followed by the two-digit value (0 to 59).

“Seconds” contains 60 items for the seconds in a minute. Each item's name is the time separator character, followed by the two-digit value (0 to 59).

In addition, all parts contain an entry “<x”, where x is the selected start date, and an entry “>x”, where x is the selected end date. All non-numeric items from the source data are also included in each part.

The fields behave similarly to group fields for named items. Each group contains the results for dates where the particular part of the date has the group value.

If two or more of “Quarters”, “Months” or “Days” are used together, details for each group contain only the corresponding items, even if “show items without data” is active. The other parts are independent of each other (“Days” contains all 366 items, even in the details for a non-leap year).

As an exception to the rule above, details for the groups “<x” and “>x”, and for non-numeric source data, contain only the same item from the other field.

When the “Ungroup” menu function is executed for a field that is grouped by dates, the grouping is removed, including all additional fields created for the date parts, and the original field's unmodified values are shown again.

Renaming of Fields and Groups

The fields that were added by grouping (group fields for names items, or date group fields except the one with the lowest-value part) can be renamed.

Renaming is done by editing the content of the header cells.

Note: The fields that have the names of source columns cannot be renamed. Later (not for “Q”) it will be possible to specify a different layout name for such a field.

Groups of named items can be renamed the same way.

If the edited cell is not the header of a field or item that can be renamed, or if the name is invalid for the field or item, an error message is shown:

If the cell is not a header that can be renamed, the message is “You cannot change this part of the DataPilot table.” (see string list below).

If the name is invalid, the existing message “Invalid name.” is shown.

Tab Order

Tab order conforms to standard tab order in dialogues.

Key Board Shortcuts

n/a

G11n

Date grouping always uses the Gregorian calendar. The date values are shown according to existing locale data.

Error Conditions

N/a

Migration

N/a

Future Tasks

N/a

Notes

MMP-1 Issue. For the Data menu, can you please rename the menu item ”Outline” into ”Group and Outline”. The use of the MS term eases in this case a conflict with Calc's Format menu that provides grouping commands as well [Format Menu in Calc]

References

Format Menu in Calc	http://specs.openoffice.org/calc/menus/FormatMenu.sxw

Grouping in DataPilot

Page 2

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

Grouping in DataPilot

Niklas Nebel

2004-03-18T14:56:54

Matthias Müller-Prove

Niklas Nebel