Bug 41645 - Pivot table formatting/sorting disappears on refresh (with no undo)
Summary: Pivot table formatting/sorting disappears on refresh (with no undo)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 104174 126557 133520 145098 157248 (view as bug list)
Depends on:
Blocks: Conditional-Formatting Pivot-Table
  Show dependency treegraph
 
Reported: 2011-10-10 04:05 UTC by Winfried Donkers
Modified: 2024-07-22 13:12 UTC (History)
13 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Winfried Donkers 2011-10-10 04:05:18 UTC
When using conditional formatting on cells in pivot table, calling refresh (via right mouse button contectmenu-refresh or via menu-data-pivot table-refresh) to update the pivot table (e.g. after changes in the data tables) causes the conditional formatting to disappear.
Comment 1 Björn Michaelsen 2011-12-23 12:39:44 UTC Comment hidden (obsolete)
Comment 2 Winfried Donkers 2011-12-24 01:21:13 UTC
bug still present on master (3.6.0alpha+, openSUSE 12.1)
Comment 3 g_bee 2012-05-25 20:50:55 UTC
Ok, now that I am registered with Bugzilla, I am confirming that this bug persists in 3.5.   Hoping it gets fixed soon.
Comment 4 m_a_riosv 2012-07-31 23:45:35 UTC
I think is not a bug, Pivot tables always overwrite all the destination area, values and formats.
Maybe can be set as enhancement.
Comment 5 QA Administrators 2015-01-05 17:52:19 UTC Comment hidden (obsolete)
Comment 6 Winfried Donkers 2015-01-06 11:09:00 UTC
Problem still persists in version 4.3.5.2
My current master is broken, so cant test there.

WRT comment #4 I think that refreshing pivot tables should just overwrite the values, not the formatting.
Comment 7 Gordon 2015-02-02 18:17:39 UTC
Using current version 4.4.0.3 and working with Calc.

Pivot tables are formatted using one set of format titles in the Styles library
 
If using several pivots against the same set of data, such as to create an order, invoice, bill of lading, summary, etc., it would be useful to allow each table to use its own formatting. 
 
Suggestion: When editing a pivot, have the option to retain formatting that has been applied or overwrite formatting on refresh. (As is permitted in Excel).

This is my first post, so please forgive if submitted incorrectly.
Comment 8 QA Administrators 2016-02-21 08:37:36 UTC Comment hidden (obsolete)
Comment 9 Winfried Donkers 2016-02-22 08:53:01 UTC
Problem still there in version 5.0.4 (Windows 7).
Comment 10 QA Administrators 2017-03-06 15:55:27 UTC Comment hidden (obsolete)
Comment 11 Winfried Donkers 2017-03-07 11:08:45 UTC
Problem still there in version 5.2.5 (Windows 7).
Comment 12 QA Administrators 2019-04-04 03:04:35 UTC Comment hidden (obsolete)
Comment 13 Winfried Donkers 2019-04-04 06:01:03 UTC
Problem still there in version 6.1.5.2 (Windows 10).
Comment 14 Sven Neuz (SERPENTEQ) 2020-04-14 10:13:47 UTC
I can confirm this bug for 6.3.2.2 x64 on Windows 10.

Also sorting gets lost on refresh.
Comment 15 armand 2020-04-22 21:38:13 UTC
Same here 6.3.2.2
Comment 16 Stéphane Guillou (stragu) 2021-12-08 13:44:26 UTC
Reproduced in:

Version: 7.3.0.0.beta1 / LibreOffice Community
Build ID: 436f14c25ec1847646b953cf13d0db4f7ca3be57
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Generalising the summary to all formatting, as many bugs refer to the same issue and should be marked as duplicates of this one.
Comment 17 Stéphane Guillou (stragu) 2021-12-08 13:44:49 UTC
affects all OSs
Comment 18 Stéphane Guillou (stragu) 2021-12-08 13:45:33 UTC
*** Bug 126557 has been marked as a duplicate of this bug. ***
Comment 19 Stéphane Guillou (stragu) 2021-12-08 13:46:33 UTC
*** Bug 104174 has been marked as a duplicate of this bug. ***
Comment 20 Stéphane Guillou (stragu) 2021-12-08 13:49:53 UTC
Issue is inherited from OOo, as mentioned in bug 126557
Comment 21 Stéphane Guillou (stragu) 2021-12-08 13:50:55 UTC
*** Bug 133520 has been marked as a duplicate of this bug. ***
Comment 22 Stéphane Guillou (stragu) 2021-12-08 13:54:20 UTC
Possibly related to bug 122472
Comment 23 Phil 2021-12-22 09:20:35 UTC
It has been marked that this bug is duplicate of my bug report 126557

However,

1/ I clearly state in bug report 126557, that there is no undo, aka a bug.

2/ I give a presumable cause, aka the refresh calls some kind of new constructor and not as it must an update/refresh one, prevailing the undo action!

I consider no undo as severe.
Comment 24 raal 2022-02-15 21:51:13 UTC
*** Bug 145098 has been marked as a duplicate of this bug. ***
Comment 25 VarathaRamanujam.C 2022-09-11 10:16:14 UTC
NOT REPRODUCED

When using conditional formatting on cells in pivot table, calling refresh (via right mouse button contectmenu-refresh or via menu-data-pivot table-refresh) to update the pivot table (e.g. after changes in the data tables) causes the conditional formatting to disappear.


bug not produced during by clicking the refresh button. 

ENVIRONMENT:
libre office 7.4.0.0
Distributor ID:	Linuxmint
Description:	Linux Mint 20.3
Release:	20.3
Codename:	una
Comment 26 Justin L 2022-10-18 18:26:26 UTC
Duplicate bug 104174 provided TESTING.ods with attachment 129016 [details].

Using that, I can repro in 7.5+, including the inability to undo.

Can someone provide a reason why formatting WOULD be replaced? As Gordon noted in comment 7, it is applying the predefined "Pivot Table" styles. But in what situation would be it be necessary to reapply?

Oh, I see. Pivot tables can grown or shrink on a refresh, covering a different range of cells. So as Gordon astutely pointed out, the only proper "solution" is to give the user a choice.
Comment 27 Justin L 2022-10-18 18:34:08 UTC
One could argue that the mere presence of direct formatting in the pivot table should be enough to prevent a reformatting - at least as long as the table doesn't change in size. (I would say that automatic formatting should be guaranteed when the size changes - removed completely when shrunk, and appropriately applied when increased.)
Comment 28 Justin L 2022-10-20 16:48:49 UTC
The recalc is initiated by an event call to ScDBFunc::RecalcPivotTable() which calls RefreshPivotTables (with bApi = false).

//Reload the referenced pivot cache, and refresh all pivot tables
// that reference the cache.
RefreshPivotTables has a note
        // This action is intentionally not undoable since it modifies cache.

ScDBDocFunc::UpdatePivotTable compares the old and new size, verifying that it won't overwrite non-pivot-table content. It calls rDPObj.Output(aNewOut.aStart) which right away calls DeleteArea with InsertDeleteFlags::ALL.

I suppose we want to keep all except InsertDeleteFlags::CONTENTS and FORMULA?

Proposed fix at gerrit.libreoffice.org/c/core/+/141600
Comment 29 Justin L 2022-10-20 16:52:59 UTC
(In reply to Sven Neuz (SERPENTEQ) from comment #14)
> Also sorting gets lost on refresh.
Sorting isn't a property, is it? Is there any other place where a sort is automatically triggered after the content changes?
Comment 30 m_a_riosv 2023-09-16 08:37:20 UTC
*** Bug 157248 has been marked as a duplicate of this bug. ***
Comment 31 Stéphane Guillou (stragu) 2024-07-22 11:38:59 UTC
Still reproduced in:

Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 8705cfecd5a10f817d3a2a02041d85e77282aa30
CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: CL threaded

Despite recent pivot table formatting-related commit by Quikee:

https://gerrit.libreoffice.org/q/owner:quikee@gmail.com+status:merged+mergedbefore:2024-05-01+mergedafter:2024-03-31+pivot

Quikee, after looking into PT formatting in depth, any comment on this particular issue?