Apache OpenOffice (AOO) Bugzilla – Issue 93495
DataPilot is disturbed by user defined number format
Last modified: 2013-01-29 21:39:17 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: MMM D, YYY 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.
Created attachment 56247 [details] spreadsheet document mentioned above
The Problem I see is, that A22 is defined as date and D22 as number, default
| 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.
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 regressions. The right fix IMO is to preserve the numerical value of column/row members during result calculation. That will probably require extending or replacing com.sun.star.sheet.MemberResult struct so that numerical values are preserved for the table output. Either way, fixing this will be non-trivial.
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.