Bug Hunting Session
Bug 79332 - self-contained pivot table. Implement pivot cache.
Summary: self-contained pivot table. Implement pivot cache.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86 (IA32) All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Pivot-Table
  Show dependency treegraph
 
Reported: 2014-05-27 18:56 UTC by raal
Modified: 2016-10-21 11:02 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
file with pivot table (14.29 KB, application/vnd.openxmlformats-officedocument.spreadsheetxml.sheet)
2014-05-27 18:56 UTC, raal
Details
bug description (330.57 KB, application/vnd.oasis.opendocument.text)
2014-05-27 18:58 UTC, raal
Details
test file created in MSO 2010 (11.01 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-05-28 02:21 UTC, Kevin Suo
Details
data pilot contains Report filter, then open this file is broken (11.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml)
2014-05-28 06:22 UTC, raal
Details
xlsx file with pivot table (10.98 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-08-01 03:26 UTC, Kevin Suo
Details
test ods file with pivot table (19.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-08-01 03:37 UTC, Kevin Suo
Details
test ods file with pivot table (16.30 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-08-01 03:42 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2014-05-27 18:56:37 UTC
Created attachment 99976 [details]
file with pivot table

How to reproduce

Create pivot table on new sheet
Delete Sheet1 (sheet with data).
Save file.
	You can filter in pivot table, data are in the pivot cache.
Close file.
Open File
Pivot table broken

	The same problem in xlsx file with self-contained pivot table.
Comment 1 raal 2014-05-27 18:58:20 UTC
Created attachment 99977 [details]
bug description
Comment 2 Kevin Suo 2014-05-28 02:18:04 UTC
(In reply to comment #0)

Confirmed in windows xp sp3, libreoffice 4.3.0 beta1 and 4.2.4.2. Changing the version accordingly.

For your test file, libreoffice is using "$'Pivot Table_Sheet1_1'.$A$1:$B$11" as the data source for pivot table, after "Sheet1" was deleted. This is totally wrong, libreoffice should use the cached data as data source.

Just for information:
In MSO 2010, when I create a pivot table with "Sheet1.$A$1:$B$3" as data source, and then delete "Sheet1", MSO 2010 then uses "$DPCache_Sheet1.$A$1:$B$3" as the data source of pivot table.
Comment 3 Kevin Suo 2014-05-28 02:20:12 UTC
Platform -> ALL
Version: 4.2.4.2 (The earliest version I tested and confirmed)
Importance: High Major
Comment 4 Kevin Suo 2014-05-28 02:21:50 UTC
Created attachment 99992 [details]
test file created in MSO 2010

As you can see, MSO is using "$DPCache_Sheet1.$A$1:$B$3" as data source. This will not broken the pivot table when refresh.
Comment 5 raal 2014-05-28 06:22:01 UTC
Another case with xlsx:
When data pilot contains Report filter, then open this file is broken. See attachment.
Comment 6 raal 2014-05-28 06:22:21 UTC
Created attachment 100002 [details]
data pilot contains Report filter, then open this file is broken
Comment 7 ign_christian 2014-07-06 05:24:49 UTC
Reproducible in LO 4.3.0.2, 4.2.5.2, 4.1.6.2, 4.0.6.2, 3.6.7.2, 3.5.7.2 (Ubuntu 12.04 x86)

Also in AOO 4.1.0, perhaps inherited from OOO

Seems that filesave in XLSX is different problem (pivot table totally lost but values stick in cells), XLS also have different problem
Comment 8 Kohei Yoshida 2014-07-31 19:52:59 UTC
Supporting this requires import of pivot cache, which was never implemented going back to the OOo time.  This is not a regression, not even a bug since it was never supported to begin with.
Comment 9 Kevin Suo 2014-08-01 00:35:07 UTC
(In reply to comment #8)
> This is not a regression, not even a bug since it was never supported to begin with.

This is a serious issue because it causes data loss:
Assume sheet1 has data, sheet2 has pivot. A user may delete sheet1 after he has draw the pivot, and when he looks at the pivot at this moment it has numbers. But when reopen he lost all the data! Before lost the user was not aware of it.

If this is a feature request and could not be fixed in time, I think we should: make the pivot broken as soon as the user deletes sheet1. This will make the user aware that he may lost data when delete, so he can UNDO to reserve it.
Comment 10 ign_christian 2014-08-01 03:13:36 UTC
(In reply to comment #9)
> If this is a feature request and could not be fixed in time, I think we
> should: make the pivot broken as soon as the user deletes sheet1. This will
> make the user aware that he may lost data when delete, so he can UNDO to
> reserve it.
How about making this enhancement request regardless there should be a "warning" after deletion?
Comment 11 Kevin Suo 2014-08-01 03:26:27 UTC
Created attachment 103779 [details]
xlsx file with pivot table

(In reply to comment #8)
> Supporting this requires import of pivot cache, which was never implemented
> going back to the OOo time.

Wait...

Please see the attached xlsx file, it has a pivot table, the orinal data sheet (sheet1) was deleted. LibreOffice can import this pivot successfully!
Comment 12 Kevin Suo 2014-08-01 03:29:31 UTC
Comment on attachment 100002 [details]
data pilot contains Report filter, then open this file is broken

(Reply to Comment 6):
> data pilot contains Report filter, then open this file is broken

This is another issue, please report another bug on this one.
Comment 13 Kevin Suo 2014-08-01 03:37:32 UTC
Created attachment 103781 [details]
test ods file with pivot table

Wait again...WORKSFORME in 4.3.0.4!
Seems it's fixed somewhere.

Steps to observe:
1. Delete the sheet "data" in the attached ods file;
2. Save and reopen.

-> Pivot appears OK now!
Comment 14 Kevin Suo 2014-08-01 03:42:15 UTC
Created attachment 103782 [details]
test ods file with pivot table
Comment 15 Kohei Yoshida 2014-08-01 03:53:16 UTC
(In reply to comment #13)
> -> Pivot appears OK now!

Refresh the pivot table and see what happens.
Comment 16 ign_christian 2014-08-01 04:00:07 UTC
Still the same in 4.3.0.4, data source is incorrect
Comment 17 Kevin Suo 2014-08-01 04:04:31 UTC
(In reply to comment #16)
> Still the same in 4.3.0.4, data source is incorrect

Refreshes the pivot in ods file (attachment 103782 [details], delete the data sheet) loses data (the datasource is "$pivot.$A$1:$C$3")
Refreshes the pivot in xlsx file (attachment 103779 [details]) works fine (the data source is "$DPCache_Data.$A$1:$C$3")

So, it's an export issue, rather an import issue?
Comment 18 ign_christian 2014-08-01 04:19:46 UTC
(In reply to comment #17)
> So, it's an export issue, rather an import issue?
Yes I think it's an export/filesave issue as described in bug description.

But your attached file in comment 11, if you created that using MSO, looks ok since it shows $DPCache_Data.$A$1:$C$3 in source range. Refreshing & playing with the filter also not destroying the data. So no problem if importing from MSO created file.
Comment 19 raal 2014-08-01 16:01:21 UTC
(In reply to comment #12)
> Comment on attachment 100002 [details]
> data pilot contains Report filter, then open this file is broken
> 
> (Reply to Comment 6):
> > data pilot contains Report filter, then open this file is broken
> 
> This is another issue, please report another bug on this one.

I created new bug 81999
Comment 20 Björn Michaelsen 2014-08-21 12:17:29 UTC Comment hidden (obsolete)
Comment 21 tommy27 2014-12-08 10:33:15 UTC
please retest with 4.3.x or 4.4.x versions.
if issue persists, please move it to mab4.3 list since 4.2.x is EOL
Comment 22 Robinson Tryon (qubit) 2014-12-08 14:03:18 UTC
What's the plan with this bug?

Kohei says (comment #8):
> import of pivot cache...never implemented.
> This is not a regression...since
> it was never supported

Kevin Suo (from comment #9):
> This is a serious issue because it causes data loss...
> we should: make the pivot broken as soon as the user deletes sheet1.
> This will make the user aware that he may lost data

ign_christian (comment #10):
> How about...enhancement request: there should be a
> "warning" after deletion?

It sounds like there's a great potential for data loss, so I agree that a warning is desirable.

Perhaps there are two bugs here:
1) (MAB due to data-loss) Add warning about data loss when creating pivot table and deleting data source
2) (lower-priority enhancement) Implement pivot cache (and remove previous warning about data loss)

If that split sounds reasonable, please create a new bug for the enhancement and update the summary of this bug to clarify the task.
Comment 23 raal 2014-12-08 17:36:32 UTC
According to comment 22 setting as enhancement.

Interoperability  enhancement - without this feature is excel self-contained  pivot table broken (https://exceljet.net/lessons/how-to-make-a-self-contained-pivot-table ). These self-contained pivot tables are used because of smaller size of file.