The destination range is not empty. Overwrite existing contents? [No]/[Yes] When a pivot table expands into non-empty cells, it is not obvious which pivot table is generating the warning when multiple pivot tables are in play, and neither is it obvious whether the warning pertains to cells on the right of said pivot table or below it, or both, particularly when there are pivot tables on multiple worksheets and pivot tables are being updated in sequence by a macro. I offer 3 levels of solution to this enhancement request. Any level will be sufficient. Level 1 solution: Simply include the address of the offending cell range in the dialog box. Level 2 solution: Also indicate whether the offending cell range is right of or below the existing pivot table. Level 3 solution: Include the address of both cells ranges in the dialog box when expansion pertains to both the vertical and horizontal directions.
Could you please attach an example spreadsheet?
(In reply to Heiko Tietze from comment #1) > Could you please attach an example spreadsheet? I would oblige with the workbook in question but it has 45 sheets and contains confidential data. I'll try to fabricate a nontrivial example with made-up data.
On second thought, there might be a different approach to solving the problem than what I have requested. The enhancement I have suggested is "thinking inside the box". Let's see what I really want, and let UI experts weigh in on potential solutions. The change as requested will allow a user to identify which pivot tables want to expand when pivot tables are refreshed, without actually letting them grow (overwrite == No). Pivot tables may be bordered by "canary" cells when these tables are not expected to grow. Such pivot tables typically have row fields or column fields or page fields with a specific set of values selected, and the user wants only these values to be selected. When a new value (perhaps a typo) appears in a cell in the source range, this causes a new value to be added to the list AND SELECTED in every pivot table which includes the offending cell's column as a row field or column field or page field. A new value for a row field grows a pivot table vertically, and a new value for a column field grows a pivot table horizontally. Canary cells are a way of identifying this growth. Spotting pivot tables which have grown is easy in workbooks with one or two pivot tables. It's not so easy when a workbook contains 55 pivot tables spread across 45 sheets, hence the desire to show ranges that would be overwritten. In general if each worksheet contains only one pivot table and no additional computations nearby, then a user may allow canary cells to be overwritten without creating havoc. The user then fixes the field selections in the altered pivot tables and restores the canary cells after the pivot table has shrunk back to size. The situation is more critical when there are nearby computations or adjacent pivot tables on the same sheet that are wiped out by overwriting. Giving a second thought about this use case, it would actually be more valuable to allow a user to "lock" row fields and column fields (and page fields) so that new values appearing in selection lists are not automatically selected. Then dimensions of these pivot tables does not change. It would also be useful to indicate somehow that a new value has appeared in the list for locked fields, so that typos can be fixed in the source range or new values in the list can be considered for inclusion among the blessed. This actually is my preferred solution, because it strikes at the root of the problem, rather than merely dealing with side effects. It also eliminates the need for canary cells, and more importantly protects the contents sheets near these pivot tables.
Created attachment 195869 [details] Example Example document: add more data on Col A & B like A5=C, B5=rand.nv() and refresh the first pivot table: it will grow into the second (regardless the confirmation box is denied). The request is to probably push the second table downwards (sorry, haven't read the latest comment in detail). Sounds not flexible and heavily error-prone.
Nope. Tldr; The request is to prevent growth of a pivot table by adding a "locked" attribute to row fields, column fields, and page fields so that no additional values become automatically selected when the pivot table is refreshed and new data values are present in the source range.
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to Robert Lacroix from comment #5) > Nope. Tldr; The request is to prevent growth of a pivot table by adding a > "locked" attribute to row fields, column fields, and page fields... Do you expect this to be applied automatically depending on the surrounding cells or is a manual action? How to feed back when content is not appended in the pivot table? (Adding the topic to the agenda of the design meeting, so we can ponder over your ideas next week.)
(In reply to Robert Lacroix from comment #5) > Nope. Tldr; The request is to prevent growth of a pivot table by adding a > "locked" attribute to row fields, column fields, and page fields so that no > additional values become automatically selected when the pivot table is > refreshed and new data values are present in the source range. Please forgive my ignorance... If you don't want the PT to be automatically updated, perhaps you could copy the content of the PT to a simple table (not in the sense of Excel's Table, nor as a Pivot Table). I mean, once you built the structure of the PT, and the data is acquired in order to fill the PT with content, can't you just copy the result to a simple table? The result of that copy should be independent of the original data, breaking the relation to the PT. Is this a valid workaround? Is this "locked" Pivot Table request available in other spreadsheet tools?
I checked the behaviour of Excel today. When all values are selected, the field's "Select All" checkbox becomes checked (or conversely, selecting every item causes Select All to become checked automatically), and in this state new values of the field upon PT refresh are added automatically to the field's values list, like LO-Calc does, so the PT grows. However when one or more values are unselected, new values do not become automatically selected upon PT refresh, in effect opposite to LO-Calc behaviour. I want to have the Excel behaviour, not for the sake of compatibility, but because it just makes sense from a use-case POV. If I select only certain values to be summarized by the PT, I do not want extraneous new values to pollute my selection list. Growing the table is just undesireable side effect in this case, one we are prepared to deal with if we want all values selected. The Select All checkbox in the field is a 3-state checkbox. When there is a mix of checked and unchecked values, the Select All checkbox shows a filled square. The field's heading cell also contains a small indicator to say multiple selections are active (I believe, have not verified).
(In reply to Heiko Tietze from comment #7) > (In reply to Robert Lacroix from comment #5) > > Nope. Tldr; The request is to prevent growth of a pivot table by adding a > > "locked" attribute to row fields, column fields, and page fields... > Do you expect this to be applied automatically depending on the surrounding > cells or is a manual action? How to feed back when content is not appended > in the pivot table? > > (Adding the topic to the agenda of the design meeting, so we can ponder over > your ideas next week.) I was requesting a manual action, but considering how Excel does it I think that is even better. As a use case, ponder how management accounting would work. A list of transactions grows, and you always want a PT to show the current sum of a subset of the transactions, say for example only "deposit" and "withdrawal" in the field values, among others. You don't want a new value of the field that suddenly appears, say "interest", to automatically modify the PT.
(In reply to Robert Lacroix from comment #9) > I checked the behaviour of Excel today. When all values are selected... Hard to follow for me. Taking the example from comment 4, Excel completely rejects the update. The message is "We couldn't complete the action for... because there's already a... there. Make space and try again." We ask and let the user decide to overwrite content. STR: * load the example that contains these data points Col_A Col_B Col_C Col_D Pivot 1 A =rnd() C =rnd() =Sheet1.$A:$B A C B D Pivot 2 B D =Sheet1.$C:$D * add something after A5/B5 * refresh the pivot table => clear message with yes/no actions
(In reply to Heiko Tietze from comment #11) > (In reply to Robert Lacroix from comment #9) > > I checked the behaviour of Excel today. When all values are selected... > Hard to follow for me. Taking the example from comment 4, Excel completely > rejects the update. The message is "We couldn't complete the action for... > because there's already a... there. Make space and try again." > > We ask and let the user decide to overwrite content. > > STR: > > * load the example that contains these data points > > Col_A Col_B Col_C Col_D Pivot 1 > A =rnd() C =rnd() =Sheet1.$A:$B > A C > B D Pivot 2 > B D =Sheet1.$C:$D > > * add something after A5/B5 > * refresh the pivot table > => clear message with yes/no actions Sure, in this example all previous field values are selected in the filter before refreshing, and the PT tries to expand because new values are added which also become selected in the filter. LO-Calc is better for asking before overwriting in this case. However, it's the behaviour where some of the previous field values are not selected in the filter. Then no new values should become selected despite being added onto the filter list with the PT refresh. PT expansion is contingent on new filter values becoming selected. Excel does not select new values in the filter when at least one of the filter values is unchecked.
Your example does not make use of field value selections. Try this example. For simplicity of demonstration we allow LO-Calc to create the pivot table in a new sheet. XACTION AMOUNT deposit 30 deposit 5 withdraw -2 billpay -10 deposit 4 endofdata 0 Step 1: create a LO-Calc spreadsheet with the above table of information 1a) highlight the above 7 rows in this Bugzilla comment 1b) press Ctrl-C to copy the example to the clipboard 1c) start LO-Calc; a blank spreadsheet appears with active cell Sheet1.A1 1d) press Ctl-V to paste the data into the spreadsheet; Text Import dialog box appears 1e) press Enter (or click OK); the example appears in LO-Calc at Sheet1.A1:B7 - The last row "endofdata" is included so that we can easily extend the source range with new data by using row insertions, avoiding the need to interact with the Pivot Table Layout dialog each time new source data is added. Step 2: create the pivot table 2a) select range A1:B7 (pressing Ctrl-Home Ctrl-Shift-End does this) 2b) choose menu Insert > Pivot Table... 2c) press Enter (or click OK) to use the current selection; Pivot Table Layout dialog box appears 2d) drag XACTION from Available Fields to Row Fields 2e) drag AMOUNT from Available Fields to Data Fields; SUM - AMOUNT appears there 2f) click OK - The pivot table is created at "Pivot Table_Sheet1_1".A1:B6 - The Total Result value in B4 is 27 - It includes rows for "billpay", "deposit", "endofdata" and "withdraw" Step 3: remove the "billpay" and "endofdata" rows from the XACTION field of the pivot table 3a) expand the field XACTION dropdown in "Pivot Table_Sheet1_1".A1 3b) uncheck "billpay" and "endofdata" 3c) click OK - The pivot table shrinks by two rows - It includes rows for deposit and withdraw - The Total Result value in B4 becomes 37 - The XACTION field heading in A1 has a dot to show at least 1 value is unchecked Step 4: extend the source data with new data 4a) go to Sheet1 4b) insert a row above row 7 (right-click row 7's selector, select Insert Rows Above) 4c) enter interest in Sheet1.A7 4d) enter 0.1 in Sheet1.B7 Step 5: refresh the pivot table 5a) go to "Pivot Table_Sheet1_1" 5b) right-click anywhere in the pivot table; the popup menu appears 5c) click Refresh In LO-CALC: - The pivot table expands to A1:B5 - Total Result value in B5 becomes 37.1 - A new row appears in the pivot table containing "interest". - The XACTION field dropdown shows the new value "interest" as checked. In Excel: - The pivot table does not change dimensions - Total Result value in B5 remains 37 - The XACTION field dropdown shows the new value "interest" as unchecked. - LO-Calc's current behaviour when a pivot table's dimensions change is better than Excel's and should remain as-is, as you describe. - I want Excel's behaviour for addition of new field values when some field values are already unchecked before the pivot table is refreshed. This should apply to row fields and column fields, and also to page fields for consistency (although new field values do not affect table dimensions). - By the way, I just found an obscure bug in LO-Calc where now inserting another source data row with the value "interest" (including double-quotes) and refreshing confuses the XACTION dropdown selector, so that neither version can be removed from the pivot table by unchecking it, until the Pivot Table Layout dialog box is opened and OK clicked.
The instructions for Step1 are incorrect. The clipboard contents must be pasted as unformatted text. As written, the procedure worked fine while I was typing into the Bugzilla comment box, but from the comment as shown, the pressing Ctrl-V wants to do an automatic text import in LO-Calc. So the modified instructions for Step 1 are: Step 1: create a LO-Calc spreadsheet with the above table of information 1a) highlight the above 7 rows in this Bugzilla comment 1b) press Ctrl-C to copy the example to the clipboard 1c) start LO-Calc; a blank spreadsheet appears with active cell Sheet1.A1 1d) right-click in cell A1; the cell context menu pops up 1e) select Paste Special > Unformatted Text 1f) press Enter (or click OK); the example appears in LO-Calc at Sheet1.A1:B7
More errata in comment 13: was: - The Total Result value in B4 is 27 should be: - The Total Result value in B6 is 27
(In reply to Robert Lacroix from comment #13) > > In LO-CALC: > - The pivot table expands to A1:B5 > - Total Result value in B5 becomes 37.1 > - A new row appears in the pivot table containing "interest". > - The XACTION field dropdown shows the new value "interest" as checked. > > In Excel: > - The pivot table does not change dimensions > - Total Result value in B5 remains 37 > - The XACTION field dropdown shows the new value "interest" as unchecked. That means that the refresh in Calc added the items to the Filter as "checked" (not hidden, not filtered out), whereas Excel adds the items to the Filter as "unchecked" (i.e. filtered out). Please correct me if I'm wrong. > > - LO-Calc's current behaviour when a pivot table's dimensions change is > better than Excel's and should remain as-is, as you describe. So, you currently prefer that newly-added data items in the original data table would be set as "shown" (not hidden) in the filter of the PT when the PT is refreshed, as already done by Calc. No changes here. > > - I want Excel's behaviour for addition of new field values when some field > values are already unchecked before the pivot table is refreshed. This > should apply to row fields and column fields, and also to page fields for > consistency (although new field values do not affect table dimensions). In your example, you added a new row of data, which impacts the Filter (with the differences against Excel as described above, "shown" vs "hidden"). But now you are mentioning other fields of the PT (which is not the same as adding data). I am possibly misunderstanding, and I am also looking for an actual concise reply to the questions I presented in comment 8: * If you want a non-changing resulting table, can't you just copy the content of the PT to another simple table, in order to "disconnect" the (pasted) result from the original data? * How Excel manages (or achieves) the suggested "locked" status? Again, I might be misunderstanding the real goal or the real meaning of this "locked" expression – an expression that is so ambiguous that should be avoided anyway.
> > In LO-CALC: > > - The pivot table expands to A1:B5 > > - Total Result value in B5 becomes 37.1 > > - A new row appears in the pivot table containing "interest". > > - The XACTION field dropdown shows the new value "interest" as checked. > > > > In Excel: > > - The pivot table does not change dimensions > > - Total Result value in B5 remains 37 > > - The XACTION field dropdown shows the new value "interest" as unchecked. > > That means that the refresh in Calc added the items to the Filter as > "checked" (not hidden, not filtered out), whereas Excel adds the items to > the Filter as "unchecked" (i.e. filtered out). Please correct me if I'm > wrong. Right, data for the new field value is not included in the "data" cell calculations and does not appear as a new row/column of the PT, except when all field values were already checked before refresh. In Excel, WHEN ALL VALUES ARE CHECKED, then Excel does the same thing as Calc for new values. But when at least 1 value is unchecked before refreshing, then Excel has the opposite behaviour. It adds new values as unchecked. > > - LO-Calc's current behaviour when a pivot table's dimensions change is > > better than Excel's and should remain as-is, as you describe. > > > So, you currently prefer that newly-added data items in the original data > table would be set as "shown" (not hidden) in the filter of the PT when the > PT is refreshed, as already done by Calc. No changes here. No. My comment is only about prompting when the PT will overwrite adjacent nonempty cells, which happens only when a PT expands. > > - I want Excel's behaviour for addition of new field values when some field > > values are already unchecked before the pivot table is refreshed. This > > should apply to row fields and column fields, and also to page fields for > > consistency (although new field values do not affect table dimensions). > > In your example, you added a new row of data, which impacts the Filter (with > the differences against Excel as described above, "shown" vs "hidden"). But > now you are mentioning other fields of the PT (which is not the same as > adding data). > > I am possibly misunderstanding, and I am also looking for an actual concise > reply to the questions I presented in comment 8: > > * If you want a non-changing resulting table, can't you just copy the > content of the PT to another simple table, in order to "disconnect" the > (pasted) result from the original data? Not really. Dozens of PTs use the same 7000-row source table, and each PT has different dimensions. I don't want to scroll through the list of field values of every PT to find the new values and uncheck them when I add new source data rows (or edit existing ones). Excel does it correctly, LO-Calc does not. In LO-Calc, the real workaround is that I MUST document the list of values which are supposed to be checked for each field, because I won't remember precisely which values are supposed to be checked, and LO-Calc always adds new values with checks. > * How Excel manages (or achieves) the suggested "locked" status? Automatically. In Excel the array of field VALUES is always extended with new values upon refresh. However the state of the CHECKBOX with new values depends on whether any of the preexisting values (before refresh) are unchecked. If all were checked, new values also have checks. If any are unchecked, then new values are not checked. > Again, I might be misunderstanding the real goal or the real meaning of this > "locked" expression – an expression that is so ambiguous that should be > avoided anyway. The goal is to avoid having the subset of checked values in a field change automatically when a particular subset is already selected. For example. A source table contains the list of everyone in a school and other columns of info. One column is the "position", say "student", "admin", and "teacher". The PT is set up with only "student" selected. Then a new source row is added with "position" of "librarian". LO-Calc transforms the PT from students-only to students+librarian, and we won't even notice a change of dimensions if the field is a page field. That's not good, we set it up already to be only students. Excel does the right thing and maintains it as students-only. Forget about the term locking, it is superfluous because Excel never changes the underlying SQL query if you think about it that way, only the user can change it. The page/column/row fields of the PT taken together define the key for rows in the source data. Checked items in these fields are key values to match in the query. Data fields are the summarized query result (sum, average, count, etc.). If all values of a field are checked, this means effectively "select ... where field_x=*" on the part of the key denoted by field_x. If any values of field_x are not checked, then the query includes individual checked values to matched "select ... where (field_x=value_a or field_x=value_b or ...)". The query is immutable (by Excel). Only the user can change the query, by changing checkboxes.
My impression is that the main issue presented in comment 0 is not the same as later (or more recent) comments (e.g. comment 17). Am I wrong? At any rate, I am sure I am misunderstanding some comments. Attempting to sum up comment 17 (which may or may not be equivalent to comment 0, IIUC): * When the PT is filtered and the filter is different than showing "All" items, then new data items should be added as hidden (not as shown, not "checked" in the filter of the PT) when the PT is refreshed (updated). FWIW, I am not sure that every use-case or user would agree with such change. There may be other use-cases in which the current behavior would be better suited. In the PT's properties dialog, maybe there should be some option in the "Options" area or in the "Source and Destination" area, since there is already an "Add filter" option. I admit, I might be misunderstanding, in part, or all.
(In reply to ady from comment #18) > My impression is that the main issue presented in comment 0 is not the same > as later (or more recent) comments (e.g. comment 17). Am I wrong? No, you are correct. In probing this issue through our discussion, it is clear the real issue I am having is the (my) unexpected expansion of multiple pivot tables when some field values are unchecked in them. But there are so many pivot tables dependent on the same source range, it is difficult to know which ones will overwrite on expansion. So it is appropriate to keep this bug about comment 0 and fork the rest into a new bug. The title of this bug is akin to "place moisture sensors in the basement" whereas the latter content is about "fix the leaking roof". They are related, but the fixes are independent and we should have both. > Attempting to sum up comment 17 (which may or may not be equivalent to > comment 0, IIUC): > > * When the PT is filtered and the filter is different than showing "All" > items, then new data items should be added as hidden (not as shown, not > "checked" in the filter of the PT) when the PT is refreshed (updated). > Yes! This should be a new bug. Let this title remain about comment 0 since there's still value in identifying which PT is wants to overwrite content for a given popup warning (there are multiple warnings, but not as many as there are PTs using the same source range). > FWIW, I am not sure that every use-case or user would agree with such > change. There may be other use-cases in which the current behavior would be > better suited. In the PT's properties dialog, maybe there should be some > option in the "Options" area or in the "Source and Destination" area, since > there is already an "Add filter" option. Agreed. This is what I was thinking about when proposing a "locked" property. However it's clear that Excel does not use one. Users who are accustomed to the way LO-Calc works now may prefer it, and users who are accustomed to Excel may prefer it the other way. I am in the latter camp. So this would be better as a global property or compatibility option. > I admit, I might be misunderstanding, in part, or all. Indeed, confusion is warranted since it is I who has been less concise and unfocussed. But you have grokked it completely now, it is 2 issues rolled into one bug.
(In reply to Robert Lacroix from comment #19) > > FWIW, I am not sure that every use-case or user would agree with such > > change. There may be other use-cases in which the current behavior would be > > better suited. In the PT's properties dialog, maybe there should be some > > option in the "Options" area or in the "Source and Destination" area, since > > there is already an "Add filter" option. > > Agreed. This is what I was thinking about when proposing a "locked" > property. However it's clear that Excel does not use one. Users who are > accustomed to the way LO-Calc works now may prefer it, and users who are > accustomed to Excel may prefer it the other way. I am in the latter camp. So > this would be better as a global property or compatibility option. No global or compatibility option; definitely not. First, Excel might change its (default) behavior (in some version or in the rolling 365 target). Second, and more important, the same user might need one behavior for one workbook but the other behavior for another workbook, depending on layouts and use-cases. Someone might even think that this could be different in different worksheets of the same workbook, or even a different behavior for different PTs on the same workbook. And there you have it; the option (if implemented) is for each PT, not global.
> No global or compatibility option; definitely not. OK, not wanting to chase moving targets is a good basis for this stance. Too much confusion among users, not enough developers who understand this system or the other. I'm pretty easy-going, I could live with a per-PT option, for the sake of backward compatibility. It's a once-per-PT click. I don't churn out new PTs every day, I just work with the 50+ PTs I already have in that 45 sheet workbook. I *could* live with it as an option, but I will take the position that changing it is the right thing to do, and not only to lessen the aggravation of expanding PTs. > First, Excel might change its (default) behavior (in some version or in the > rolling 365 target). That's an unfounded proposition since it affects computational results when PTs are refreshed, and changing computations capriciously in Excel is an extremely remote possibility. It's more likely for the user interface to change. There is a deep history that will enrage a lot of monied enterprise users if a snot-nosed kid at MS changes the way Excel's pivot tables work. You may not realize that Excel is the grand-daddy which outlived its peers. It existed in the late 1980s, when I first used it on a Mac SE, years before Windows made its debut. That's a long time with a lot of users to make sure that MS got this functionality right. By the same token, it's unlikely that the default (proposed PT option) will flip in LO-Calc some day in the future - there has to be a very good reason to change it - and mere compatibility with Excel isn't it. I argue that Excel's current way is the better way, since LO-Calc changes PT results capriciously with new unrelated source data rows, as demonstrated in comment 13. If Excel ever changes the way PT's work, and LO-Calc preserves the current Excel behaviour, then score one for LO-Calc! I'm not a proponent of compatibility when functionality is at stake. > Second, and more important, the same user might need one behavior for one > workbook but the other behavior for another workbook, depending on layouts > and use-cases. Someone might even think that this could be different in > different worksheets of the same workbook, or even a different behavior for > different PTs on the same workbook. And there you have it; the option (if > implemented) is for each PT, not global. So backward compatibility for the current user base is your paramount consideration, and it is possible that some use case depends on it. (Thowing down the gauntlet) I challenge anyone to produce a use case that works better the way LO-Calc does it now, than the way Excel does it.
Created attachment 196039 [details] Data Field Options You can achieve this with the data field options. To stick with my example, showing only two items from top keeps the PT height constant. Besides the questionable UX, isn't this what you want? If so, can we discuss how to make this feature easier to access (I have to add the row to filters, click options in data fields, change settings)?
(In reply to Robert Lacroix from comment #21) > So backward compatibility for the current user base is your paramount > consideration Not necessarily. Generally speaking, we (users) tend to assume that "our" case is also better, and that it is probably the way everyone else would use it, need it and want it to be. As for the space that each PT could take (potentially conflicting with each other when the size changes in some way), I would suggest either: * using other spreadsheets (in the same workbook) to each PT location (instead of multiple PTs in one in-common spreadsheet); or, * each PT should span horizontally (so each can grow vertically if needed) and never have their layout overlap on (the same) columns; or, * each PT should span vertically (so each can grow horizontally if needed) and never have their layout overlap on (the same) rows. All that does not necessarily invalidate the request(s), but I'll leave that evaluation to others.
(In reply to Heiko Tietze from comment #22) > You can achieve this with the data field options. To stick with my example, > showing only two items from top keeps the PT height constant. > > Besides the questionable UX, isn't this what you want? If so, can we discuss > how to make this feature easier to access (I have to add the row to filters, > click options in data fields, change settings)? This is a good try at a workaround, but no. "Show Top N items" does constrain the PT dimensions, but it has a fatal side effect. "Show Top N items" selects the N field values which have the greatest total of their associated data. If a new (unexpected column for example) appears because of the unconditional setting of the new value's checkbox, then "Show Top N items" makes a choice among the N+1 columns now available for display and the lowest one is omitted. So when the new field value has a total which is greater than any of the others, one of the previously displayed columns is omitted from the PT and the columns are rearranged in alphabetical order. This is bad news for any formula which refers into the data in the PT.
(In reply to ady from comment #23) > As for the space that each PT could take (potentially conflicting with each > other when the size changes in some way), I would suggest either: > > * using other spreadsheets (in the same workbook) to each PT location > (instead of multiple PTs in one in-common spreadsheet); or, The issue of a PT overwriting other content when it expands is a pretty big concern, but not the biggest one. The killer is that formulae referring into PT data are not adjusted when the shape of the PT changes. Isolating a PT onto one sheet by itself does not fix this. > * each PT should span horizontally (so each can grow vertically if needed) > and never have their layout overlap on (the same) columns; or, > > * each PT should span vertically (so each can grow horizontally if needed) > and never have their layout overlap on (the same) rows. Rows, columns, same issue - unexpected growth of the PT invalidates a formula which refers to data in the PT.
I notice now that I have been calling a Filter field in a PT Layout a "page field". Please excuse this. I will switch terminology in future comments.
You know, there are many things that LO-Calc does better than Excel when it comes to Pivot Tables. I loathe navigating PT options in newer Excel. I'm not used to table options splashed across all manner of dialogs and ribbons. Excel sacrifices the Grant Totals column, putting a data column there, if sideways expansion is cancelled because of impending cell overwrite, yet leaves the "Grand total column" option active. This looks like a bug. They might call it a feature. In LO-Calc the field values list contracts to the minimum set after the PT is refreshed. But in Excel, typos are persistent in the field values list unless typos are backed out with Ctrl-Z. I find that what happens when cancelling PT expansion in LO-Calc could be more consistent. It depends on the context. If cancellation happens because of activity in the PT Layout dialog (even changing the source range) everything is nicely returned to the way it was. But if cancelling because of editing source data, the values list remains updated. Ctrl-Z will undo the data edit but leaves the values list untouched until the PT is refreshed. But at least it is returned to minimum on refresh.
We discussed the topic in the design meeting. The issue boils down to whether new data should be added as hidden when a filter is applied to PT. Some users may expect the Excel workflow, some the opposite and current LibreOffice way (which by the way is more safe as one may forget to update the filter). Side note: The same function is effective for autofilter, but in this case users should see that adding new data are really accepted. In fact both Excel and LibreOffice do show data but add the new value as disabled item to the AF. Regardless this inconsistency we suggest to add an option "[x] Enable new items in autofilter", defaulting to on, at the PT filter widget. The option needs to be stored with the document, which might be difficult to implement. And it's very unclear why PT should become overwritten at all and we suggest to just inform the user about the insufficient space (again a safety measure). This should be done with an advanced option to confirm, meaning to get the current behavior again. Adding Eike as Calc expert to review the considerations.
How about the "other item" addressed by comment 0? A blurb in the warning mentioning the PT address having a collision would be helpful and relatively easy to implement.
(In reply to Heiko Tietze from comment #28) > "[x] Enable new items in autofilter" The meaning of that phrase might sound clear in this ticket, but unclear / ambiguous to a common user (who has not read this report). How about: "[x] New items as 'shown' in autofilter" or: "[x] Refreshed items as 'shown' (not hidden) in autofilter" or similar? A tooltip with further explanation / clarification might help too.
(In reply to Robert Lacroix from comment #29) > A blurb in the warning mentioning the PT address having a collision... (In reply to ady from comment #30) > How about... No objection about more info in the message and a better label.
(In reply to Heiko Tietze from comment #31) > (In reply to Robert Lacroix from comment #29) > > A blurb in the warning mentioning the PT address having a collision... > > No objection about more info in the message and a better label. Thank you for including the additional information in the warning as requested in comment 0. Also, the committee's decision in comment 28 to add an option controlling automatic addition of new vlaues is satisfactory to me, as I said it would be. I have another PT UI idea related to new field values, so I will open a new enhancement request for it and refer back to the discussion in this bug.