Issue 93495 - DataPilot is disturbed by user defined number format
Summary: DataPilot is disturbed by user defined number format
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOO300m2
Hardware: All All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2008-09-04 20:06 UTC by Stefan Weigel
Modified: 2013-01-29 21:39 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

spreadsheet document mentioned above (12.02 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-09-04 20:08 UTC, Stefan Weigel
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Stefan Weigel 2008-09-04 20:06:27 UTC
Data pilot screws up data, when some user defined number format is used for
displaying dates.

For example see the attached spreadsheet:

Test Case A (left) is fine. The DataPilot is basing upon a list of dates and
values. The cells, that contain the dates, have been set to the number format:

Test Case B (right) shows the defect. The only difference in the list is, that
the cells, that contain the dates, have been set to the user defined number
format: DDD, MM D

The categories of the DataPilot´s result on the right (B) are no longer dates,
but text. The data type has been changed from date to text. The big problem
becomes obvious, when you try to group the DataPilots by date:

(1) Select cell A22
(2) Press F12
(3) You are able to group by date (days, months, quarters, years, ...)

(1) Select cell D22
(2) Press F12
(3) You are *not* able to group by date, because there are no dates but texts.
Comment 1 Stefan Weigel 2008-09-04 20:08:03 UTC
Created attachment 56247 [details]
spreadsheet document mentioned above
Comment 2 Mechtilde 2008-09-04 20:30:27 UTC
The Problem I see is, that A22 is defined as date and D22 as number, default
Comment 3 Stefan Weigel 2008-09-05 08:44:16 UTC
  | The Problem I see is, that A22 is defined as
  | date and D22 as number, default

This is a result of the defect.

The problem is, that DataPilot takes the value from D6, wich is
  a date,
  represented as the number 39448
  and displayed as "Tue, Jan 1" (number format DDD, MM D)
and transforms this erroneously into a dumb text "Tue, Jan 1" in D22.

The user can not control this behavior.

As a consequence, sorting and grouping of the DataPilot´s result is impossible. 
Comment 4 kyoshida 2008-09-29 23:00:38 UTC
This problem is caused by two things.

1) DataPilot relies only on Calc's automatic format recognition given a date
string.  So, if Calc cannot recognize the given string as a date, the date value
is lost at that point.  IOW, this problem occurs for the same reason Calc
inserts it as a string when you type "Tue, Jan 1" directly into a cell.

2) When DataPilot's results are computed, column and row's member values only
carry string data.  IOW, even if a member is a number or a date, the result set
only stores a textural representation of that data.  So, by the time the
DataPilot table is constructed, we no longer have the numerical value of the
number/date member.

I've already tried to fix it by fixing Calc's number format parser, but that
parser code is such a mess, that modifying that code would likely introduce

The right fix IMO is to preserve the numerical value of column/row members
during result calculation.  That will probably require extending or replacing struct so that numerical values are preserved
for the table output.  Either way, fixing this will be non-trivial.
Comment 5 kyoshida 2008-09-29 23:26:23 UTC
note to self:

ScDPResultMember::FillMemberResults(...) stores individual members of each
dimension into sheet.MemberResult.

ScDPOutput::Output() constructs the datapilot output table using the set of
sheet.MemberResult instances.

These two places need to be modified after the API change.