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.
Created attachment 99977 [details] bug description
(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.
Platform -> ALL Version: 4.2.4.2 (The earliest version I tested and confirmed) Importance: High Major
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.
Another case with xlsx: When data pilot contains Report filter, then open this file is broken. See attachment.
Created attachment 100002 [details] data pilot contains Report filter, then open this file is broken
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
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.
(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.
(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?
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 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.
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!
Created attachment 103782 [details] test ods file with pivot table
(In reply to comment #13) > -> Pivot appears OK now! Refresh the pivot table and see what happens.
Still the same in 4.3.0.4, data source is incorrect
(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?
(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.
(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
(This is an automated message.) Setting priority to highest as this is a MAB. This is part of an effort to make the importance of MAB reflected in priority too.
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
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.
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.
Reproduced with: Version: 7.2.0.0.beta1 / LibreOffice Community Build ID: c6974f7afec4cd5195617ae48c6ef9aacfe85ddd CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded In both ODS or XLSX, removing the pivot table data source brings up a warning message, which is good. The difference between the two formats, after removing pivot table source, then saving and reopening: - In ODS: still shows the contents of the pivot table, but refreshing it makes data disappear. - In XLSX: contents of pivot table vanished directly when opening the file.